CREATE TABLE Test1 (SalesID INT,Product CHAR(2),QTY INT)
INSERT Test1 SELECT 1,'A',1
UNION ALL SELECT 2,'A',1
UNION ALL SELECT 2,'B',1
UNION ALL SELECT 3,'A',1
UNION ALL SELECT 3,'B',1
UNION ALL SELECT 3,'C',1
UNION ALL SELECT 4,'A',1
UNION ALL SELECT 4,'B',1
UNION ALL SELECT 4,'C',1
UNION ALL SELECT 4,'D',2
UNION ALL SELECT 5,'A',2
UNION ALL SELECT 6,'A',1
UNION ALL SELECT 6,'B',2
UNION ALL SELECT 7,'D',1
go
select
产品数=ProductCon,count(1) as 记录数
from
(select SalesID,count(distinct Product)ProductCon
from Test1 a group by SalesID having sum(QTY)=count(distinct Product))T
group by ProductCon
INSERT Test1 SELECT 1,'A',1
UNION ALL SELECT 2,'A',1
UNION ALL SELECT 2,'B',1
UNION ALL SELECT 3,'A',1
UNION ALL SELECT 3,'B',1
UNION ALL SELECT 3,'C',1
UNION ALL SELECT 4,'A',1
UNION ALL SELECT 4,'B',1
UNION ALL SELECT 4,'C',1
UNION ALL SELECT 4,'D',2
UNION ALL SELECT 5,'A',2
UNION ALL SELECT 6,'A',1
UNION ALL SELECT 6,'B',2
UNION ALL SELECT 7,'D',1
go
select
产品数=ProductCon,count(1) as 记录数
from
(select SalesID,count(distinct Product)ProductCon
from Test1 a group by SalesID having sum(QTY)=count(distinct Product))T
group by ProductCon
INSERT Test1 SELECT 1,'A',1
UNION ALL SELECT 2,'A',1
UNION ALL SELECT 2,'B',1
UNION ALL SELECT 3,'A',1
UNION ALL SELECT 3,'B',1
UNION ALL SELECT 3,'C',1
UNION ALL SELECT 4,'A',1
UNION ALL SELECT 4,'B',1
UNION ALL SELECT 4,'C',1
UNION ALL SELECT 4,'D',2
UNION ALL SELECT 5,'A',2
UNION ALL SELECT 6,'A',1
UNION ALL SELECT 6,'B',2
UNION ALL SELECT 7,'D',1 select '卖'+ltrim(con)+'个产品' as 类型,count(1) as 数量
from (
select SalesID,count(1) con from test1 t
where not exists(select 1 from test1 where SalesID=t.SalesID and QTY>1)
group by SalesID)t
group by '卖'+ltrim(con)+'个产品'类型 数量
卖1个产品 2
卖2个产品 1
卖3个产品 1
--或
select
产品数=ProductCon,count(*) as 记录数from
(select count( distinct Product)ProductCon,SalesID,sum(QTY)qty from test1 group by SalesID)t
where ProductCon=qty group by ProductCon