我也来散分

[jyxhz]

有表table(cid,gid),表示(客户代码,物品代码)
c1,g1
c1,g2
c2,g2
c2,g3
....


求至少购买了c1客户购买的所有物品的客户号

写出一种方法给10分,多多益善

[dawugui]

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

[dawugui]

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

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')
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 行）

*/

[lzhcxc]

--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)
)

[lzhcxc]

--借用二楼的数据


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)

[jyxhz]

上面都行,还有吗?

[wgzaaa]

select * from tb where gid in(select gid from tb where cid='c1' )

[wgzaaa]

select distinct gid from tb where gid in(select gid from tb where cid='c1' )

[wgzaaa]

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)
)