求条汇总数据的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'是你的输入值):
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')godeclare @sdate datetimedeclare @edate datetimeset @sdate = '2011-01-01'set @edate = dateadd(mm,1,@sdate) - 1declare @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) datefrom (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere dateadd(dd,num,@sdate)<=@edate order by date) as aset @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*/