sql server,求一关联sql?

[电灯泡]

表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
c55 3 n
08d 2 y

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

[电灯泡]

关键是课程4不好处理，表B没有存放uid和课程4的关系，如果用from A left join B, 也不能实现，不知道有没有更好的法子

[还在加载中灬]

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

如果有单独的课程表好点

[电灯泡]

引用 2 楼 ky_min 的回复:

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

如果有单独的课程表好点

这个貌似有点问题，在结果集中，对于is_pass is NULL情况下，他所对应的sid也为空

[还在加载中灬]

这是你执行过的结果吗?如果是，把你的改后的SQL发上来看下 如果没有执行过,就执行一下

[电灯泡]

引用 4 楼 ky_min 的回复:

这是你执行过的结果吗?如果是，把你的改后的SQL发上来看下 如果没有执行过,就执行一下

一点点小问题哈结果是： 08d 1 08d 4 c55 2 c55 3 c55 3 c55 4 eb4 1 eb4 2 eb4 3 eb4 4 加一个group by T1.uid,T1.sid就ok了，非常感谢哈

[还在加载中灬]

引用 5 楼 xukunddp 的回复:

[quote=引用 4 楼 ky_min 的回复:] 这是你执行过的结果吗?如果是，把你的改后的SQL发上来看下 如果没有执行过,就执行一下

一点点小问题哈结果是： 08d 1 08d 4 c55 2 c55 3 c55 3 c55 4 eb4 1 eb4 2 eb4 3 eb4 4 加一个group by T1.uid,T1.sid就ok了，非常感谢哈[/quote]哦，没注意你一个科目不通过会记录两次，会不会记录两次一次通过一次不通过呢，如果这样要怎么算呢

[chen357313771]

这种问题，应该以学生表做主表，而不是去考试记录里面找学生，是有了学生，学生才去考试，儿不是考了试才有学生，所以放出你的学生表就好

CREATE TABLE #student
(
	UId VARCHAR(10)
	,NAME NVARCHAR(20)
)
INSERT INTO #student
SELECT 'c55',	'x'	UNION ALL 
SELECT 'eb4',	'x' UNION ALL 
SELECT 'c55',	'x' UNION ALL
SELECT '08d',	'x' UNION ALL	 
SELECT 'c55',	'x' UNION ALL	 
SELECT '08d',	'x' UNION ALL
SELECT 'NOT',	'没有参加过考试的学生'

CREATE TABLE #passhis
(
	UId VARCHAR(10)
	,sid  INT
	,pass CHAR(1)
)
INSERT INTO #passhis
SELECT 'c55',	1,	 'y' UNION ALL
SELECT 'eb4',	2,	 'n' UNION ALL
SELECT 'c55',	3,	 'n' UNION ALL
SELECT '08d',	3,	 'y' UNION ALL
SELECT 'c55',	3,	 'n' UNION ALL
SELECT '08d',	2,	 'y'

CREATE TABLE #course
(
	sid VARCHAR(10)
	,NAME NVARCHAR(20)
)
INSERT INTO #course
SELECT 1,'y' UNION ALL
SELECT 2,'yu' UNION ALL
SELECT 3,'xx' UNION ALL
SELECT 4,'xx'

SELECT DISTINCT s.UId,c.sid
FROM #student AS S
CROSS JOIN #course AS C 
WHERE NOT EXISTS (SELECT 1 FROM #passhis P WHERE S.UId=P.UId AND C.sid=P.sid AND p.pass='y')
--LEFT JOIN #passhis P ON S.UId=P.UId AND C.sid=P.sid  --left join /exists 都可以
--WHERE p.pass='n' OR p.pass IS NULL

DROP TABLE #student
DROP TABLE #passhis
DROP TABLE #course

[Tiger_Zhao]

--学生表是必须的，假定是 c
WITH t1 AS ( -- 学生和课程做个全集
    SELECT c.uid,
           a.sid
      FROM c,a
)
,t2 AS ( -- 生成全集的通过状态
    SELECT t1.uid,
           t1.sid,
           ISNULL(b.pass,'n') AS pass
      FROM t1
 LEFT JOIN b
         ON b.uid = t1.uid
        AND b.sid = t1.sid
)
-- 输出未通过的学生、课程
SELECT t2.uid,
       t2.sid
  FROM t2
 WHERE pass = 'n'

[电灯泡]

谢谢各位哈，公布下我自己执行过后的正确答案： SELECT T1.uid,T1.sid FROM (SELECT * FROM A,(SELECT uid FROM B GROUP BY uid)T)T1 LEFT JOIN B T2 ON T1.uid = T2.uid AND T1.sid = T2.sid WHERE T2.pass IS NULL OR T2.pass=’n’ and group by T1.uid,T1.sid