sql server查询SQL备忘(2)- cross join

最新推荐文章于 2024-09-04 16:02:01 发布

电灯泡

最新推荐文章于 2024-09-04 16:02:01 发布

阅读量637

点赞数 1

分类专栏： Database 文章标签： sql server sql cross join

本文链接：https://blog.csdn.net/xukunddp/article/details/41177697

版权

Database 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

表A有个字段sid,里面有四门课程1，2，3，4；表B存放某学生(uid)以及这门课程(sid)是否通过(pass)的对应关系，结果是求没有通过考试的学生以及课程的对应关系，如果表B没有存放是否考过的关系，则也视为不通过，如下：
table A
sid
1
2
3
4

table B
uid sid pass
c55 1 y
eb4 2 n
c55 3 n
08d 3 y
08d 2 y

结果集应该如下：
uid sid
c55 2
c55 3
c55 4
eb4 1
eb4 2
eb4 3
eb4 4
08d 1

08d 4

要实现上面红色的要求，left/right join以及full join试过都不行，得用cross join

SELECT
	T1.uid,T1.sid
FROM
(
SELECT * FROM tableA,(SELECT uid FROM tableB GROUP BY uid)T
)T1
	LEFT JOIN tableB T2 ON T1.uid = T2.uid AND T1.sid = T2.sid
WHERE T2.pass IS NULL OR T2.pass='n'

其中

SELECT * FROM tableA,(SELECT uid FROM tableB GROUP BY uid)T

等同于 SELECT * FROM tableA cross join (SELECT uid FROM tableB GROUP BY uid)T，所以也可以用:

SELECT
	T1.uid,T1.sid
FROM
	(
SELECT * FROM tableA cross join (SELECT uid FROM tableB GROUP BY uid)T
)T1
	LEFT JOIN tableB T2 ON T1.uid = T2.uid AND T1.sid = T2.sid
WHERE T2.pass IS NULL OR T2.pass='n'

以下是建表数据，大家可以逐步调试玩玩：

CREATE TABLE tableA(sid VARCHAR(36) NOT NULL);
CREATE TABLE tableB(uid VARCHAR(36) NOT NULL,sid VARCHAR(36) NOT NULL,pass VARCHAR(1) NOT NULL);
insert tableA values('1');
insert tableA values('2');
insert tableA values('3');
insert tableA values('4');
insert tableB values('c55','1','y');
insert tableB values('eb4','2','n');
insert tableB values('c55','3','n');
insert tableB values('08d','3','y');
insert tableB values('08d','2','y');