首页 新闻 搜索 专区 学院

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

彬彬@科比的主页 彬彬@科比 | 初学一级 | 园豆:43
提问于:2012-06-04 19:23
< >
分享
所有回答(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

P_Chou | 园豆:210 (菜鸟二级) | 2012-06-04 22:42

楼上正解,SQL2005以后可以使用pivot关键字行转列,非常方便。

支持(0) 反对(0) webaspx | 园豆:1987 (小虾三级) | 2012-06-05 15:44
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

Nature Q | 园豆:342 (菜鸟二级) | 2012-06-04 23:00
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]
无之无 | 园豆:5085 (大侠五级) | 2012-06-05 09:01

表结构图:

数据内容图:

运行结果图:

支持(0) 反对(0) 无之无 | 园豆:5085 (大侠五级) | 2012-06-05 09:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册