如何最高效地实现这个表的查询？

[diggywang]

TABLE:(3个keyColumn为主键)

keyColumn1 keyColumn2 keyColumn3 dataColumn1 dataColumn2
------------------------------------------------------------
a b c d1 d2
a b c d3 d4
a b c d5 d6
x y z d1 d2
x y z d3 E4
i m n d1 d2
i m n d4 Q4
i m n Q5 Q6
------------------------------------------------------------
现在要查询dataColumn1为d1和d3，dataColumn2为d2的keyColumn1，keyColumn2，keyColumn3的数据集，请问这个SQL语句怎么写最高效？
上面例表的结果应该是:[a,b,c]和[x,y,z],[i,m,n]的keyColumn1因为没有d3而不符合。

[diggywang]

最后一句可能写得不好,应该是：
上面例表的结果应该是:[a,b,c]和[x,y,z]。
[i,m,n]的keyColumn1因为没有d3而不符合。

[dawugui]

select * from TABLE where dataColumn2='d2' and (dataColumn1='d1' or dataColumn1='d3')

[diggywang]

家里的电脑太滥了，居然试不了，只能明天上班再看了。
老乌龟的太深奥...呵，臭虫的倒还容易理解。
我对数据库开发不是很了解，平时也就用到很简单的语句，持久层的操作很少我来做，碰到这种情况就无能为力了。很想提高一下，但又不知从何下手。这几天看SQLSERVER联机丛书，说的很详细，不错。
不知这里的高手这一路是怎么走来的，揭帖后将另开一个送分贴，希望一路过来的兄弟姐妹们一起来讨论一下

[bugchen888]

SELECT a.*
FROM
(SELECT keyColumn1, keyColumn2, keyColumn3
FROM table1
WHERE dataColumn1 IN ('d1','d3')
GROUP BY keyColumn1, keyColumn2, keyColumn3
HAVING COUNT(DISTINCT dataColumn1)>=2) a,
(SELECT DISTINCT keyColumn1, keyColumn2, keyColumn3
FROM table1
WHERE dataColumn2='d2') b
WHERE a.keyColumn1=b.keyColumn1
AND a.keyColumn2=b.keyColumn2
AND c.keyColumn3=c.keyColumn3

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
keyColumn1 varchar(10),
keyColumn2 varchar(10),
keyColumn3 varchar(10),
dataColumn1 varchar(10),
dataColumn2 varchar(10)
)

insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d3','d4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d5','d6')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d3','E4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d4','Q4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','Q5','Q6')

select * from tb where dataColumn2='d2' and (dataColumn1='d1' or dataColumn1='d3')

drop table tb

keyColumn1 keyColumn2 keyColumn3 dataColumn1 dataColumn2
---------- ---------- ---------- ----------- -----------
a b c d1 d2
x y z d1 d2
i m n d1 d2

（所影响的行数为 3 行）

[dawugui]

最后一句可能写得不好,应该是：
上面例表的结果应该是:[a,b,c]和[x,y,z]。
[i,m,n]的keyColumn1因为没有d3而不符合。
你的意思是,keyColumn1既要有d1,也要有d3?

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
keyColumn1 varchar(10),
keyColumn2 varchar(10),
keyColumn3 varchar(10),
dataColumn1 varchar(10),
dataColumn2 varchar(10)
)

insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d3','d4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d5','d6')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d3','E4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d4','Q4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','Q5','Q6')

select b.* from
(
select keyColumn1,keyColumn2,keyColumn3 from
(
select distinct keyColumn1,keyColumn2,keyColumn3
from tb
where dataColumn1 = 'd1'
union all
select distinct keyColumn1,keyColumn2,keyColumn3
from tb
where dataColumn1 = 'd3'
) t
group by keyColumn1,keyColumn2,keyColumn3
having count(*) > 1
) a,
(
select * from tb where dataColumn2 = 'd2' and (dataColumn1 = 'd1' or dataColumn1 = 'd3')
) b
where a.keyColumn1 = b.keyColumn1 and a.keyColumn2 = b.keyColumn2 and a.keyColumn3 = b.keyColumn3

drop table tb

keyColumn1 keyColumn2 keyColumn3 dataColumn1 dataColumn2
---------- ---------- ---------- ----------- -----------
a b c d1 d2
x y z d1 d2

（所影响的行数为 2 行）

[diggywang]

潇洒老乌龟速度还真快！
不过可惜上面方法不对的，我只需要[a,b,c],[x,y,z]
而[i,m,n]是不符合要求的

[diggywang]

高手，今天mark，明天周日测一下，通过立即给分!

[diggywang]

通过了，还是老乌龟的准确，臭虫的还是会产生冗余，
如再增加一列：
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','d1','aa')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','aa','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','d3','aaaaa')
则[o,p,q]也是被当成符合条件的查出来。

[bugchen888]

这个需求的SQL可以简洁一些：
SELECT keyColumn1, keyColumn2, keyColumn3
FROM table1
WHERE (dataColumn1 = 'd1' AND dataColumn2='d2')
OR (dataColumn1 = 'd3' AND dataColumn2='d2')
GROUP BY keyColumn1, keyColumn2, keyColumn3
HAVING COUNT(DISTINCT dataColumn1)>=2

[diggywang]

哎，其实老乌龟的还是有毛病哦！
如这个表
if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
keyColumn1 varchar(10),
keyColumn2 varchar(10),
keyColumn3 varchar(10),
dataColumn1 varchar(10),
dataColumn2 varchar(10)
)

insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d3','d4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('a','b','c','d5','d6')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('x','y','z','d3','E4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d1','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','d4','Q4')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('i','m','n','Q5','Q6')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','d1','aa')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','aa','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('o','p','q','d3','aaaaa')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('e','f','g','d3','d2')
insert into tb(keyColumn1,keyColumn2,keyColumn3,dataColumn1,dataColumn2) values('e','f','g','d1','d4')

select b.* from
(
select keyColumn1,keyColumn2,keyColumn3 from
(
select distinct keyColumn1,keyColumn2,keyColumn3
from tb
where dataColumn1 = 'd1'
union all
select distinct keyColumn1,keyColumn2,keyColumn3
from tb
where dataColumn1 = 'd3'
) t
group by keyColumn1,keyColumn2,keyColumn3
having count(*) > 1
) a,
(
select * from tb where dataColumn2 = 'd2' and (dataColumn1 = 'd1' or dataColumn1 = 'd3')
) b
where a.keyColumn1 = b.keyColumn1 and a.keyColumn2 = b.keyColumn2 and a.keyColumn3 = b.keyColumn3

drop table tb

就会把[e,f,g]也选出来。
也怪我，没说清楚，其实我还有个条件，d1和d2必须是同一行的

[diggywang]

多虑了多虑了，稍稍修改就好，多谢！