日期:2014-05-18 浏览次数:20393 次
--> 生成测试数据: @tb CREATE TABLE tb (商品id INT,商品名称 VARCHAR(4),客户名称 VARCHAR(4),日期 DATETIME,销量 INT) INSERT INTO tb SELECT 1,'a','张三','2009-1-1',1 UNION ALL SELECT 1,'a','李四','2009-1-5',2 UNION ALL SELECT 2,'b','王二','2009-1-5',3 UNION ALL SELECT 2,'b','李四','2009-1-5',5 UNION ALL SELECT 3,'c','王二','2009-1-1',6 UNION ALL SELECT 1,'a','张三','2009-2-1',8 UNION ALL SELECT 1,'a','张三','2009-2-2',2 --SQL查询如下: DECLARE @sql varchar(8000); SET @sql = ''; SELECT ID = IDENTITY(int,1,1),CONVERT(varchar(6),日期,112) AS 日期 INTO #tmp1 FROM tb GROUP BY CONVERT(varchar(6),日期,112); SELECT DISTINCT 商品名称 INTO #tmp2 FROM tb; DECLARE @id int,@日期 varchar(6); SET @id = (SELECT TOP 1 id FROM #tmp1 ORDER BY id); WHILE @id IS NOT NULL BEGIN SET @日期 = (SELECT 日期 FROM #tmp1 WHERE ID = @id); SELECT @sql = @sql + ',SUM(CASE WHEN 商品名称 = ''' + 商品名称 + ''' AND CONVERT(varchar(6),日期,112) = ''' + @日期 + ''' THEN 销量 ELSE 0 END) AS [' + @日期 + '_' + 商品名称 + '销量]' FROM #tmp2 SET @sql = @sql + ',SUM(CASE WHEN CONVERT(varchar(6),日期,112)=''' + @日期 + ''' THEN 销量 ELSE 0 END) AS [' + @日期 + '_累计销量]' SET @id = (SELECT TOP 1 id FROM #tmp1 WHERE id > @id ORDER BY id); END SELECT @sql = @sql + ',SUM(CASE WHEN 商品名称 = ''' + 商品名称 + ''' THEN 销量 ELSE 0 END) AS [' + 商品名称 + '销量]' FROM #tmp2; EXEC('SELECT ISNULL(客户名称,''累计'') AS 客户名称'+@sql+',SUM(销量) AS 累计 FROM tb GROUP BY 客户名称 WITH ROLLUP') DROP TABLE #tmp1,#tmp2; DROP TABLE tb;
------解决方案--------------------
--> 生成测试数据: @tb --drop table tb
create table Tb (商品id INT,商品名称 VARCHAR(4),客户名称 VARCHAR(4),日期 DATETIME,销量 INT)
INSERT INTO tb
SELECT 1,'a','张三','2009-1-1',1 UNION ALL
SELECT 1,'a','李四','2009-1-5',2 UNION ALL
SELECT 2,'b','王二','2009-1-5',3 UNION ALL
SELECT 2,'b','李四','2009-1-5',5 UNION ALL
SELECT 3,'c','王二','2009-1-1',6 UNION ALL
SELECT 1,'a','张三','2009-2-1',8 UNION ALL
SELECT 1,'a','张三','2009-2-2',2
select distinct a.商品id,a.商品名称,b.日期 into #a from tb a
cross join(select distinct CONVERT(varchar(6),日期,112) as 日期 from tb)b
declare @sql nvarchar(4000)
SET @sql=N'select isnull([客户名称],N''总计'') as 客户名称' --初始化变量必须
select @sql=@sql+N','+
QUOTENAME(日期+N'_'+a.商品名称+N'销量')+
N'=sum(
case when [商品名称]='+quotename(a.商品名称,N'''')+