加个排序字段sort: select *,(select count(*) from t where 日期=a.日期 and 時間<=a.時間) sort from t a order by 日期,sort剩下的group by datepart(ww,日期),sort后再横排自己搞定
已经搞定,因为数据和复杂,和Realgz讨论了一上午,最后的处理方法是: 按照时间顺序,把所有数据重新生成ID,放到新的表中,然后再排序输出! select IDENTITY (int,1,1) AS id,ActDate,UnitName,Speaker,Location,WorkTime,TrainType into v_v_ActionUnitDoor from v_ActionUnitDoor order by ActDate--------------------------------------------------------------------------------------select dbo.f_GetDatePart(min(ActDate)) DayPart , WeekNo,id, min(Mon_U) as Mon_U,min(Mon_S) as Mon_S,min(Mon_L) as Mon_L,min(Mon_T) as Mon_T, min(Tue_U) as Tue_U,min(Tue_S) as Tue_S,min(Tue_L) as Tue_L,min(Tue_T) as Tue_T, min(Wed_U) as Wed_U,min(Wed_S) as Wed_S,min(Wed_L) as Wed_L,min(Wed_T) as Wed_T, min(Thu_U) as Thu_U,min(Thu_S) as Thu_S,min(Thu_L) as Thu_L,min(Thu_T) as Thu_T, min(Fri_U) as Fri_U,min(Fri_S) as Fri_S,min(Fri_L) as Fri_L,min(Fri_T) as Fri_T, min(Sat_U) as Sat_U,min(Sat_S) as Sat_S,min(Sat_L) as Sat_L,min(Sat_T) as Sat_T from ( select actDate, id=(select count(*) from v_v_ActionUnitDoor where Year(ActDate)=Year(a.ActDate) and month(ActDate)=month(a.ActDate) and day(ActDate)=day(a.ActDate) and id<a.id ), datepart(wk,A.actDate) WeekNo, case when datepart(dw,ActDate)=2 then UnitName else null end as Mon_U, case when datepart(dw,ActDate)=2 then Speaker else null end as Mon_S, case when datepart(dw,ActDate)=2 then Location else null end as Mon_L, case when datepart(dw,ActDate)=2 then WorkTime else null end as Mon_T, case when datepart(dw,ActDate)=3 then UnitName else null end as Tue_U, case when datepart(dw,ActDate)=3 then Speaker else null end as Tue_S, case when datepart(dw,ActDate)=3 then Location else null end as Tue_L, case when datepart(dw,ActDate)=3 then WorkTime else null end as Tue_T,case when datepart(dw,ActDate)=4 then UnitName else null end as Wed_U, case when datepart(dw,ActDate)=4 then Speaker else null end as Wed_S, case when datepart(dw,ActDate)=4 then Location else null end as Wed_L, case when datepart(dw,ActDate)=4 then WorkTime else null end as Wed_T, case when datepart(dw,ActDate)=5 then UnitName else null end as Thu_U, case when datepart(dw,ActDate)=5 then Speaker else null end as Thu_S, case when datepart(dw,ActDate)=5 then Location else null end as Thu_L, case when datepart(dw,ActDate)=5 then WorkTime else null end as Thu_T,case when datepart(dw,ActDate)=6 then UnitName else null end as Fri_U, case when datepart(dw,ActDate)=6 then Speaker else null end as Fri_S, case when datepart(dw,ActDate)=6 then Location else null end as Fri_L, case when datepart(dw,ActDate)=6 then WorkTime else null end as Fri_T,case when datepart(dw,ActDate)=7 then UnitName else null end as Sat_U, case when datepart(dw,ActDate)=7 then Speaker else null end as Sat_S, case when datepart(dw,ActDate)=7 then Location else null end as Sat_L, case when datepart(dw,ActDate)=7 then WorkTime else null end as Sat_T from v_v_ActionUnitDoor a where Year(ActDate)=2004 and Month(ActDate)=7
) as t group by datepart(ww,ActDate),id,WeekNo
将本贴总结如下sql if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DigJim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DigJim] GOCREATE TABLE [dbo].[DigJim] ( [编号] [int] NULL , [日期] [datetime] NULL , [姓名] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [时间] [varchar] (50) COLLATE Chinese_PRC_BIN NULL ) ON [PRIMARY] GO insert DigJim values('1','2003-9-1','AAA','8:00') insert DigJim values('2','2000-9-2','BBB','15:00') insert DigJim values('3','2000-9-3','CCC','10:00') insert DigJim values('4','2000-9-3','DDD','10:00') insert DigJim values('5','2000-9-3','AAA','12:00') insert DigJim values('6','2000-9-4','EEE','19:00') insert DigJim values('7','2000-9-6','BBB','09:00') insert DigJim values('8','2000-9-7','BBB','12:00') insert DigJim values('9','2000-9-7','CCC','8:00') insert DigJim values('10','2000-9-7','EEE','13:00') insert DigJim values('11','2000-9-8','AAA','14:00') insert DigJim values('12','2000-9-9','BBB','9:00') insert DigJim values('13','2000-9-10','DDD','12:00') insert DigJim values('14','2000-9-11','CCC','8:00') select *,(select count(*) from DigJim where 日期=a.日期 and( (时间=a.时间 and 编号<a.编号) or (时间<=a.时间))) sort
into DigJim1 from DigJim a order by 日期,sortselect 日期,sort,datepart(ww,日期)as 周,case when datepart(dw,日期)=2 then 姓名 else null end as 星期一, case when datepart(dw,日期)=2 then 时间 else null end as 时间1, case when datepart(dw,日期)=3 then 姓名 else null end as 星期二, case when datepart(dw,日期)=3 then 时间 else null end as 时间2, case when datepart(dw,日期)=4 then 姓名 else null end as 星期三, case when datepart(dw,日期)=4 then 时间 else null end as 时间3, case when datepart(dw,日期)=5 then 姓名 else null end as 星期四, case when datepart(dw,日期)=5 then 时间 else null end as 时间4, case when datepart(dw,日期)=6 then 姓名 else null end as 星期五, case when datepart(dw,日期)=6 then 时间 else null end as 时间5, case when datepart(dw,日期)=7 then 姓名 else null end as 星期六, case when datepart(dw,日期)=7 then 时间 else null end as 时间6 into DigJim2 from DigJim1 a order by datepart(ww,日期),sortselect sort, min(星期一) as 星期一, min(时间1) as 时间1, min(星期二) as 星期二, min(时间2) as 时间2, min(星期三) as 星期三, min(时间3) as 时间3, min(星期四) as 星期四, min(时间4) as 时间4, min(星期五) as 星期五, min(时间5) as 时间5, min(星期六) as 星期六, min(时间6) as 时间6, datepart(ww,日期)as 周 into N from DigJim2 group by datepart(ww,日期),sort order by min(日期)select Max(日期) as 大日期,min(日期)as 小日期,datepart(ww,日期) as 周 into NN from DigJim group by datepart(ww,日期)select N.*, datename(day,NN.小日期)+'-'+datename(day,NN.大日期) as 时间范围 from N inner join NN on N.周=NN.周 drop table N,NN,DigJim1,DigJim2
在这个地方加一个时间排序是没有用的,因为结果已经出来了,只能对一个星期的数据排序!
编号 日期 姓名 時間 sort
1 2004-9-1 AAA 08:00 1
2 2004-9-2 BBB 15:00 2
2 2004-9-2 AAA 10:00 1
3 2004-9-3 CCC 10:00 2
4 2004-9-3 DDD 08:00 1
5 2004-9-3 AAA 12:00 3
6 2004-9-4 EEE 09:30 1
....不知道理解得对不对
select *,(select count(*) from t where 日期=a.日期 and 時間<=a.時間) sort
from t a order by 日期,sort剩下的group by datepart(ww,日期),sort后再横排自己搞定
按照时间顺序,把所有数据重新生成ID,放到新的表中,然后再排序输出!
select IDENTITY (int,1,1) AS id,ActDate,UnitName,Speaker,Location,WorkTime,TrainType
into v_v_ActionUnitDoor
from v_ActionUnitDoor
order by ActDate--------------------------------------------------------------------------------------select dbo.f_GetDatePart(min(ActDate)) DayPart , WeekNo,id,
min(Mon_U) as Mon_U,min(Mon_S) as Mon_S,min(Mon_L) as Mon_L,min(Mon_T) as Mon_T,
min(Tue_U) as Tue_U,min(Tue_S) as Tue_S,min(Tue_L) as Tue_L,min(Tue_T) as Tue_T,
min(Wed_U) as Wed_U,min(Wed_S) as Wed_S,min(Wed_L) as Wed_L,min(Wed_T) as Wed_T,
min(Thu_U) as Thu_U,min(Thu_S) as Thu_S,min(Thu_L) as Thu_L,min(Thu_T) as Thu_T,
min(Fri_U) as Fri_U,min(Fri_S) as Fri_S,min(Fri_L) as Fri_L,min(Fri_T) as Fri_T,
min(Sat_U) as Sat_U,min(Sat_S) as Sat_S,min(Sat_L) as Sat_L,min(Sat_T) as Sat_T
from (
select
actDate,
id=(select count(*) from v_v_ActionUnitDoor where Year(ActDate)=Year(a.ActDate) and month(ActDate)=month(a.ActDate)
and day(ActDate)=day(a.ActDate)
and id<a.id ),
datepart(wk,A.actDate) WeekNo,
case when datepart(dw,ActDate)=2 then UnitName else null end as Mon_U,
case when datepart(dw,ActDate)=2 then Speaker else null end as Mon_S,
case when datepart(dw,ActDate)=2 then Location else null end as Mon_L,
case when datepart(dw,ActDate)=2 then WorkTime else null end as Mon_T, case when datepart(dw,ActDate)=3 then UnitName else null end as Tue_U,
case when datepart(dw,ActDate)=3 then Speaker else null end as Tue_S,
case when datepart(dw,ActDate)=3 then Location else null end as Tue_L,
case when datepart(dw,ActDate)=3 then WorkTime else null end as Tue_T,case when datepart(dw,ActDate)=4 then UnitName else null end as Wed_U,
case when datepart(dw,ActDate)=4 then Speaker else null end as Wed_S,
case when datepart(dw,ActDate)=4 then Location else null end as Wed_L,
case when datepart(dw,ActDate)=4 then WorkTime else null end as Wed_T, case when datepart(dw,ActDate)=5 then UnitName else null end as Thu_U,
case when datepart(dw,ActDate)=5 then Speaker else null end as Thu_S,
case when datepart(dw,ActDate)=5 then Location else null end as Thu_L,
case when datepart(dw,ActDate)=5 then WorkTime else null end as Thu_T,case when datepart(dw,ActDate)=6 then UnitName else null end as Fri_U,
case when datepart(dw,ActDate)=6 then Speaker else null end as Fri_S,
case when datepart(dw,ActDate)=6 then Location else null end as Fri_L,
case when datepart(dw,ActDate)=6 then WorkTime else null end as Fri_T,case when datepart(dw,ActDate)=7 then UnitName else null end as Sat_U,
case when datepart(dw,ActDate)=7 then Speaker else null end as Sat_S,
case when datepart(dw,ActDate)=7 then Location else null end as Sat_L,
case when datepart(dw,ActDate)=7 then WorkTime else null end as Sat_T
from v_v_ActionUnitDoor a
where Year(ActDate)=2004 and Month(ActDate)=7
) as t
group by datepart(ww,ActDate),id,WeekNo
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DigJim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DigJim]
GOCREATE TABLE [dbo].[DigJim] (
[编号] [int] NULL ,
[日期] [datetime] NULL ,
[姓名] [varchar] (10) COLLATE Chinese_PRC_BIN NULL ,
[时间] [varchar] (50) COLLATE Chinese_PRC_BIN NULL
) ON [PRIMARY]
GO
insert DigJim values('1','2003-9-1','AAA','8:00')
insert DigJim values('2','2000-9-2','BBB','15:00')
insert DigJim values('3','2000-9-3','CCC','10:00')
insert DigJim values('4','2000-9-3','DDD','10:00')
insert DigJim values('5','2000-9-3','AAA','12:00')
insert DigJim values('6','2000-9-4','EEE','19:00')
insert DigJim values('7','2000-9-6','BBB','09:00')
insert DigJim values('8','2000-9-7','BBB','12:00')
insert DigJim values('9','2000-9-7','CCC','8:00')
insert DigJim values('10','2000-9-7','EEE','13:00')
insert DigJim values('11','2000-9-8','AAA','14:00')
insert DigJim values('12','2000-9-9','BBB','9:00')
insert DigJim values('13','2000-9-10','DDD','12:00')
insert DigJim values('14','2000-9-11','CCC','8:00')
select *,(select count(*) from DigJim where 日期=a.日期 and( (时间=a.时间 and 编号<a.编号) or
(时间<=a.时间))) sort
into DigJim1 from DigJim a order by 日期,sortselect
日期,sort,datepart(ww,日期)as 周,case when datepart(dw,日期)=2 then 姓名 else null end as 星期一,
case when datepart(dw,日期)=2 then 时间 else null end as 时间1,
case when datepart(dw,日期)=3 then 姓名 else null end as 星期二,
case when datepart(dw,日期)=3 then 时间 else null end as 时间2,
case when datepart(dw,日期)=4 then 姓名 else null end as 星期三,
case when datepart(dw,日期)=4 then 时间 else null end as 时间3,
case when datepart(dw,日期)=5 then 姓名 else null end as 星期四,
case when datepart(dw,日期)=5 then 时间 else null end as 时间4,
case when datepart(dw,日期)=6 then 姓名 else null end as 星期五,
case when datepart(dw,日期)=6 then 时间 else null end as 时间5,
case when datepart(dw,日期)=7 then 姓名 else null end as 星期六,
case when datepart(dw,日期)=7 then 时间 else null end as 时间6 into DigJim2
from DigJim1 a
order by datepart(ww,日期),sortselect
sort,
min(星期一) as 星期一,
min(时间1) as 时间1,
min(星期二) as 星期二,
min(时间2) as 时间2,
min(星期三) as 星期三,
min(时间3) as 时间3,
min(星期四) as 星期四,
min(时间4) as 时间4,
min(星期五) as 星期五,
min(时间5) as 时间5,
min(星期六) as 星期六,
min(时间6) as 时间6,
datepart(ww,日期)as 周 into N
from DigJim2
group by datepart(ww,日期),sort
order by min(日期)select Max(日期) as 大日期,min(日期)as 小日期,datepart(ww,日期) as 周 into NN
from DigJim
group by datepart(ww,日期)select N.*, datename(day,NN.小日期)+'-'+datename(day,NN.大日期) as 时间范围
from N inner join NN on N.周=NN.周
drop table N,NN,DigJim1,DigJim2