select cid , count(*) from ( select * from tb where cid <> 'c1' and gid in (select gid from tb where cid = 'c1') ) t group by cid having count(*) >= select count(*) from (select gid from tb where cid = 'c1') m
if object_id('pubs..tb') is not null drop table tb gocreate table tb(cid varchar(10),gid varchar(10)) insert into tb(cid,gid) values('c1','g1') insert into tb(cid,gid) values('c1','g2') insert into tb(cid,gid) values('c2','g1') insert into tb(cid,gid) values('c2','g2') insert into tb(cid,gid) values('c2','g3') insert into tb(cid,gid) values('c3','g1') insert into tb(cid,gid) values('c4','g1') insert into tb(cid,gid) values('c4','g2') insert into tb(cid,gid) values('c4','g3') go select cid from ( select * from tb where cid <> 'c1' and gid in (select gid from tb where cid = 'c1') ) t group by cid having count(*) >= (select count(*) from (select gid from tb where cid = 'c1') m)drop table tb/* cid ---------- c2 c4(所影响的行数为 2 行)*/
--try select * from 客户表 where not exists( select tmp.gid from [table] as tmp where tmp.cid='c1' and not exists(select 1 from [table] where cid=客户表.cid and gid=tmp.gid) )
--借用二楼的数据 create table ta(cid varchar(10), [name] varchar(10)) insert ta select 'c1','AA' union all select 'c2','BB' union all select 'c3','CC' union all select 'c4','DD'create table tb(cid varchar(10),gid varchar(10)) insert into tb(cid,gid) values('c1','g1') insert into tb(cid,gid) values('c1','g2') insert into tb(cid,gid) values('c2','g1') insert into tb(cid,gid) values('c2','g2') insert into tb(cid,gid) values('c2','g3') insert into tb(cid,gid) values('c3','g1') insert into tb(cid,gid) values('c4','g1') insert into tb(cid,gid) values('c4','g2') insert into tb(cid,gid) values('c4','g3')select * from ta where not exists( select tmp.gid from [tb] as tmp where tmp.cid='c1' and not exists(select 1 from [tb] where cid=ta.cid and gid=tmp.gid) )--result cid name ---------- ---------- c1 AA c2 BB c4 DD(3 row(s) affected)
select * from tb where gid in(select gid from tb where cid='c1' )
select distinct gid from tb where gid in(select gid from tb where cid='c1' )
select distinct gid from tb A where exists(select 1 from tb B where A.gid=B.gid and B.Cid='c1')
select * from 客户表 where not exists( select tmp.gid from [table] as tmp where tmp.cid='c1' and not exists(select 1 from [table] where cid=客户表.cid and gid=tmp.gid) )
(
select * from tb where cid <> 'c1' and gid in (select gid from tb where cid = 'c1')
) t
group by cid
having count(*) >= select count(*) from (select gid from tb where cid = 'c1') m
drop table tb
gocreate table tb(cid varchar(10),gid varchar(10))
insert into tb(cid,gid) values('c1','g1')
insert into tb(cid,gid) values('c1','g2')
insert into tb(cid,gid) values('c2','g1')
insert into tb(cid,gid) values('c2','g2')
insert into tb(cid,gid) values('c2','g3')
insert into tb(cid,gid) values('c3','g1')
insert into tb(cid,gid) values('c4','g1')
insert into tb(cid,gid) values('c4','g2')
insert into tb(cid,gid) values('c4','g3')
go
select cid from
(
select * from tb where cid <> 'c1' and gid in (select gid from tb where cid = 'c1')
) t
group by cid
having count(*) >= (select count(*) from (select gid from tb where cid = 'c1') m)drop table tb/*
cid
----------
c2
c4(所影响的行数为 2 行)*/
select * from 客户表
where not exists(
select tmp.gid from [table] as tmp where tmp.cid='c1'
and not exists(select 1 from [table] where cid=客户表.cid and gid=tmp.gid)
)
create table ta(cid varchar(10), [name] varchar(10))
insert ta select 'c1','AA'
union all select 'c2','BB'
union all select 'c3','CC'
union all select 'c4','DD'create table tb(cid varchar(10),gid varchar(10))
insert into tb(cid,gid) values('c1','g1')
insert into tb(cid,gid) values('c1','g2')
insert into tb(cid,gid) values('c2','g1')
insert into tb(cid,gid) values('c2','g2')
insert into tb(cid,gid) values('c2','g3')
insert into tb(cid,gid) values('c3','g1')
insert into tb(cid,gid) values('c4','g1')
insert into tb(cid,gid) values('c4','g2')
insert into tb(cid,gid) values('c4','g3')select * from ta
where not exists(
select tmp.gid from [tb] as tmp where tmp.cid='c1'
and not exists(select 1 from [tb] where cid=ta.cid and gid=tmp.gid)
)--result
cid name
---------- ----------
c1 AA
c2 BB
c4 DD(3 row(s) affected)
where not exists(
select tmp.gid from [table] as tmp where tmp.cid='c1'
and not exists(select 1 from [table] where cid=客户表.cid and gid=tmp.gid)
)