关键是课程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'如果有单独的课程表好点
这种问题,应该以学生表做主表,而不是去考试记录里面找学生,是有了学生,学生才去考试,儿不是考了试才有学生,所以放出你的学生表就好 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 NULLDROP TABLE #student DROP TABLE #passhis DROP TABLE #course
--学生表是必须的,假定是 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
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也为空
如果没有执行过,就执行一下
一点点小问题哈结果是:
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了,非常感谢哈
一点点小问题哈结果是:
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了,非常感谢哈哦,没注意你一个科目不通过会记录两次,会不会记录两次一次通过一次不通过呢,如果这样要怎么算呢
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 NULLDROP TABLE #student
DROP TABLE #passhis
DROP TABLE #course
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