求条汇总数据的SQL解决方案

求条汇总数据的SQL
表结构

id date remark
UUID 2012-02-12 OOXX
UUID 2012-03-12 OOXX
UUID 2011-04-12 OOXX
UUID 2011-01-15 OOXX
UUID 2011-10-12 OOXX
UUID 2011-11-14 OOXX
UUID 2011-11-16 OOXX
UUID 2011-02-17 OOXX
UUID 2011-03-13 OOXX
UUID 2011-01-10 OOXX
UUID 2011-01-11 OOXX
UUID 2011-01-12 OOXX

求汇总后的格式为
X月
时间 1 2 3 4 5 6 7 8.。。。。。总的次数
状态如果数据库有的话返回'/'如果没有记录的话返回‘ ’

------解决方案--------------------
如果是查询某个月的,则参考如下(其中:'2011-01-01'是你的输入值):

SQL code

create table tb(id varchar(20), date varchar(20), remark varchar(20)  )
insert into tb values( 'UUID', '2012-02-12', 'OOXX')
insert into tb values( 'UUID' ,'2012-03-12', 'OOXX')
insert into tb values( 'UUID', '2011-04-12', 'OOXX')
insert into tb values( 'UUID', '2011-01-15', 'OOXX')
insert into tb values( 'UUID' ,'2011-10-12', 'OOXX')
insert into tb values( 'UUID' ,'2011-11-14', 'OOXX')
insert into tb values( 'UUID' ,'2011-11-16', 'OOXX')
insert into tb values( 'UUID' ,'2011-02-17', 'OOXX')
insert into tb values( 'UUID' ,'2011-03-13', 'OOXX')
insert into tb values( 'UUID' ,'2011-01-10', 'OOXX')
insert into tb values( 'UUID' ,'2011-01-11' ,'OOXX')
insert into tb values( 'UUID' ,'2011-01-12', 'OOXX')
go

declare @sdate datetime
declare @edate datetime
set @sdate = '2011-01-01'
set @edate = dateadd(mm,1,@sdate) - 1

declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case datepart(dd,date) when ''' + cast(date as varchar) + ''' then ''/'' else '''' end) [' + cast(date as varchar) + ']'
from  
(
select 
    top 100 percent right('0' + datename(dd,dateadd(dd,num,@sdate)),2) date
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate order by date
) as a
set @sql = @sql + ',count(1) 总的次数 from tb where datediff(mm,date,''' + convert(varchar(10),@sdate,120) + ''') = 0 group by id'
exec(@sql) 

drop table tb

/*
id                   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   总的次数        
-------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----------- 
UUID                                                              /    /    /              /                                                                                    4

*/

求条汇总数据的SQL解决方案

相关推荐