时间 设备1 设备2 设备3
2012-7-1 1 1 1
2012-7-2 2 3 4
。 。 。 。
。 。 。 。
2012-7-31 10 20 30
。 。 。 。
。 。 。 。
2012-8-1 12 23 36
2012-8-2 13 25 40
2012-8-3 15 27 41
。 。 。 。
。 。 。 。
要搜索出来的是
和
时间 设备1 设备2 设备3
2012-7 设备1七月份总和 设备2七月份总和 设备3七月份总和
2012-8 设备1八月份总和 设备2八月份总和 设备3八月份总和
差
时间 设备1 设备2 设备3
2012-7 最后一天-第一天
2012-8数据是一年的,要把每个月的数据都检索出来
2012-7-1 1 1 1
2012-7-2 2 3 4
。 。 。 。
。 。 。 。
2012-7-31 10 20 30
。 。 。 。
。 。 。 。
2012-8-1 12 23 36
2012-8-2 13 25 40
2012-8-3 15 27 41
。 。 。 。
。 。 。 。
要搜索出来的是
和
时间 设备1 设备2 设备3
2012-7 设备1七月份总和 设备2七月份总和 设备3七月份总和
2012-8 设备1八月份总和 设备2八月份总和 设备3八月份总和
差
时间 设备1 设备2 设备3
2012-7 最后一天-第一天
2012-8数据是一年的,要把每个月的数据都检索出来
,sum(设备1)
,sum(设备2)
,sum(设备3)
from tb
group by convert(varchar(7),时间,120)
SELECT 时间, SUM(设备1), SUM(设备2), SUM(设备3)
FROM (
SELECT CONVERT(VARCHAR(7), 时间, 120) AS 时间, 设备1, 设备2, 设备3
FROM tb AS t
WHERE 时间 = (
SELECT MAX (时间) FROM tb WHERE CONVERT( VARCHAR (7), 时间, 120)= CONVERT( VARCHAR (7), t.时间, 120)
)
GROUP BY CONVERT(VARCHAR(7), 时间, 120)
UNION ALL
SELECT CONVERT(VARCHAR(7), 时间, 120) AS 时间, -设备1, -设备2, -设备3
FROM tb AS t
WHERE 时间 = (
SELECT MIN (时间) FROM tb WHERE CONVERT( VARCHAR (7), 时间, 120)= CONVERT( VARCHAR (7), t.时间, 120)
)
GROUP BY CONVERT(VARCHAR(7), 时间, 120)
) AS a
GROUP BY 时间
-------------------------------------------------------------------------
--差
SELECT 时间, SUM(设备1), SUM(设备2), SUM(设备3)
FROM (
SELECT CONVERT(VARCHAR(7), 时间, 120) AS 时间, 设备1, 设备2, 设备3
FROM tb AS t
WHERE 时间 = (
SELECT MAX (时间) FROM tb WHERE CONVERT( VARCHAR (7), 时间, 120)= CONVERT( VARCHAR (7), t.时间, 120)
)
GROUP BY CONVERT(VARCHAR(7), 时间, 120)
UNION ALL
SELECT CONVERT(VARCHAR(7), 时间, 120) AS 时间, -设备1, -设备2, -设备3
FROM tb AS t
WHERE 时间 = (
SELECT MIN (时间) FROM tb WHERE CONVERT( VARCHAR (7), 时间, 120)= CONVERT( VARCHAR (7), t.时间, 120)
)
GROUP BY CONVERT(VARCHAR(7), 时间, 120)
) AS a
GROUP BY 时间
declare @year int
set @year=2012
--和
select isnull(b.yearMonth,cast(@year as varchar(4))+'-'+cast(right(100+a.mn,2) as varchar(2))) 时间,
isnull(b.sum1,0) 设备1,
isnull(b.sum2,0) 设备2,
isnull(b.sum3,0) 设备3
from
(
select number as mn from master..spt_values
where number between 1 and 12 and type='P'
) a
left join
(
select convert(varchar(7),dt,120) yearMonth,sum(device1) sum1,sum(device2) sum2,sum(device3) sum3 from @test
where year(dt)=@year
group by convert(varchar(7),dt,120)
) b
on a.mn=right(b.yearMonth,2)
--差
;with cte as
(
select dt,
case when rn=2 then -1*device1 else device1 end device1,
case when rn=2 then -1*device2 else device2 end device2,
case when rn=2 then -1*device3 else device3 end device3
from
(
select row_number() over(partition by convert(varchar(7),a.dt,120) order by a.dt) rn,
a.dt,
a.device1,
a.device2,
a.device3
from @test a
inner join
(
select max(dt) maxdt,min(dt) mindt from @test
group by convert(varchar(7),dt,120)
) b
on a.dt=b.maxdt or a.dt=b.mindt
) t
)
select convert(varchar(7),dt,120) 时间,sum(device1) 设备1,sum(device2) 设备2,sum(device3) 设备3 from cte
group by convert(varchar(7),dt,120)