如何在 MySQL 中正确应用 CROSS JOIN?
mysqlmysqli database更新于 2024/12/15 19:24:17
MySQL 中的 CROSS JOIN 返回连接表中行的笛卡尔积。让我们通过创建表来查看示例 −
mysql> create table PairDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserRelationshipName varchar(20) -> ); Query OK, 0 rows affected (0.56 sec)
示例
使用 insert 命令在表中插入一些记录。 查询语句如下 −
mysql> insert into PairDemo(UserName,UserRelationshipName) values('John','James');
Query OK, 1 row affected (0.15 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','James');
Query OK, 1 row affected (0.20 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Carol','David');
Query OK, 1 row affected (0.25 sec)
mysql> insert into PairDemo(UserName,UserRelationshipName) values('Sam','David');
Query OK, 1 row affected (0.19 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from PairDemo;
输出
+--------+----------+----------------------+ | UserId | UserName | UserRelationshipName | +--------+----------+----------------------+ | 1 | John | James | | 2 | Carol | James | | 3 | Carol | David | | 4 | Sam | David | +--------+----------+----------------------+ 4 rows in set (0.00 sec)
以下是应用 CROSS JOIN 的查询 −
mysql> SELECT U.UserName, UR.UserRelationshipName -> FROM ( -> (SELECT DISTINCT UserName from PairDemo) U -> CROSS JOIN -> (SELECT DISTINCT UserRelationshipName from PairDemo) UR -> ) WHERE NOT EXISTS ( -> SELECT * FROM PairDemo tbl -> WHERE tbl.UserName = U.UserName -> AND tbl.UserRelationshipName = UR.UserRelationshipName -> ) ;
以下是显示上述查询实现 CROSS JOIN 的结果的输出 −
+----------+----------------------+ | UserName | UserRelationshipName | +----------+----------------------+ | Sam | James | | John | David | +----------+----------------------+ 2 rows in set (0.03 sec)
相关文章
有用资源
mysql 参考教程 - 该教程包含有关 mysql 的更多信息:https://www.cainiaomax.com/mysql/
