日期:2014-05-18  浏览次数:20565 次

按天统计数据!问题很急
表字段和数据
riqi(日期) LDHM(号码) LDCH(次数)
2011-02-01 13611797573 1
2011-02-01 13611797571 1
2011-02-01 13611797572 2
2011-02-02 136117975743 3
2011-02-02 136117975743 4
2011-02-02 136117975733 1
2011-02-03 136117975723 1
2011-02-03 136117975713 1
2011-02-04 13611797571 1
2011-02-04 73611797571 5
2011-02-04 73611797577 1
2011-02-05 73611797572 1
2011-02-05 73611797576 1
需求是 一天一统计和 两天一统计! 比如 01-02这两天数据的显示成一条记录比如:2011-02-01 6 12
02-03这两天数据的显示成一条记录:2011-02-02 5 8
03-04这两天数据的显示成一条记录(这是两天一统计的)

请大家帮帮忙 谢谢!!

------解决方案--------------------
SQL code

--> 测试数据:[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 
    ID=ROW_NUMBER()over(order by [riqi]),
    CONVERT(varchar(10),[riqi],120) as [riqi],
    COUNT([LDHM]) as [LDHM],
    COUNT([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.ID=a.ID+1
/*
riqi    LDCH    LDHM
1-2    6    6
2-3    5    5
3-4    5    5
4-5    5    5
*/