假如表TB中有两列versionid和datacode数据如下:
versionid datacode
-------------------------------------------------- --------------------------------------------------
V001 A001
V001 A002
V001 A003
V002 A001
V002 A002
V002 A004
V003 A001
V003 A002
V003 A003
V003 A004(10 行受影响)要求按datacode为条件进行查询,例如datacode为where datacode in('A001','A002','A003')
结果如下:
versionid datacode
-------------------------------------------------- --------------------------------------------------
V001 A002
V001 A003
V002 A001
V002 A002
V003 A001
V003 A002
V003 A003(7 行受影响)
我想要的A001,A002,A003共有的versionid 即 V003也就是去结果的公共部分。谢谢兄弟们
versionid datacode
-------------------------------------------------- --------------------------------------------------
V001 A001
V001 A002
V001 A003
V002 A001
V002 A002
V002 A004
V003 A001
V003 A002
V003 A003
V003 A004(10 行受影响)要求按datacode为条件进行查询,例如datacode为where datacode in('A001','A002','A003')
结果如下:
versionid datacode
-------------------------------------------------- --------------------------------------------------
V001 A002
V001 A003
V002 A001
V002 A002
V003 A001
V003 A002
V003 A003(7 行受影响)
我想要的A001,A002,A003共有的versionid 即 V003也就是去结果的公共部分。谢谢兄弟们
declare @count int
select @count =count(distinct datacode) from T1select versionid from (
select versionid,count(datacode)as Countdatacode from T1
group by versionid
) as A where Countdatacode= @count
versionid
----------
V003(所影响的行数为 1 行)
insert into tb values('V001' , 'A001')
insert into tb values('V001' , 'A002')
insert into tb values('V001' , 'A003')
insert into tb values('V002' , 'A001')
insert into tb values('V002' , 'A002')
insert into tb values('V002' , 'A004')
insert into tb values('V003' , 'A001')
insert into tb values('V003' , 'A002')
insert into tb values('V003' , 'A003')
insert into tb values('V003' , 'A004')
goselect * from tb where versionid in
(
select versionid from
(
select distinct versionid from tb where datacode = 'A001'
union all
select distinct versionid from tb where datacode = 'A002'
union all
select distinct versionid from tb where datacode = 'A003'
) t
group by versionid having count(*) = 3
)drop table tb/*
versionid datacode
---------- ----------
V001 A001
V001 A002
V001 A003
V003 A001
V003 A002
V003 A003
V003 A004(所影响的行数为 7 行)
*/
insert into tb values('V001' , 'A001')
insert into tb values('V001' , 'A002')
insert into tb values('V001' , 'A003')
insert into tb values('V002' , 'A001')
insert into tb values('V002' , 'A002')
insert into tb values('V002' , 'A004')
insert into tb values('V003' , 'A001')
insert into tb values('V003' , 'A002')
insert into tb values('V003' , 'A003')
insert into tb values('V003' , 'A004')
go
select versionid from
(
select distinct versionid from tb where datacode = 'A001'
union all
select distinct versionid from tb where datacode = 'A002'
union all
select distinct versionid from tb where datacode = 'A003'
) t
group by versionid having count(*) = 3
/*
versionid
----------
V001
V003(所影响的行数为 2 行)
*/select * from tb where versionid in
(
select versionid from
(
select distinct versionid from tb where datacode = 'A001'
union all
select distinct versionid from tb where datacode = 'A002'
union all
select distinct versionid from tb where datacode = 'A003'
) t
group by versionid having count(*) = 3
)
/*
versionid datacode
---------- ----------
V001 A001
V001 A002
V001 A003
V003 A001
V003 A002
V003 A003
V003 A004(所影响的行数为 7 行)
*/drop table tb
create table tb(versionid varchar(10) , datacode varchar(10))
insert into tb values('V001' , 'A001')
insert into tb values('V001' , 'A002')
insert into tb values('V001' , 'A003')
insert into tb values('V002' , 'A001')
insert into tb values('V002' , 'A002')
insert into tb values('V002' , 'A004')
insert into tb values('V003' , 'A001')
insert into tb values('V003' , 'A002')
insert into tb values('V003' , 'A003')
insert into tb values('V003' , 'A004')
go
--要求按datacode为条件进行查询,例如datacode为where datacode in('A001','A002','A003')
--结果如下:
--SQL code
select * from tb where datacode in('A001','A002','A003')
--想要的A001,A002,A003共有的versionid 即 V003也就是去结果的公共部分
select a.versionid
from(select distinct versionid from tb where datacode = 'A002')a
,(select distinct versionid from tb where datacode = 'A003')b
,(select distinct versionid from tb where datacode = 'A003')c
where a.versionid=b.versionid and c.versionid=b.versionid
这是我的个人意见,你可以参考一下,如果不对你要告诉我呀,我们来共同交流一下.
declare @t table (versionid varchar(10),datacode varchar(10))
insert @t
select 'V001','A001' union
select 'V001','A002' union
select 'V001','A003' union
select 'V002','A001' union
select 'V002','A002' union
select 'V002','A004' union
select 'V003','A001' union
select 'V003','A002' union
select 'V003','A003' union
select 'V003','A004'--查询
select * from @t t where datacode in('A001','A002','A003')
and (select count(distinct datacode) from @t where versionid=t.versionid and datacode in('A001','A002','A003'))=3
/*结果
versionid datacode
---------- ----------
V001 A001
V001 A002
V001 A003
V003 A001
V003 A002
V003 A003
*/
insert into tb values('V001' , 'A001')
insert into tb values('V001' , 'A002')
insert into tb values('V001' , 'A003')
insert into tb values('V002' , 'A001')
insert into tb values('V002' , 'A002')
insert into tb values('V002' , 'A004')
insert into tb values('V003' , 'A001')
insert into tb values('V003' , 'A002')
insert into tb values('V003' , 'A003')
insert into tb values('V003' , 'A004')select * from tb where datacode in('A001','A002','A003')
and versionid in(select versionid from tb where datacode='A001')
and versionid in(select versionid from tb where datacode='A002')
and versionid in(select versionid from tb where datacode='A003')
select @count =count(distinct datacode) from T1select versionid from (
select versionid,count(datacode)as Countdatacode from T1
group by versionid
) as A where Countdatacode= @count
CREATE TABLE TB
(
versionid VARCHAR(50),
datacode VARCHAR(50)
)
GOINSERT [TB]
select 'V001','A001'
union all select 'V001','A002'
union all select 'V001','A003'
union all select 'V002','A001'
union all select 'V002','A002'
union all select 'V002','A004'
union all select 'V003','A001'
union all select 'V003','A002'
union all select 'V003','A003'
union all select 'V003','A004'SELECT versionid
FROM
(
SELECT versionid, SUM(CASE datacode
WHEN 'A001' THEN 1
WHEN 'A002' THEN 2
WHEN 'A003' THEN 4
ELSE 0
END) AS CountFlag
FROM TB where datacode in('A001','A002','A003')
GROUP BY versionid
)AS T
WHERE [CountFlag] = 7DROP TABLE tb