create table DigJim (
编号 int,
日期 datetime,
姓名 varchar(10)
)
goinsert DigJim
select
1, '2004-9-1', 'AAA'
union all select
2, '2004-9-2', 'BBB'
union all select
3, '2004-9-3', 'CCC'
union all select
4, '2004-9-3', 'DDD'
union all select
5, '2004-9-3', 'AAA'
union all select
6, '2004-9-4', 'EEE'
union all select
7, '2004-9-5', 'AAA'
union all select
8, '2004-9-6', 'BBB'
union all select
9, '2004-9-7', 'BBB'
union all select
10, '2004-9-7', 'CCC'
union all select
11, '2004-9-7', 'EEE'
union all select
12, '2004-9-8', 'AAA'
union all select
13, '2004-9-9', 'BBB'
union all select
14, '2004-9-10', 'DDD'
union all select
15, '2004-9-11', 'CCC'goselect
datename(day,min(日期))+'-'+datename(day,max(日期)) as 日期范围,
min(case when datepart(dw,日期)=1 then 姓名 else null end) as 星期一,
min(case when datepart(dw,日期)=2 then 姓名 else null end) as 星期二,
min(case when datepart(dw,日期)=3 then 姓名 else null end) as 星期三,
min(case when datepart(dw,日期)=4 then 姓名 else null end) as 星期四,
min(case when datepart(dw,日期)=5 then 姓名 else null end) as 星期五,
min(case when datepart(dw,日期)=6 then 姓名 else null end) as 星期六
from DigJim
group by datepart(ww,日期)
order by min(日期)/*
结果
日期范围 星期一 星期二 星期三 星期四 星期五 星期六
------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
1-4 NULL NULL NULL AAA BBB AAA
5-11 AAA BBB BBB AAA BBB DDD
*/
编号 int,
日期 datetime,
姓名 varchar(10)
)
goinsert DigJim
select
1, '2004-9-1', 'AAA'
union all select
2, '2004-9-2', 'BBB'
union all select
3, '2004-9-3', 'CCC'
union all select
4, '2004-9-3', 'DDD'
union all select
5, '2004-9-3', 'AAA'
union all select
6, '2004-9-4', 'EEE'
union all select
7, '2004-9-5', 'AAA'
union all select
8, '2004-9-6', 'BBB'
union all select
9, '2004-9-7', 'BBB'
union all select
10, '2004-9-7', 'CCC'
union all select
11, '2004-9-7', 'EEE'
union all select
12, '2004-9-8', 'AAA'
union all select
13, '2004-9-9', 'BBB'
union all select
14, '2004-9-10', 'DDD'
union all select
15, '2004-9-11', 'CCC'goselect
datename(day,min(日期))+'-'+datename(day,max(日期)) as 日期范围,
min(case when datepart(dw,日期)=1 then 姓名 else null end) as 星期一,
min(case when datepart(dw,日期)=2 then 姓名 else null end) as 星期二,
min(case when datepart(dw,日期)=3 then 姓名 else null end) as 星期三,
min(case when datepart(dw,日期)=4 then 姓名 else null end) as 星期四,
min(case when datepart(dw,日期)=5 then 姓名 else null end) as 星期五,
min(case when datepart(dw,日期)=6 then 姓名 else null end) as 星期六
from DigJim
group by datepart(ww,日期)
order by min(日期)/*
结果
日期范围 星期一 星期二 星期三 星期四 星期五 星期六
------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
1-4 NULL NULL NULL AAA BBB AAA
5-11 AAA BBB BBB AAA BBB DDD
*/
1、星期一应该datepart(dw,日期)=2,这个改很快
2、楼主要求多行,没看清楚
7 2003-9-5 AAA
显示结果的第一列我简化了一点,留给你自己搞定:create table T(id int,dt datetime,n varchar(3))
insert T select 1, '2003-9-1', 'AAA'
union select 2, '2003-9-2', 'BBB'
union select 3, '2003-9-3', 'CCC'
union select 4, '2003-9-3', 'DDD'
union select 5, '2003-9-3', 'AAA'
union select 6, '2003-9-4', 'EEE'
union select 7, '2003-9-5', 'AAA'
union select 8, '2003-9-6', 'BBB'
union select 9, '2003-9-7', 'BBB'
union select 10, '2003-9-7', 'CCC'
union select 11, '2003-9-7', 'EEE'
union select 12, '2003-9-8', 'AAA'
union select 13, '2003-9-9', 'BBB'
union select 14, '2003-9-10', 'DDD'
union select 15, '2003-9-11', 'CCC'select datepart(wk,dt)
,[一]=max(case when datepart(weekday,dt)=2 then n else '' end)
,[二]=max(case when datepart(weekday,dt)=3 then n else '' end)
,[三]=max(case when datepart(weekday,dt)=4 then n else '' end)
,[四]=max(case when datepart(weekday,dt)=5 then n else '' end)
,[五]=max(case when datepart(weekday,dt)=6 then n else '' end)
,[六]=max(case when datepart(weekday,dt)=7 then n else '' end)
from t a join ( select id,r=( select count(*)
from t
where datepart(wk,dt)=datepart(wk,c.dt)
and datepart(weekday,dt)=datepart(weekday,c.dt)
and id<=c.id )
from t c
) b
on a.id=b.id
group by datepart(wk,dt),r
order by datepart(wk,dt),rdrop table t另,你的日期好象是2004年的不是2003年的才对。
case when month(dateadd(day,-datepart(dw,min(日期))+2,min(日期)))=month(min(日期)) then datename(day,dateadd(day,-datepart(dw,min(日期))+2,min(日期)))
else '1'
end+'-'+case when month(dateadd(day,7-datepart(dw,min(日期)),min(日期)))=month(min(日期)) then datename(day,dateadd(day,7-datepart(dw,min(日期)),min(日期)))
else datename(day,dateadd(day,-datepart(day,dateadd(month,1,min(日期))),dateadd(month,1,min(日期))))
end as 日期范围,
id,
min(星期一) as 星期一,
min(星期二) as 星期二,
min(星期三) as 星期三,
min(星期四) as 星期四,
min(星期五) as 星期五,
min(星期六) as 星期六
from (
select
日期,
id=(select count(*) from DigJim where 日期=a.日期 and 编号<=a.编号),
case when datepart(dw,日期)=2 then 姓名 else null end as 星期一,
case when datepart(dw,日期)=3 then 姓名 else null end as 星期二,
case when datepart(dw,日期)=4 then 姓名 else null end as 星期三,
case when datepart(dw,日期)=5 then 姓名 else null end as 星期四,
case when datepart(dw,日期)=6 then 姓名 else null end as 星期五,
case when datepart(dw,日期)=7 then 姓名 else null end as 星期六
from DigJim a
) as t
group by datepart(ww,日期),id
order by min(日期)结果:
日期范围 id 星期一 星期二 星期三 星期四 星期五 星期六
------------------------------------------------------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
1-4 1 NULL NULL AAA BBB CCC EEE
1-4 2 NULL NULL NULL NULL DDD NULL
1-4 3 NULL NULL NULL NULL AAA NULL
6-11 1 BBB BBB AAA BBB DDD CCC
6-11 2 NULL CCC NULL NULL NULL NULL
6-11 3 NULL EEE NULL NULL NULL NULL