34,576
社区成员
发帖
与我相关
我的任务
分享
select
a.客户号,
max(a.客户名称) as 客户名称,
max(a.地址) as 地址
from 客户 a inner join 订单 b on a.客户号 = b.客户号
inner join 产品 c on c.产品号 = b.产品号
group by a.客户号
having count(distinct b.产品号) = (select count(distinct 产品号) from 产品)
SELECT * FROM 客户
WHERE 客户号 IN
(SELECT 客户号 FROM
(SELECT 客户号,COUNT(DISTINCT 产品号)个数 FROM 订单 GROUP BY 客户号)AS T
WHERE T.个数=(SELECT COUNT(*) FROM 产品))
select a.* from 客户 a
where 客户号 in
(select 客户号 from 订单 c where count(distinct c.产品号)=(select count(1) from 产品) group by 客户号)
select a.* from 客户 a
where 客户号 in
(select 客户号 from 订单 c where count(distinct c.产品号)=(select count(1) from 产品))
select distinct a.产品号,ISNULL( b.客户号,0) 客户号,isnull(c.客户名称) 客户名称,
ISNULL(c.客户地址,0) 客户地址 from 产品 a left join 订单 b
on a.产品号=b.产品号 left join 客户 c on b.客户号=c.客户号
CREATE TABLE product
(
pid int identity(1,1) primary key,
productname nvarchar(20) not null
)
CREATE TABLE orderdetail
(
did int identity(1,1) primary key,
cusid nvarchar(20) not null,
pid int
)
CREATE TABLE custom
(
cusid int identity(1,1) primary key,
cusname nvarchar(20) not null,
)
//select * from product
//select * from orderdetail
//select * from custom
INSERT INTO custom Values('客户1')
INSERT INTO custom Values('客户2')
INSERT INTO product Values('产品1')
INSERT INTO product Values('产品2')
INSERT INTO product Values('产品3')
INSERT INTO product Values('产品4')
INSERT INTO product Values('产品5')
INSERT INTO orderdetail Values(1,1)
INSERT INTO orderdetail Values(1,2)
INSERT INTO orderdetail Values(1,5)
INSERT INTO orderdetail Values(2,1)
INSERT INTO orderdetail Values(2,2)
INSERT INTO orderdetail Values(2,3)
INSERT INTO orderdetail Values(2,4)
INSERT INTO orderdetail Values(2,5)
select cusid ,cusname
from custom
Where not exists (
select pid from product where not exists
(select * from orderdetail where pid=product.pid and cusid=custom.cusid))