公司表company
产品表product
关系表
companyid productid
A 产品A
A 产品B
B 产品A
B 产品C
现在要查出所有生产完全相同产品的公司,例如
只生产A、B两种产品的公司有A,B
只生产A、B、C三种产品的公司有C,D
只生产B、C、D三种产品的公司有E,F,G
求SQL
产品表product
关系表
companyid productid
A 产品A
A 产品B
B 产品A
B 产品C
现在要查出所有生产完全相同产品的公司,例如
只生产A、B两种产品的公司有A,B
只生产A、B、C三种产品的公司有C,D
只生产B、C、D三种产品的公司有E,F,G
求SQL
http://blog.csdn.net/beirut/article/details/6995871
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 3,'a' union all
select 3,'b' union all
select 3,'c' union all
select 4,'a' union all
select 4,'b' union all
select 4,'d' union all
select 5,'a' union all
select 5,'b' union all
select 5,'c' union all
select 5,'d' with cte as
(select t1.name,(select convert(nvarchar(10),id)+',' from tb where tb.name=t1.name order by id for XML path('')) as idlist
from (select distinct name from tb) as t1)select cte.name,cte.idlist from cte
join
(select idlist
from cte
group by idlist
having count(idlist)>1) as t2 on cte.idlist=t2.idlist
order by cte.idlist
他这个sql需要传入一个具体的作为比较的id,例如我传入公司A,它找到所有和公司A生产同样产品的公司,但是我这里是要列出所有,也就是这个公司A,也是通过sql自己找出来的。
if OBJECT_ID('product') is not null drop table product
create table product(companyid nvarchar(1),product_Name nvarchar(20))insert into product
select 'A','产品A' union
select 'A','产品B' union
select 'A','产品C' union
select 'B','产品D' union
select 'B','产品E' union
select 'C','产品D' union
select 'C','产品E' union
select 'D','产品G' union
select 'D','产品A' union
select 'E','产品G' union
select 'F','产品G' ;with P as(
select companyid,
stuff((select ','+product_Name from product where companyid=c.companyid for XML path('')),1,1,'') as product_Name
from product AS c group by companyid
)
select stuff((select ','+
companyid from P where product_Name=p1.product_Name for XML path('')),1,1,'') as companyid
,product_Name from P AS p1 group by product_Name having COUNT(product_Name)>1
/*
companyid product_Name
--------- ---------
B,C 产品D,产品E
E,F 产品G
*/