求条汇总数据的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.。。。。。 总的次数
状态 如果数据库有的话返回'/'如果没有记录的话返回‘ ’

[dawugui]

这么显示?(所有月份都显示到31号)

月份     1 , 2, 3, 4, ...31
2011-01 
2011-02

还是每次只显示一个月的?

[勿勿]

sum (count(1)).....

[dawugui]

select convert(varchar(7),date,120) 月份,
       max(case datepart(dd,date) when 1 then '/' else '' end) [1],
       max(case datepart(dd,date) when 2 then '/' else '' end) [2],
       ...
       max(case datepart(dd,date) when 31 then '/' else '' end) [31],
       count(1) 总的次数
from tb
group by convert(varchar(7),date,120) 

[开着拖拉机泡妞]

没明白什么意思

[陈永富]

是一个考勤表 需要在月底的时候汇总在当月所有人员的考勤状况
1 2 3 4 5 6 7 。。。。。。。。总数
张三 有考勤的话显示'/'没有的话显示''
里斯

[陈永富]

只显示一个月的数据 那个数据是参数

[陈永富]

那个时间是参数

[开着拖拉机泡妞]

给点测试数据啊

[勿勿]

--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

declare @t table (id varchar(20), date varchar(20), remark varchar(20)  )
insert into @t values( 'UUID', '2012-02-12', 'OOXX')
insert into @t values( 'UUID' ,'2012-03-12', 'OOXX')
insert into @t values( 'UUID', '2011-04-12', 'OOXX')
insert into @t values( 'UUID', '2011-01-15', 'OOXX' )
insert into @t values( 'UUID' ,'2011-10-12', 'OOXX')
insert into @t values( 'UUID' ,'2011-11-14', 'OOXX')
insert into @t values( 'UUID' ,'2011-11-16', 'OOXX')
insert into @t values( 'UUID' ,'2011-02-17', 'OOXX')
insert into @t values( 'UUID' ,'2011-03-13', 'OOXX')
insert into @t values( 'UUID' ,'2011-01-10', 'OOXX')
insert into @t values( 'UUID' ,'2011-01-11' ,'OOXX')
insert into @t values( 'UUID' ,'2011-01-12', 'OOXX')
--select * from @t
select *,(select count(1) from @t where s.id=id)as 总次数 from(
select id,
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=1 and t.id=id)as '1',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=2 and t.id=id)as '2',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=3 and t.id=id)as '3',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=4 and t.id=id)as '4',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=5 and t.id=id)as '5',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=6 and t.id=id)as '6',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=7 and t.id=id)as '7',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=8 and t.id=id)as '8',
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=9 and t.id=id)as '9' ,
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=10 and t.id=id)as '10' ,
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=11 and t.id=id)as '11', 
(select  case when  count(1)>0 then '/'  else '' end as 是否有  from @t where datepart(mm,date)=12 and t.id=id)as '12' 
  from @t t 
 group by id
 )s



id                   1    2    3    4    5    6    7    8    9    10   11   12   总次数
-------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----------
UUID                 /    /    /    /                             /    /         12

(1 row(s) affected)

[陈永富]

测试数据

id date name
4028824935c6b9cb0135c68429040002 2012-02-29 00:00:00.000 胡艳花
4028824935c6b9cb0135c684f3d60005 2012-02-29 00:00:00.000 刑利娟
4028824935c6c3650135c69633f90009 2012-02-29 00:00:00.000 索桂花
4028824935c8439f0135c91eb03e0002 2012-02-29 00:00:00.000 和国华
4028824935cbde900135cbdf4cdb0002 2012-03-01 00:00:00.000 和国华
4028824935cbde900135cbdfaf4e0003 2012-03-01 00:00:00.000 朱莹莹
4028824935cbde900135cbe30be70008 2012-03-01 00:00:00.000 冯瑞卿
4028824935d0d7490135d0ddcc830003 2012-03-02 00:00:00.000 和国华
4028824935d0d7490135d0ddefe90005 2012-03-02 00:00:00.000 朱莹莹
4028824935d0d7490135d0de1d810007 2012-03-02 00:00:00.000 和国英

date 是作为参数传到SQL的显示的是一个月的各类人员的考勤

[开着拖拉机泡妞]

DECLARE @STR VARCHAR(MAX)
SET @STR=''
SELECT @STR=@STR+','+'['+LTRIM(datepart(DD,日期))+'日'+']'+'=max(CASE WHEN datepart(mm,日期)='+
QUOTENAME(datepart(DD,日期),'''')+' THEN ''/'' ELSE '''' END)' FROM TBL
group by datepart(DD,日期)
print @str
set @STR='select 员工编号'+@STR+' from #TBL group by 员工编号'
print @str
exec(@str)

[dawugui]

如果是查询某个月的,则参考如下(其中:'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')
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

*/