27,582
社区成员




WITH
/* 测试数据
table1(DISH_NAME,STORE_NAME,COLD_DISH_YN,SEAFOOD_YN,SICHUAN_CUISINE_YN)AS(
SELECT '001','A',1,1,1 UNION ALL
SELECT '002','B',1,1,0 UNION ALL
SELECT '003','C',1,0,1 UNION ALL
SELECT '004','A',1,0,0 UNION ALL
SELECT '005','B',0,1,1 UNION ALL
SELECT '006','C',0,1,0 UNION ALL
SELECT '007','A',0,0,1 UNION ALL
SELECT '008','B',1,0,0
), */
a AS (
SELECT STORE_NAME,
SUM(COLD_DISH_YN) 冷菜,
SUM(SEAFOOD_YN) 海鲜,
SUM(SICHUAN_CUISINE_YN) 川菜,
COUNT(*) TOTAL
FROM table1
GROUP BY STORE_NAME
)
,b AS (
SELECT *
FROM a
UNPIVOT (AMOUNT
FOR KIND IN (冷菜,海鲜,川菜)
) u
)
SELECT STORE_NAME,
CONVERT(nvarchar(2),KIND) KIND,
AMOUNT,
TOTAL,
CONVERT(varchar(6),
CONVERT(decimal(5,2),AMOUNT*100.0/TOTAL)
)+'%' BFB
FROM b
STORE_NAME KIND AMOUNT TOTAL BFB
---------- ---- ----------- ----------- -------
A 冷菜 2 3 66.67%
A 海鲜 1 3 33.33%
A 川菜 2 3 66.67%
B 冷菜 2 3 66.67%
B 海鲜 2 3 66.67%
B 川菜 1 3 33.33%
C 冷菜 1 2 50.00%
C 海鲜 1 2 50.00%
C 川菜 1 2 50.00%