SQL难题(高手进)
0
悬赏园豆：50
[已关闭问题]
关闭于 2012-06-05 18:33
现有一表数据如下
设备 加油量 加油日期
A1 10 2012-10-1
A2 2 2012-10-2
A3 3 2012-10-1
现在想得到10月份每天每台设备的加油汇总表，如下
日期
设备 1 2 3 4 5 。。。。 31 1-31
A1 10 10
A2 2 2
A3 3 3
A4 0
日统计 13 2 15
所有回答(3)
0
用pivot行列转换，下面的sql，表名叫tb1，你自己替换掉表名，然后试试
DECLARE @sql NVARCHAR(4000)
SET @sql = ''
select @sql=@sql+',['+加油日期+']' from tb1 group by 加油日期
set @sql=stuff(@sql,1,1,'')
PRINT @sql
set @sql = '
SELECT cc.*,bb.[1-31] FROM (
SELECT 设备,SUM(加油量) as [1-31] FROM
(
SELECT * FROM tb1 t
UNION
SELECT ''日统计'' as 设备,加油日期,SUM(加油量) as 加油量 FROM tb1 GROUP BY 加油日期
)
AS b group by 设备
) AS bb
INNER JOIN
(
SELECT * FROM
(
SELECT * FROM tb1 t
UNION
SELECT ''日统计'' as 设备,加油日期,SUM(加油量) as 加油量 FROM tb1 GROUP BY 加油日期
)
AS a PIVOT(sum(a.加油量) FOR a.加油日期 IN(' + @sql + ')) as c
) AS cc
on bb.设备 = cc.设备'
EXEC (@sql)
结果如下：
设备 2012-10-1 2012-10-2 1-31
A1 10 NULL 10
A2 NULL 2 2
A3 3 NULL 3
日统计 13 2 15
楼上正解，SQL2005以后可以使用pivot关键字行转列，非常方便。
0
--临时表
DECLARE @t TABLE(id varchar(4),oilMass int,refuelDate smalldatetime)
INSERT @t SELECT 'A1',10,getdate()
UNION ALL SELECT 'A2',2,getdate()+1
UNION ALL SELECT 'A3',3,getdate()+2
--测试输出
select * from @t
--sql
select id,
case when convert(varchar(10),refuelDate,120) ='2012-06-04' then oilMass else '' end as '2012-06-04' ,
case when convert(varchar(10),refuelDate,120) ='2012-06-05' then oilMass else '' end as '2012-06-05' ,
case when convert(varchar(10),refuelDate,120) ='2012-06-06' then oilMass else '' end as '2012-06-06'
from @t
union all
select '日累计',
sum(case when convert(varchar(10),refuelDate,120) ='2012-06-04' then oilMass else '' end) as '2012-06-04',
sum(case when convert(varchar(10),refuelDate,120) ='2012-06-05' then oilMass else '' end) as '2012-06-05',
sum(case when convert(varchar(10),refuelDate,120) ='2012-06-06' then oilMass else '' end) as '2012-06-06'
from @t
ps:
楼上写的比较完美~~我这个只能查看3行意思大概一样~不过我没用行列转换的函数，反正就是要拼case when 那部分~有多少列(多少天)就给拼接多少个case when
0
用行转列（呵呵，我这个方案没使用PIVOIT技术），并且使用了一个临时列type，目的是保证日统计一定是最后一条记录。
SELECT [type], Name, MAX(CASE(datepart(d, [date])) when 1 then working else 0 end) as [1], MAX(CASE(datepart(d, [date])) when 2 then working else 0 end) as [2], MAX(CASE(datepart(d, [date])) when 3 then working else 0 end) as [3], MAX(CASE(datepart(d, [date])) when 4 then working else 0 end) as [4], MAX(CASE(datepart(d, [date])) when 5 then working else 0 end) as [5], MAX(CASE(datepart(d, [date])) when 6 then working else 0 end) as [6], MAX(CASE(datepart(d, [date])) when 7 then working else 0 end) as [7], MAX(CASE(datepart(d, [date])) when 8 then working else 0 end) as [8], MAX(CASE(datepart(d, [date])) when 9 then working else 0 end) as [9], MAX(CASE(datepart(d, [date])) when 10 then working else 0 end) as [10], MAX(CASE(datepart(d, [date])) when 11 then working else 0 end) as [11], MAX(CASE(datepart(d, [date])) when 12 then working else 0 end) as [12], MAX(CASE(datepart(d, [date])) when 13 then working else 0 end) as [13], MAX(CASE(datepart(d, [date])) when 14 then working else 0 end) as [14], MAX(CASE(datepart(d, [date])) when 15 then working else 0 end) as [15], MAX(CASE(datepart(d, [date])) when 16 then working else 0 end) as [16], MAX(CASE(datepart(d, [date])) when 17 then working else 0 end) as [17], MAX(CASE(datepart(d, [date])) when 18 then working else 0 end) as [18], MAX(CASE(datepart(d, [date])) when 19 then working else 0 end) as [19], MAX(CASE(datepart(d, [date])) when 20 then working else 0 end) as [20], MAX(CASE(datepart(d, [date])) when 21 then working else 0 end) as [21], MAX(CASE(datepart(d, [date])) when 22 then working else 0 end) as [22], MAX(CASE(datepart(d, [date])) when 23 then working else 0 end) as [23], MAX(CASE(datepart(d, [date])) when 24 then working else 0 end) as [24], MAX(CASE(datepart(d, [date])) when 25 then working else 0 end) as [25], MAX(CASE(datepart(d, [date])) when 26 then working else 0 end) as [26], MAX(CASE(datepart(d, [date])) when 27 then working else 0 end) as [27], MAX(CASE(datepart(d, [date])) when 28 then working else 0 end) as [28], MAX(CASE(datepart(d, [date])) when 29 then working else 0 end) as [29], MAX(CASE(datepart(d, [date])) when 30 then working else 0 end) as [30], MAX(CASE(datepart(d, [date])) when 31 then working else 0 end) as [31], SUM(Working) as [1-31] from ( select 1 as [type], [name], ISNULL(working, 0) as working, [DATE] from Table_1 union select 2 as [type], '日统计' as [name], SUM(working) as working, [DATE] from Table_1 group by [date] ) as t where [date]>='2010-10-1' and [date]<'2010-11-1' group by [type], [Name] order by t.[type]
表结构图：
数据内容图：
运行结果图：
