一道很有挑战性的SQL语句，想了好久没想到好的办法
如下表：
userID programId answer
81771881B
81771882A
81771883A
58022011A
58022012A
58022013A
59629461B
59629462C
59629463A
52119821A
52119822B
52119823A
20512521B
20512522B
20512523A
70880331A
70880332A
70880333A
55710791A
55710792A
55710793A
21273661B
21273662B
21273663A
79943071C
79943072C
79943073B
57186161A
57186162A
57186163A
71103071A
71103072A
71103073A
写SQL找出所有答案都正确的用户，如正确答案为1 B，2 B，3 A，就要找出所有答案为1 B，2 B，3 A的用户。
[解决办法]
Select
A.*
From
TableName A
Inner Join TableName B
On A.userID = B.userID
Inner Join TableName C
On A.userID = C.userID
Where
A.programId = 1 And C.answer = 'B '
And
B.programId = 2 And C.answer = 'B '
And
C.programId = 3 And C.answer = 'A '
[解决办法]
create table #t(userID int, programId int, answer varchar(10))
insert into #t
select 8177188,1, 'B ' union all
select 8177188,2, 'A ' union all
select 8177188,3, 'A ' union all
select 5802201,1, 'A ' union all
select 5802201,2, 'A ' union all
select 5802201,3, 'A ' union all
select 5962946,1, 'B ' union all
select 5962946,2, 'C ' union all
select 5962946,3, 'A ' union all
select 5211982,1, 'A ' union all
select 5211982,2, 'B ' union all
select 5211982,3, 'A ' union all
select 2051252,1, 'B ' union all
select 2051252,2, 'B ' union all
select 2051252,3, 'A ' union all
select 7088033,1, 'A ' union all
select 7088033,2, 'A ' union all
select 7088033,3, 'A ' union all
select 5571079,1, 'A ' union all
select 5571079,2, 'A ' union all
select 5571079,3, 'A ' union all
select 2127366,1, 'B ' union all
select 2127366,2, 'B ' union all
select 2127366,3, 'A ' union all
select 7994307,1, 'C ' union all
select 7994307,2, 'C ' union all
select 7994307,3, 'B ' union all
select 5718616,1, 'A ' union all
select 5718616,2, 'A ' union all
select 5718616,3, 'A ' union all
select 7110307,1, 'A ' union all
select 7110307,2, 'A ' union all
select 7110307,3, 'A '
select userID
from #t as A
where programId = 1 and answer = 'b '
and exists (select * from #t where userID=A.userID and programId = 2 and answer = 'b ')
and exists (select * from #t where userID=A.userID and programId = 3 and answer = 'A ')
drop table #t
/*
--结果
2051252
2127366
*/
[解决办法]
Create Table TEST
(userIDVarchar(10),
programIdInt,
answerVarchar(3))
Insert TEST Select '8177188 ',1, 'B '
Union All Select '8177188 ',2, 'A '
Union All Select '8177188 ',3, 'A '
Union All Select '5802201 ',1, 'A '
Union All Select '5802201 ',2, 'A '
Union All Select '5802201 ',3, 'A '
Union All Select '5962946 ',1, 'B '
Union All Select '5962946 ',2, 'C '
Union All Select '5962946 ',3, 'A '
Union All Select '5211982 ',1, 'A '
Union All Select '5211982 ',2, 'B '
Union All Select '5211982 ',3, 'A '
Union All Select '2051252 ',1, 'B '
Union All Select '2051252 ',2, 'B '
Union All Select '2051252 ',3, 'A '
Union All Select '7088033 ',1, 'A '
Union All Select '7088033 ',2, 'A '
Union All Select '7088033 ',3, 'A '
Union All Select '5571079 ',1, 'A '
Union All Select '5571079 ',2, 'A '
Union All Select '5571079 ',3, 'A '
Union All Select '2127366 ',1, 'B '
Union All Select '2127366 ',2, 'B '
Union All Select '2127366 ',3, 'A '
Union All Select '7994307 ',1, 'C '
Union All Select '7994307 ',2, 'C '
Union All Select '7994307 ',3, 'B '
Union All Select '5718616 ',1, 'A '
Union All Select '5718616 ',2, 'A '
Union All Select '5718616 ',3, 'A '
Union All Select '7110307 ',1, 'A '
Union All Select '7110307 ',2, 'A '
Union All Select '7110307 ',3, 'A '
GO
Select
A.userID
From
TEST A
Inner Join TEST B
On A.userID = B.userID
Inner Join TEST C
On A.userID = C.userID
Where
A.programId = 1 And A.answer = 'B '
And
B.programId = 2 And B.answer = 'B '
And
C.programId = 3 And C.answer = 'A '
GO
Drop Table TEST
--Result
/*
userID
2051252
2127366
*/
[解决办法]
--创建测试环境
create table 答题表(userID int,programId int,answer varchar(10))
create table 答案表(programId int,answer varchar(10))
--插入测试数据
insert 答题表(userID,programId,answer)
select '8177188 ', '1 ', 'B ' union all
select '8177188 ', '2 ', 'A ' union all
select '8177188 ', '3 ', 'A ' union all
select '5802201 ', '1 ', 'A ' union all
select '5802201 ', '2 ', 'A ' union all
select '5802201 ', '3 ', 'A ' union all
select '5962946 ', '1 ', 'B ' union all
select '5962946 ', '2 ', 'C ' union all
select '5962946 ', '3 ', 'A ' union all
select '5211982 ', '1 ', 'A ' union all
select '5211982 ', '2 ', 'B ' union all
select '5211982 ', '3 ', 'A ' union all
select '2051252 ', '1 ', 'B ' union all
select '2051252 ', '2 ', 'B ' union all
select '2051252 ', '3 ', 'A ' union all
select '7088033 ', '1 ', 'A ' union all
select '7088033 ', '2 ', 'A ' union all
select '7088033 ', '3 ', 'A ' union all
select '5571079 ', '1 ', 'A ' union all
select '5571079 ', '2 ', 'A ' union all
select '5571079 ', '3 ', 'A ' union all
select '2127366 ', '1 ', 'B ' union all
select '2127366 ', '2 ', 'B ' union all
select '2127366 ', '3 ', 'A ' union all
select '7994307 ', '1 ', 'C ' union all
select '7994307 ', '2 ', 'C ' union all
select '7994307 ', '3 ', 'B ' union all
select '5718616 ', '1 ', 'A ' union all
select '5718616 ', '2 ', 'A ' union all
select '5718616 ', '3 ', 'A ' union all
select '7110307 ', '1 ', 'A ' union all
select '7110307 ', '2 ', 'A ' union all
select '7110307 ', '3 ', 'A '
insert 答案表(programId,answer)
select '1 ', 'B ' union all
select '2 ', 'B ' union all
select '3 ', 'A '
--求解过程
select distinct userID
from 答题表 a
join 答案表 b on a.programId = b.programId
where a.answer = b.answer
group by userID
having count(1) = (select count(1) from 答案表)
--删除测试环境
drop table 答题表,答案表
/*--测试结果
userID
-----------
2051252
2127366
（所影响的行数为 2 行）
*/
[解决办法]
用關聯的效率應該更好些。
mengmou()mengmou() 的結果不正確。
[解决办法]
--其实还可以用exists。注：1号答了三道题，全对。2号答了两道题，全对。
--创建测试环境
create table 答题表(userID int,programId int,answer varchar(10))
create table 答案表(programId int,answer varchar(10))
--插入测试数据
insert 答题表(userID,programId,answer)
select '1 ', '1 ', 'B ' union all
select '1 ', '2 ', 'B ' union all
select '1 ', '3 ', 'A ' union all
select '2 ', '1 ', 'B ' union all
select '2 ', '2 ', 'B '
insert 答案表(programId,answer)
select '1 ', 'B ' union all
select '2 ', 'B ' union all
select '3 ', 'A '
--求解过程
select distinct userID
from 答题表 a
where not exists(
select 1
from 答案表 b
where not exists (
select 1
from 答题表 c
where c.programId = b.programId
and c.answer = b.answer
and c.userID = a.userID))
/*--测试结果
userID
-----------
1
（所影响的行数为 1 行）
*/
select distinct userID
from 答题表 a
where not exists(
select 1
from 答案表 b
where exists (
select 1
from 答题表 c
where c.programId = b.programId
and c.answer <> b.answer
and c.userID = a.userID))
/*--测试结果
userID
-----------
1
2
（所影响的行数为 2 行）
*/
--删除测试环境
drop table 答题表,答案表
[解决办法]
wangtiecheng已經寫出了用exists的方法，但是用exists的效率可能不夠好。
