一条SQL，一个月每日的用量

一条SQL，一个月每天的用量
每天一条数据，用量=今天-昨天。
如何一条语句计算给定日期区间，此用户每天的用量
数据
============
用户值日期
a 200 2012-1-1
a 230 2012-1-2
a 250 2012-1-3
a 270 2012-1-4
a 290 2012-1-5
a 300 2012-1-6
。。。。。。。。

得出如下结果
用户日期用量
a 2012-1-1 0
a 2012-1-2 30
a 2012-1-3 20
........

------解决方案--------------------
select o.用户,count(*),o.日期 FROM W_Status_log o where GROUP by o.用户,o.日期
------解决方案--------------------

探讨

select o.用户,count(*),o.日期 FROM W_Status_log o where GROUP by o.用户,o.日期

------解决方案--------------------
引用:

select o.用户,count(*),o.日期 FROM W_Status_log o GROUP by o.用户,o.日期

------解决方案--------------------

SQL code


create table tb (a1 varchar(50), a2 int,a3 datetime)
insert into tb select 'a',200,'2012-1-1'
insert into tb select 'a',230,'2012-1-2'
insert into tb select 'a',250,'2012-1-3'
insert into tb select 'a',270,'2012-1-4'


with a as 
( select row_number()over(order by a3)as na,* from tb)
,b as 
(select row_number()over(order by a3)-1 as nb,* from tb)

select b.a1,(b.a2-a.a2)a2,a.a3 from a ,b where nb=na

/*
a1                                                 a2          a3
-------------------------------------------------- ----------- -----------------------
a                                                  30          2012-01-01 00:00:00.000
a                                                  20          2012-01-02 00:00:00.000
a                                                  20          2012-01-03 00:00:00.000

(3 行受影响)

------解决方案--------------------

SQL code


CREATE TABLE DEMO (用户 VARCHAR(100),值 INT,日期 DATETIME)
INSERT INTO DEMO
SELECT 'a','200','2012-1-1'
UNION ALL 
SELECT 'a','230','2012-1-2'
UNION ALL 
SELECT 'a','250','2012-1-3'
UNION ALL 
SELECT 'a','270','2012-1-4'
UNION ALL 
SELECT 'a','290','2012-1-5'
UNION ALL 
SELECT 'a','300','2012-1-6'


SELECT T1.用户,T1.日期,CASE WHEN T2.日期 IS NULL THEN 0 ELSE T1.值-T2.值 END AS [用量]
FROM DEMO T1
LEFT JOIN DEMO T2 ON DATEDIFF(DAY,DATEADD(DAY,-1,T1.日期),T2.日期)=0

DROP TABLE DEMO

------解决方案--------------------

SQL code


go
create table tbl(
用户 varchar(2),
值 int,
日期 datetime
)
go
insert tbl
select 'a',200,'2012-1-1' union all
select 'a',230,'2012-1-2' union all
select 'a',250,'2012-1-3' union all
select 'a',270,'2012-1-4' union all
select 'a',290,'2012-1-5' union all
select 'a',300,'2012-1-6'

go
create table #tt(
编号 int identity(1,1),
用户 varchar(2),
值 int,
日期 datetime
)
insert #tt
select 用户,值,日期 from tbl


select 用户,convert(varchar(10),日期,120) as 日期,[值a]-[值b] as 数量 from(
select a.编号,a.用户,a.日期,a.值 as [值a],isnull(b.值,a.值) as [值b] from #tt a 
left join #tt b
on a.编号=b.编号+1)t
/*
a    2012-01-01    0
a    2012-01-02    30
a    2012-01-03    20
a    2012-01-04    20
a    2012-01-05    20
a    2012-01-06    10
*/


--2000的环境，无语了

------解决方案--------------------
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (用户 nvarchar(2),值 int,日期 datetime)
insert into [TB]
select 'a',200,'2012-1-1' union all
select 'a',230,'2012-1-2' union all
select 'a',250,'2012-1-3' union all
select 'a',270,'2012-1-4' union all
select 'a',290,'2012-1-5' union all
select 'a',300,'2012-1-6'

select * from [TB]



select TB.用户,isnull(TB.值 - B.值,0)  as '值',TB.日期
from TB
left join TB B on TB.日期 = B.日期 + 1

一条SQL，一个月每日的用量

相关推荐