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

2012-09-14

一条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.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


 
 
/*
a02012-01-01 00:00:00.000
a302012-01-02 00:00:00.000
a202012-01-03 00:00:00.000
a202012-01-04 00:00:00.000
a202012-01-05 00:00:00.000
a102012-01-06 00:00:00.000
*/

前提条件是数据中日期连续...
[解决办法]
探讨
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 i……

[解决办法]
SQL codeCREATE TABLE Table13(    UserName VARCHAR(100) NOT NULL,    UserData INT NOT NULL,    UseDate VARCHAR(10))GOINSERT INTO Table13SELECT 'a',200,'2012-1-1' UNIONSELECT 'a',230,'2012-1-2' UNIONSELECT 'a',250,'2012-1-3' UNIONSELECT 'a',270,'2012-1-4' UNIONSELECT 'a',290,'2012-1-5' UNIONSELECT 'a',300,'2012-1-6'SELECT    A.UserName,        CASE         WHEN B.UseDate IS NULL THEN 0        ELSE (B.UserData - A.UserData) END AS UserData,        CASE         WHEN B.UseDate IS NULL THEN A.UseDate        ELSE B.UseDate END AS UseDateFROM Table13 AS A LEFT OUTER JOIN Table13 AS B ON CONVERT(VARCHAR(10),DATEADD(DAY,1,A.UseDate),23) = CONVERT(VARCHAR(10),CAST(B.UseDate AS DATETIME),23)WHERE B.UseDate IS NOT NULL
[解决办法]
今天我悲剧了
SQL codewith aa as (select row_number()over(order by b.a2)as na, dateadd(dd,-1,a.a3)a3,isnull(b.a1,a.a1)a1,isnull(b.a2,a.a2)a2 from tb a left  join  tb b on dateadd(dd,-1,a.a3)=b.a3),bb as (select row_number()over(order by a3)as nb,* from tb)select aa.a1,bb.a3,bb.a2-aa.a2 from aa ,bb where na=nb
[解决办法]
SELECT    A.UserName,
       CASE  
       WHEN B.UseDate = (SELECT MIN(C.USEDATE) FROM Table13 C) THEN 0
       ELSE (B.UserData - A.UserData) END AS UserData,
       CASE  
       WHEN B.UseDate IS NULL THEN A.UseDate
       ELSE B.UseDate END AS UseDate
FROM Table13  A ,Table13  B WHERE B.USEDATE=TO_DATE(A.USEDATE)+1 ORDER BY UserData

可能效率上有问题，参考参考，相互学习
[解决办法]
with aa as (
select row_number()over(order by b.a2)as na, dateadd(dd,-1,a.a3)a3,isnull(b.a1,a.a1)a1,isnull(b.a2,a.a2)a2 from tb a  
left  join  tb b on dateadd(dd,-1,a.a3)=b.a3
)
,bb as (select row_number()over(order by a3)as nb,* from tb)
select bb.a1,bb.a3,bb.a2-aa.a2 from aa ,bb where na=nb


[解决办法]
用户 =custcode
值   =qty
日期 =dateinput  


select a.custcode, a.dateinput, qtydif=a.qty - b.qty
from tableA a
inner join
(
select custcode, qty, dateinput
from tableA
) b on  
convert(char(10), a.dateinput, 111)=convert( char(10 ), dateadd (day, -1 , b.dateinput), 111)  
and a.custcode=b.custcode  


[解决办法]
select  
UserName,
isnull(t1.UserData-(select isnull(UserData,0) from Table13 t where dateadd(day,1,t.UseDate)=t1.UseDate),0)
from  
Table13 t1

条条大路通罗马
[解决办法]
如果日期连续：
select a.用户,a.日期,a.值-b.值
from 表 a  
left join  
表 b  
on a.用户=b.用户 and b.日期=a.日期-1

如果日期不连续：
select 用户,日期
,值-(select top 1 值 from 表 b where b.日期<a.日期 order by b.日期 desc)  
from 表 a

热点排行

SQL Server

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