使用ROLLUP遇到的问题
SQL语句： SELECT case when (grouping(data0010.cust_code)=1) then '合计：' else isnull(data0010.cust_code,'unknown') end 客户代码,--Data0010.abbr_name 客户简称, sum(Data0060.parts_ordered) as 数量, sum(Data0060.sq) AS 面积 from data0060 inner join DATA0010 on DATA0060.CUSTOMER_PTR=DATA0010.RKEY group by data0010.cust_code with rollup --,Data0010.abbr_name ORDER BY data0010.cust_code desc得到结果：客户代码 数量 面积---------- ---------------------- ----------------------SUNSZ 300 16.535526NCASE 19040 3935.9965624NCAGB 17560 3683.5445024MTPZZ 30720 1159.65696INTZZ 3426 1361.16867256HPCIS 30240 141.665328HHNJX 98 3.42099282HESNZ 2320 390.0781184HENHZ 2000 66.41342CGDPY 9000 1437.9912ASEZZ 265400 5369.934796ADMZZ 3000 291.65085合计： 383104 17858.05692858-----现在想增加 DATA0010 表的 RKEY列和 abbr_name （客户名称）列，使结果为：rkey 客户代码 客户简称 数量 面积----------- ---------- -------------------- ---------------------- ----------------------58 SUNSZ S1012 300 16.535526113 NCASE N1010 19040 3935.9965624117 NCAGB N1011 17560 3683.544502448 MTPZZ M1013 30720 1159.6569635 INTZZ I1004 3426 1361.1686725669 HPCIS H1019 30240 141.665328127 HHNJX H1024 98 3.4209928226 HESNZ H1009 2320 390.078118425 HENHZ H1005 2000 66.41342109 CGDPY C1022 9000 1437.99123 ASEZZ A1020 265400 5369.9347961 ADMZZ A1002 3000 291.65085 合计： 383104 17858.05692858请问用rollup,cube 等如何实现？
;with ach as( SELECT case when (grouping(data0010.cust_code)=1) then '合计：' else isnull(data0010.cust_code,'unknown') end 客户代码,--Data0010.abbr_name 客户简称, sum(Data0060.parts_ordered) as 数量, sum(Data0060.sq) AS 面积 from data0060 inner join DATA0010 on DATA0060.CUSTOMER_PTR=DATA0010.RKEY group by data0010.cust_code with rollup --,Data0010.abbr_name ORDER BY data0010.cust_code desc)select a.*,b.[RKEY],b.[abbr_name]from ach a left join DATA0010 b on a.客户代码 = b.客户代码
