每天一条数据,用量=今天-昨天。
如何一条语句计算给定日期区间,此用户每天的用量
数据
============
用户 值 日期
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
........
如何一条语句计算给定日期区间,此用户每天的用量
数据
============
用户 值 日期
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
........
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 行受影响)
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.日期)=0DROP TABLE DEMO
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的环境,无语了
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/*
a 0 2012-01-01 00:00:00.000
a 30 2012-01-02 00:00:00.000
a 20 2012-01-03 00:00:00.000
a 20 2012-01-04 00:00:00.000
a 20 2012-01-05 00:00:00.000
a 10 2012-01-06 00:00:00.000
*/前提条件是数据中日期连续...
CREATE TABLE Table13
(
UserName VARCHAR(100) NOT NULL,
UserData INT NOT NULL,
UseDate VARCHAR(10)
)
GOINSERT INTO Table13
SELECT 'a',200,'2012-1-1' UNION
SELECT 'a',230,'2012-1-2' UNION
SELECT 'a',250,'2012-1-3' UNION
SELECT 'a',270,'2012-1-4' UNION
SELECT 'a',290,'2012-1-5' UNION
SELECT '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 UseDate
FROM 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
这个with aa as (
select row_number()over(order by b.a2)as na, dateadd(dd,-1,a.a3)a3,isnull(b.a1,'a')a1,isnull(b.a2,0)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,aa.a3,bb.a2-aa.a2 from aa ,bb where na=nb
with aa as (
select row_number()over(order by b.a2)as na, dateadd(dd,-1,a.a3)a3,isnull(b.a1,'a')a1,isnull(b.a2,0)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,aa.a3,bb.a2-aa.a2 from aa ,bb where na=nb/*
a1 a3
-------------------------------------------------- ----------------------- -----------
a 2012-01-01 00:00:00.000 200
a 2012-01-02 00:00:00.000 30
a 2012-01-03 00:00:00.000 20
a 2012-01-04 00:00:00.000 20(4 行受影响)在修改一下
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
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 是什么意思还有,我这个表里面不会只有一个用户,多个用户就不行了
其它的看看
指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询,并且在 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中,作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。
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
a 200 2012-01-01 00:00:00.000
a 230 2012-01-02 00:00:00.000
a 250 2012-01-03 00:00:00.000
a 270 2012-01-04 00:00:00.000
b 220 2012-01-04 00:00:00.000
b 230 2012-01-05 00:00:00.000上面语句结果
a 2012-01-01 00:00:00.000 0
a 2012-01-02 00:00:00.000 30
a 2012-01-03 00:00:00.000 30
a 2012-01-04 00:00:00.000 40
b 2012-01-04 00:00:00.000 -30
b 2012-01-05 00:00:00.000 -20
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
值 =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
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