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

一条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.000a                                                  20          2012-01-02 00:00:00.000a                                                  20          2012-01-03 00:00:00.000(3 行受影响)
------解决方案--------------------
SQL codeCREATE TABLE DEMO (用户 VARCHAR(100),值 INT,日期 DATETIME)INSERT INTO DEMOSELECT '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 T1LEFT JOIN DEMO T2 ON DATEDIFF(DAY,DATEADD(DAY,-1,T1.日期),T2.日期)=0DROP TABLE DEMO
------解决方案--------------------
SQL codegocreate table tbl(用户 varchar(2),值 int,日期 datetime)goinsert tblselect 'a',200,'2012-1-1' union allselect 'a',230,'2012-1-2' union allselect 'a',250,'2012-1-3' union allselect 'a',270,'2012-1-4' union allselect 'a',290,'2012-1-5' union allselect 'a',300,'2012-1-6'gocreate table #tt(编号 int identity(1,1),用户 varchar(2),值 int,日期 datetime)insert #ttselect 用户,值,日期 from tblselect 用户,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 bon a.编号=b.编号+1)t/*a    2012-01-01    0a    2012-01-02    30a    2012-01-03    20a    2012-01-04    20a    2012-01-05    20a    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