--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([riqi] datetime,[LDHM] bigint,[LDCH] int) insert [test] select '2011-02-01',13611797573,1 union all select '2011-02-01',13611797571,1 union all select '2011-02-01',13611797572,2 union all select '2011-02-02',136117975743,3 union all select '2011-02-02',136117975743,4 union all select '2011-02-02',136117975733,1 union all select '2011-02-03',136117975723,1 union all select '2011-02-03',136117975713,1 union all select '2011-02-04',13611797571,1 union all select '2011-02-04',73611797571,5 union all select '2011-02-04',73611797577,1 union all select '2011-02-05',73611797572,1 union all select '2011-02-05',73611797576,1 --按两天统计 with t as( select CONVERT(varchar(10),[riqi],120) as [riqi], COUNT([LDHM]) as [LDHM], SUM([LDCH]) as [LDCH] from test group by CONVERT(varchar(10),[riqi],120),[riqi] ) select ltrim(DAY(a.riqi))+'-'+ltrim(day(b.riqi)) as riqi,a.LDCH+b.LDCH as LDCH, a.LDHM+b.LDHM as LDHM from t a inner join t b on b.riqi=dateadd(dd,1,a.riqi) /* riqi LDCH LDHM --------------------------- 1-2 12 6 2-3 10 5 3-4 9 5 4-5 9 5 */--按天统计 select ID=ROW_NUMBER()over(order by [riqi]), CONVERT(varchar(10),[riqi],120) as [riqi], COUNT([LDHM]) as [LDHM], SUM([LDCH]) as [LDCH] from test group by CONVERT(varchar(10),[riqi],120),[riqi] /* ID riqi LDHM LDCH 1 2011-02-01 3 4 2 2011-02-02 3 8 3 2011-02-03 2 2 4 2011-02-04 3 7 5 2011-02-05 2 2 */ --> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([A] int,[B] int,[C] datetime,[D] int,[E] int) insert [test] select 19,1,'2012-05-14',46,30 union all select 20,2,'2012-05-15',46,10 union all select 21,2,'2012-05-16',46,5 union all select 22,1,'2012-05-16',46,20select [A],[B],[C],[D],[E], case when [B]=1 then [E] - isnull((select SUM([E]) from test b where b.[A]>a.[A] and b.A<(select min(A) from test c where c.A>a.A and c.B=1)),0) end as F from test a/* A B C D E F 19 1 2012-05-14 00:00:00.000 46 30 15 20 2 2012-05-15 00:00:00.000 46 10 NULL 21 2 2012-05-16 00:00:00.000 46 5 NULL 22 1 2012-05-16 00:00:00.000 46 20 20 */
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([A] int,[B] int,[C] datetime,[D] int,[E] int) insert [test] select 19,1,'2012-05-14',46,30 union all select 20,2,'2012-05-15',46,10 union all select 21,2,'2012-05-16',46,5 union all select 22,1,'2012-05-16',46,20select [A],[B],[C],[D],[E], case when [B]=1 then [E] - isnull((select SUM([E]) from test b where b.[A]>a.[A] and b.A<(select min(A) from test c where c.A>a.A and c.B=1)),0) end as F from test a/* A B C D E F 19 1 2012-05-14 00:00:00.000 46 30 15 20 2 2012-05-15 00:00:00.000 46 10 NULL 21 2 2012-05-16 00:00:00.000 46 5 NULL 22 1 2012-05-16 00:00:00.000 46 20 20 */贴错了,怎么把全部都贴上了,汗
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([riqi] datetime,[LDHM] bigint,[LDCH] int)
insert [test]
select '2011-02-01',13611797573,1 union all
select '2011-02-01',13611797571,1 union all
select '2011-02-01',13611797572,2 union all
select '2011-02-02',136117975743,3 union all
select '2011-02-02',136117975743,4 union all
select '2011-02-02',136117975733,1 union all
select '2011-02-03',136117975723,1 union all
select '2011-02-03',136117975713,1 union all
select '2011-02-04',13611797571,1 union all
select '2011-02-04',73611797571,5 union all
select '2011-02-04',73611797577,1 union all
select '2011-02-05',73611797572,1 union all
select '2011-02-05',73611797576,1
--按两天统计
with t
as(
select
CONVERT(varchar(10),[riqi],120) as [riqi],
COUNT([LDHM]) as [LDHM],
SUM([LDCH]) as [LDCH]
from
test
group by
CONVERT(varchar(10),[riqi],120),[riqi]
)
select
ltrim(DAY(a.riqi))+'-'+ltrim(day(b.riqi)) as riqi,a.LDCH+b.LDCH as LDCH,
a.LDHM+b.LDHM as LDHM
from t a inner join t b on b.riqi=dateadd(dd,1,a.riqi)
/*
riqi LDCH LDHM
---------------------------
1-2 12 6
2-3 10 5
3-4 9 5
4-5 9 5
*/--按天统计
select
ID=ROW_NUMBER()over(order by [riqi]),
CONVERT(varchar(10),[riqi],120) as [riqi],
COUNT([LDHM]) as [LDHM],
SUM([LDCH]) as [LDCH]
from
test
group by
CONVERT(varchar(10),[riqi],120),[riqi]
/*
ID riqi LDHM LDCH
1 2011-02-01 3 4
2 2011-02-02 3 8
3 2011-02-03 2 2
4 2011-02-04 3 7
5 2011-02-05 2 2
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([A] int,[B] int,[C] datetime,[D] int,[E] int)
insert [test]
select 19,1,'2012-05-14',46,30 union all
select 20,2,'2012-05-15',46,10 union all
select 21,2,'2012-05-16',46,5 union all
select 22,1,'2012-05-16',46,20select [A],[B],[C],[D],[E],
case when [B]=1 then [E] -
isnull((select SUM([E]) from test b where b.[A]>a.[A]
and b.A<(select min(A) from test c where c.A>a.A and c.B=1)),0) end as F
from test a/*
A B C D E F
19 1 2012-05-14 00:00:00.000 46 30 15
20 2 2012-05-15 00:00:00.000 46 10 NULL
21 2 2012-05-16 00:00:00.000 46 5 NULL
22 1 2012-05-16 00:00:00.000 46 20 20
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([A] int,[B] int,[C] datetime,[D] int,[E] int)
insert [test]
select 19,1,'2012-05-14',46,30 union all
select 20,2,'2012-05-15',46,10 union all
select 21,2,'2012-05-16',46,5 union all
select 22,1,'2012-05-16',46,20select [A],[B],[C],[D],[E],
case when [B]=1 then [E] -
isnull((select SUM([E]) from test b where b.[A]>a.[A]
and b.A<(select min(A) from test c where c.A>a.A and c.B=1)),0) end as F
from test a/*
A B C D E F
19 1 2012-05-14 00:00:00.000 46 30 15
20 2 2012-05-15 00:00:00.000 46 10 NULL
21 2 2012-05-16 00:00:00.000 46 5 NULL
22 1 2012-05-16 00:00:00.000 46 20 20
*/贴错了,怎么把全部都贴上了,汗
求个access 写法 case 可以用switch isnull 用什么