急求大神帮忙

[qq_31669825]

一张表里有菜名、门店、是否凉菜，是否海鲜，是否川菜这5个字段，要统计的表字段是：门店，种类（凉菜、海鲜、川菜可以并存），数量，总数，百分比，运用存储过程写，求大神帮忙

[Tiger_Zhao]

测试数据？期望结果？

[qq_31669825]

DISH_NAME STORE_NAME COLD_DISH_YN SEAFOOD_YN SICHUAN_CUISINE_YN
001 A 1 1 1
002 B 1 1 0
003 C 1 0 1
004 A 1 0 0
005 B 0 1 1
006 C 0 1 0
007 A 0 0 1
008 B 1 0 0
中文依次是菜品名称，门店名称，是否冷菜，是否海鲜，是否川菜
其中数字0代表否，1代表是

[qq_31669825]

期望结果 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% 中文依次是门店名称，种类，数量，总数量，百分比

[Tiger_Zhao]

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%

[qq_31669825]

谢谢大神

[qq_31669825]

CREATE PROCEDURE sp_food AS BEGIN --declare @table0 table(DISH_NAME varchar(50),STORE_NAME varchar(50),COLD_DISH_YN varchar(50),SEAFOOD_YN varchar(50),SICHUAN_CUISINE_YN varchar(50)); declare @table1 table(STORE_NAME varchar(50),lcs int,hxs int,ccs int,total int); declare @table2 table(STORE_NAME varchar(50),lcs int,hxs int,ccs int,total int); declare @table3 table(STORE_NAME varchar(50),KIND nvarchar(2),AMOUNT int,TOTAL int,BFB decimal(5,2)); --insert into @table0(DISH_NAME,STORE_NAME,COLD_DISH_YN,SEAFOOD_YN,SICHUAN_CUISINE_YN) create table table0(DISH_NAME varchar(50),STORE_NAME varchar(50),COLD_DISH_YN varchar(50),SEAFOOD_YN varchar(50),SICHUAN_CUISINE_YN varchar(50)); insert into table0(DISH_NAME,STORE_NAME,COLD_DISH_YN,SEAFOOD_YN,SICHUAN_CUISINE_YN) 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 insert into @table1(STORE_NAME,lcs,hxs,ccs,total) SELECT STORE_NAME, SUM(COLD_DISH_YN) 冷菜, SUM(SEAFOOD_YN) 海鲜, SUM(SICHUAN_CUISINE_YN) 川菜, COUNT(*) TOTAL FROM table0 GROUP BY STORE_NAME insert into @table2(STORE_NAME,lcs,hxs,ccs,total) SELECT * FROM @table1 UNPIVOT (AMOUNT FOR KIND IN (冷菜,海鲜,川菜) ) u insert into @table3(STORE_NAME,KIND,AMOUNT,TOTAL,BFB) SELECT STORE_NAME, CONVERT(nvarchar(2),KIND) KIND, AMOUNT, TOTAL, CONVERT(varchar(6), CONVERT(decimal(5,2),AMOUNT*100.0/TOTAL) )+'%' BFB FROM @table2 END GO 这里UNPIVOT (AMOUNT FOR KIND IN (冷菜,海鲜,川菜) ) u 报错说冷菜,海鲜,川菜这3个列名无效，还有u是干嘛的？ 另外 CONVERT(nvarchar(2),KIND) KIND, AMOUNT, TOTAL, CONVERT(varchar(6), CONVERT(decimal(5,2),AMOUNT*100.0/TOTAL) )+'%' BFB 这里的第一个KIND和其余的AMOUNT都说列名无效，怎么破？

[qq_31669825]

谢谢，我理解错了