这个sql查询要怎么写

[etonchan]

有表aa如下明细：
单号 品名 销售量
101 A 2
101 B 1
102 B 1
102 C 2
102 D 2
103 A 3
103 B 2
104 A 1
104 B 1
104 C 1
105 B 2
105 C 2
105 D 2

要查询两个以上货品组合有多少单，结果如下:
组合 出现单数 总数量
A+B 3 10
B+C 3 6
C+D 2 8
B+C+D 2 11
A+B+C 1 3

要怎么写，请帮忙看看,谢谢

[ダ雨夹雪リ]

--建表
CREATE TABLE T
(
	  单号 VARCHAR(10),	品名 varchar(10),	销售量 int
)

--插入数据
INSERT INTO T VALUES(101,'A',	2)
INSERT INTO T VALUES(101,'B',	1)
INSERT INTO T VALUES(102,'B',	1)
INSERT INTO T VALUES(102,'C',	2)
INSERT INTO T VALUES(102,'D',	2)
INSERT INTO T VALUES(103,'A',	3)
INSERT INTO T VALUES(103,'B',	2)
INSERT INTO T VALUES(104,'A',	1)
INSERT INTO T VALUES(104,'B',	1)
INSERT INTO T VALUES(104,'C',	1)
INSERT INTO T VALUES(105,'B',	2)
INSERT INTO T VALUES(105,'C',	2)
INSERT INTO T VALUES(105,'D',	2)

 
--创建函数,得到该单两个以上货品组合
CREATE FUNCTION fn_getzh(@单号 VARCHAR(10))
RETURNS TABLE AS
RETURN
(
WITH CT
AS
(
SELECT 单号,品名 AS X,CONVERT(VARCHAR(max),品名) AS 品名,1 AS lv FROM T   WHERE 单号=@单号
UNION All
SELECT B.单号,B.品名,A.品名+'+'+B.品名,1+lv FROM CT A INNER JOIN (SELECT 单号,品名 FROM T   WHERE 单号=@单号) B  ON B.单号 = A.单号
WHERE A.X<B.品名
)
SELECT CT.品名 FROM CT WHERE lv>1
)

--统计结果
SELECT A.品名,COUNT(DISTINCT 单号) AS 单数,SUM( CASE WHEN CHARINDEX(T.品名,A.品名)>0   THEN 销售量 ELSE 0 END ) AS 销量
FROM T OUTER APPLY dbo.fn_getzh(单号) A 
GROUP by A.品名

[RINK_1]

WITH CTE_1
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY 单号 ORDER BY 品名) AS SEQ FROM TABLE),

CTE_2
AS
( SELECT A.单号,
         CAST(A.品名+'+'+B.品名 AS VARCHAR(10)) AS 品名组合,
		 A.销售量+B.销售量 AS 销量,
		 B.SEQ  
  FROM CTE_1 A
  JOIN CTE_1 B ON A.单号=B.单号 AND A.SEQ<B.SEQ
  UNION ALL
  SELECT A.单号,CAST(B.品名组合+'+'+A.品名 AS VARCHAR(10)),B.销量+A.销售量,A.SEQ
  FROM CTE_1 A
  JOIN CTE_2 B ON A.单号=B.单号 AND A.SEQ>B.SEQ)


SELECT 品名组合,SUM(销量) AS 销量,COUNT(DISTINCT 单号) AS 单数 FROM CTE_2
GROUP BY 品名组合
ORDER BY 品名组合