報表常用語句（1）

1.      分級汇总

生成測試數據：

CREATE TABLE [dbo].[门店顺序]([地区] [nvarchar](20), [层次类型] [nchar](10), [分组] [nchar](10) ,[门店名称] [nvarchar](50) ,[营业额] [float] ,[id] [float] ,) ON [PRIMARY]GOINSERT INTO 门店顺序 VALUES('上海',6,'运营' ,      '百联世茂店',100,1)INSERT INTO 门店顺序 VALUES('上海',2,'运营' ,      '古北店',200 ,2)INSERT INTO 门店顺序 VALUES('上海',2,'运营' ,      '金茂店',300,3)INSERT INTO 门店顺序 VALUES('上海',NULL,NULL,'百联世茂店',400,4)INSERT INTO 门店顺序 VALUES('北京',1,'运营' ,      '金融街店',500,5)INSERT INTO 门店顺序 VALUES('北京',8,'运营' ,      '北京凯德店',500,6)INSERT INTO 门店顺序 VALUES('北京',1,'运营' ,      '新国贸店',700,7)INSERT INTO 门店顺序 VALUES('北京',8,'运营' ,      '新国贸店',200,8)INSERT INTO 门店顺序 VALUES('北京',3,NULL,'东方广场',300,9)INSERT INTO 门店顺序 VALUES('北京',3,NULL,'都汇天地店',500,10)GO

需求描述：

從門店由小到大汇总到分組、地區、全國。門店順序要按id升序（難點：要按指定順序排序,分組爲空的話要把這些門店單獨放地區各運營組之後）

變成

思路：看到分級汇总，就應該想到用之前熟悉過的 Grouping 函數，如果不熟悉可以去看看。先實現一個大致框架，再去調整細節，我下面給出的一個答案，大致框架就是子查詢，外面的一系列過濾與排序就是調整細節。

SELECT  层次类型,        地区,        门店名称,        营业额FROM    (SELECT    CASE WHEN GROUPING(门店名称) = 0 THEN ISNULL(MAX(层次类型), '')                         ELSE ''                    END 层次类型 ,                    CASE WHEN GROUPING(地区) = 1 THEN '全国'                         ELSE 地区                    END 地区 ,                    CASE WHEN GROUPING(门店名称) = 0 THEN 门店名称                         WHEN GROUPING(门店名称) = 1                              AND GROUPING(分组) = 0 THEN 分组                         ELSE '总计'                    END 门店名称 ,                    MAX(id) 'max_id',                    SUM(营业额) 营业额          FROM      门店顺序          GROUP BY  地区 ,分组 ,门店名称 WITH ROLLUP) aWHERE   门店名称 IS NOT NULLORDER BY max_id ,      CASE WHEN 门店名称 IN ( SELECT [门店名称] FROM 门店顺序 ) THEN 0 ELSE 1 END ,      CASE 地区 WHEN '全国' THEN 1 ELSE 0 END

2.      合併列值

生成測試數據：

CREATE TABLE tb(id VARCHAR(10), col1 VARCHAR(10), col2 VARCHAR(10), col3 VARCHAR(10))GOINSERT tbSELECT '001', 'text11', '2010-1-1', '2010-6-2' UNION ALLSELECT '001', 'text12', '2010-2-1', '2010-8-9' UNION ALLSELECT '001', 'text13', '2010-3-1', '2010-10-8' UNION ALLSELECT '002', 'text21', '2010-4-1', '2010-8-9' UNION ALLSELECT '002', 'text22', '2010-5-1', NULLGO

需求：

變成：

思路：這個之前也有熟悉過，但那只有一列要合併，這裏是多列，所以那篇文章裏的第一種方法就顯得有點蹩腳，還是for xml path 好用呀：

SELECT DISTINCT id,STUFF((SELECT ','+col1 FROM tb WHERE id = a.id FOR XML PATH('')),1,1,'') col1,STUFF((SELECT ','+col2 FROM tb WHERE id = a.id FOR XML PATH('')),1,1,'') col2,STUFF((SELECT ','+col3 FROM tb WHERE id = a.id FOR XML PATH('')),1,1,'') col3FROM tb a

3.      拆分 + 行轉列

奇葩地將

變成：

這個需求太奇葩，有必要專門寫一篇博文。請猛擊 這裏。