34,593
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:@tb
declare @tb table([id] int,[teamid] int,[team] varchar(6),[carcode] varchar(5),[shuliang] numeric(7,4),[shijian] datetime)
insert @tb
select 1,1,'厂一队','00001',100.0000,'2012-6-12 0:00:00' union all
select 2,1,'厂一队','00001',200.0000,'2012-6-13 0:00:00' union all
select 3,2,'厂二队','00002',300.0000,'2012-6-12 0:00:00' union all
select 4,2,'厂二队','00002',400.0000,'2012-6-13 0:00:00' union all
select 5,1,'厂一队','00002',100.0000,'2012-6-12 0:00:00' union all
select 6,1,'厂一队','00002',200.0000,'2012-6-13 0:00:00' union all
select 7,3,'厂三队','00010',500.0000,'2012-6-12 0:00:00'
--SQL语句
--得到每个team,carcode的当天及累计数量
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY teamid,team ORDER BY teamid),
teamid,team,carcode,
SUM(CASE WHEN shijian >= CONVERT(varchar(10),GETDATE(),120)
AND shijian < CONVERT(varchar(10),DATEADD(day,1,GETDATE()),120)
THEN shuliang ELSE 0 END) AS CurrentDay,
SUM(shuliang) AS total INTO #TMP
FROM @tb
GROUP BY teamid,team,carcode;
--得到一共有多少个team,并生成列字符串
DECLARE @s varchar(MAX);
SET @s='';
SELECT @s=@s+',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN carcode ELSE '''' END) AS ['+team+']'
+',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(CurrentDay AS numeric(12,2))) ELSE '''' END) AS [' + team+'_当天]'
+',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(total AS numeric(12,2))) ELSE '''' END) AS [' + team+'_累计]'
FROM #TMP
GROUP BY teamid,team
ORDER BY teamid;
--执行
SET @s='SELECT '+STUFF(@s,1,1,'')+' FROM #TMP GROUP BY rowid';
PRINT @s;
EXEC(@s);
--删除临时表
DROP TABLE #TMP;
/*
厂一队 厂一队_当天 厂一队_累计 厂二队 厂二队_当天 厂二队_累计 厂三队 厂三队_当天 厂三队_累计
00001 200.00 300.00 00002 400.00 700.00 00010 0.00 500.00
00002 200.00 300.00
*/
;WITH cte AS(
SELECT rowid=ROW_NUMBER() OVER(ORDER BY 单位ID)-1,
单位ID,
SUM(CASE WHEN 加油时间 >= CONVERT(varchar(10),GETDATE(),120)
AND 加油时间 < CONVERT(varchar(10),DATEADD(day,1,GETDATE()),120)
THEN 加油数量 ELSE 0 END) AS 当天加油量,
SUM(加油数量) AS 累计加油量
FROM 单位表
GROUP BY 单位ID
)
SELECT
MAX(CASE WHEN rowid%2=0 THEN 单位ID ELSE '' END) AS 单位一,
MAX(CASE WHEN rowid%2=0 THEN RTRIM(当天加油量) ELSE '' END) AS 当天加油量一,
MAX(CASE WHEN rowid%2=0 THEN RTRIM(累计加油量) ELSE '' END) AS 累计加油量一,
MAX(CASE WHEN rowid%2=1 THEN 单位ID ELSE '' END) AS 单位二,
MAX(CASE WHEN rowid%2=1 THEN RTRIM(当天加油量) ELSE '' END) AS 当天加油量二,
MAX(CASE WHEN rowid%2=1 THEN RTRIM(累计加油量) ELSE '' END) AS 累计加油量二
FROM cte
GROUP BY rowid/2