• 闪存
• 博客
• 发言 小组
• 投递 新闻
• 提问 博问
• 添加 收藏
• 文库

# SQL难题(高手进)

0 悬赏园豆：50 [已关闭问题] 关闭于 2012-06-05 18:33 A1     10       2012-10-1

A2     2        2012-10-2

A3     3        2012-10-1

A1    10                                  　　 10

A2          2                                      2

A3    3                                            3

A4                                                  0

0

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)

A1 10 NULL 10
A2 NULL 2 2
A3 3 NULL 3

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

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:

Nature Q | 园豆：342 (菜鸟二级) | 2012-06-04 23:00
0

```SELECT [type],
Name,
MAX(CASE(datepart(d, [date])) when 1 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 2 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 3 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 4 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 5 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 6 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 7 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 8 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 9 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 10 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 11 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 12 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 13 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 14 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 15 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 16 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 17 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 18 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 19 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 20 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 21 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 22 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 23 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 24 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 25 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 26 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 27 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 28 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 29 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 30 then working else 0 end) as ,
MAX(CASE(datepart(d, [date])) when 31 then working else 0 end) as ,
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]```   您需要登录以后才能回答，未注册用户请先注册