t1
美容师ID 美容师名
1 张三
2 李四
3 王五
t2
预约开始时间 结束时间 美容师ID 疗程名
2006-6-8 10:00 2006-6-8 11:00 2 M6
2006-6-8 12:00 2006-6-8 12:45 1 舒缓更生护理
2006-6-8 13:30 2006-6-8 15:00 3 暗疮修复疗程今天是2006-6-8
输出以下结果集
2006-6-8 张三 李四 王五
10:00 M6
10:30 M6
11:00
11:30
12:00 舒缓更生护理
12:30 舒缓更生护理
13:00
13:30 暗疮修复疗程
14:00 暗疮修复疗程
14:30 暗疮修复疗程
15:00
15:30
16:00
......
22:00
美容师ID 美容师名
1 张三
2 李四
3 王五
t2
预约开始时间 结束时间 美容师ID 疗程名
2006-6-8 10:00 2006-6-8 11:00 2 M6
2006-6-8 12:00 2006-6-8 12:45 1 舒缓更生护理
2006-6-8 13:30 2006-6-8 15:00 3 暗疮修复疗程今天是2006-6-8
输出以下结果集
2006-6-8 张三 李四 王五
10:00 M6
10:30 M6
11:00
11:30
12:00 舒缓更生护理
12:30 舒缓更生护理
13:00
13:30 暗疮修复疗程
14:00 暗疮修复疗程
14:30 暗疮修复疗程
15:00
15:30
16:00
......
22:00
create table t1
(
id int,
name varchar(10)
)create table t2
(
begdate smalldatetime,
enddate smalldatetime,
id int,
sname varchar(50)
)insert into t1 select 1, '张三'
insert into t1 select 2, '李四'
insert into t1 select 3, '王五'insert into t2 select '2006-6-8 10:00', '2006-6-8 11:00', 2, 'M6'
insert into t2 select '2006-6-8 12:00', '2006-6-8 12:45', 1, '舒缓更生护理'
insert into t2 select '2006-6-8 13:30', '2006-6-8 15:00', 3, '暗疮修复疗程'
--执行交叉表查询
declare @s varchar(8000)
set @s = 'select adate'
select
@s = @s+',['+a.name+']=(case when name='''+a.name+''' then sname else '''+''' end)'
from
(select top 100 name from t1 order by id )aset @s = @s + ' from (select adate,name,sname from (select begdate as adate,id,sname from t2 union all select begdate,id,sname from t2)b,t1
where t1.id = b.id )c order by adate'
exec(@s)--结果
2006-06-08 10:00:00 M6
2006-06-08 10:00:00 M6
2006-06-08 12:00:00 舒缓更生护理
2006-06-08 12:00:00 舒缓更生护理
2006-06-08 13:30:00 暗疮修复疗程
2006-06-08 13:30:00 暗疮修复疗程这样?
如果时间上有交叉怎么办?
麻烦再写一下吧
对的,又来请教大家了
declare @date char(10)
set @date = convert(char(10),getdate(),120)
set @sql= 'select '+char(39)+@date+char(39)+'=t.c1'
select @sql = @sql +','+t1.name+'=isnull(case t.name when'+char(39)+ t1.name+char(39)+' then sname else null end,'+char(39)+' '+char(39)+')'
from t1
set @sql = @sql+' from (select * from
(select t2.begdate,t2.enddate,t1.name,t2.sname
from t1,t2
where t1.id=t2.id) as aaa right join tempt
on aaa.begdate<=tempt.c1 and aaa.enddate>tempt.c1) as t order by t.c1'
select @sql
********************************************
其中表t1為:
id name
----------- --------
1 a
2 b
3 c
********************************************
其中表t2為:
create table t2
(
begdate smalldatetime,
enddate smalldatetime,
id int,
sname varchar(50)
)引用coolingpige的
********************************************
其中表tempt為:
2006-06-08 11:00:00.000
2006-06-08 10:00:00.000
2006-06-08 10:30:00.000
2006-06-08 11:30:00.000
2006-06-08 12:00:00.000
2006-06-08 12:30:00.000
2006-06-08 13:00:00.000
2006-06-08 13:30:00.000
2006-06-08 14:00:00.000
2006-06-08 14:30:00.000
2006-06-08 15:00:00.000
2006-06-08 15:30:00.000
2006-06-08 16:00:00.000
2006-06-08 16:30:00.000
2006-06-08 17:00:00.000
2006-06-08 17:30:00.000
2006-06-08 18:00:00.000
2006-06-08 18:30:00.000
2006-06-08 19:00:00.000
2006-06-08 19:30:00.000
2006-06-08 20:00:00.000
2006-06-08 20:30:00.000
2006-06-08 21:00:00.000
2006-06-08 21:30:00.000
2006-06-08 22:00:00.000
result:
2006-06-08 a b c
2006-06-08 10:00:00.000 M6
2006-06-08 10:30:00.000 M6
2006-06-08 11:00:00.000
2006-06-08 11:30:00.000
2006-06-08 12:00:00.000 舒缓更生护理
2006-06-08 12:30:00.000 舒缓更生护理
2006-06-08 13:00:00.000
2006-06-08 13:30:00.000 暗疮修复疗程
2006-06-08 14:00:00.000 暗疮修复疗程
2006-06-08 14:30:00.000 暗疮修复疗程
2006-06-08 15:00:00.000
2006-06-08 15:30:00.000
2006-06-08 16:00:00.000
2006-06-08 16:30:00.000
2006-06-08 17:00:00.000
2006-06-08 17:30:00.000
2006-06-08 18:00:00.000
2006-06-08 18:30:00.000
2006-06-08 19:00:00.000
2006-06-08 19:30:00.000
2006-06-08 20:00:00.000
2006-06-08 20:30:00.000
2006-06-08 21:00:00.000
2006-06-08 21:30:00.000
2006-06-08 22:00:00.000
做適當修改 可將2006-06-08 22:00:00.000形式->22:00形式
我这是每天会用到的,那样生成会产生临时数据还要定时清除的
t1,t2,tempt
只有tempt是臨時的呀.
一周删除旧数据,生成一次tempt,总觉得不是最好
不知还有更好的办法吗
--------------------------------------------------------------------------------
if exists(select * from sysobjects where name='T1') drop table T1
if exists(select * from sysobjects where name='T2') drop table T2
if exists(select * from sysobjects where name='temp') drop table temp
GO--生成测试用表数据
create table t1
(美容师id int,美容师名 varchar(10))
insert into t1 select 1,'张三'
Union all select 2,'李四'
Union all select 3,'王五'create table t2(预约开始时间 smalldatetime,结束时间 smalldatetime,美容师id int,疗程名 varchar(50))
insert t2 select '2006-6-8 10:00','2006-6-8 11:00', 2,'M6'
Union all select '2006-6-8 12:00','2006-6-8 12:45',1,'舒缓更生护理'
Union all select '2006-6-8 13:30','2006-6-8 15:00',3,'暗疮修复疗程'select * from T1
select * from T2GO
--得到一个空表结构 */
Declare @Sql varchar(8000)
set @sql='select 时间段='' '''
select @sql=@sql +','+美容师名+'=max(case 美容师名 when '''+美容师名+''' then '' '' else '' '' end)' from T1 group by 美容师名
exec (@sql+' into temp from T1 where 1>2 group by 美容师id')
--select * from temp
GO--插入10~22点的值
Declare @Dt datetime
Declare @strT varchar(8000)
set @dt='2006-06-05 10:00:00'
while @Dt<='2006-06-05 22:00:00'
BEGIN
set @strT='insert into temp(时间段) values('''+convert(varchar(5),@dt,8)+''')'
--select @strT
exec (@strT)
set @Dt=dateadd(mi,30,@Dt)
END
select * from Temp--删除临时数据
GO
drop table T1,T2,temp
----- ---- ---- ----
10:00 NULL NULL NULL
10:30 NULL NULL NULL
11:00 NULL NULL NULL
11:30 NULL NULL NULL
12:00 NULL NULL NULL
12:30 NULL NULL NULL
13:00 NULL NULL NULL
13:30 NULL NULL NULL
14:00 NULL NULL NULL
14:30 NULL NULL NULL
15:00 NULL NULL NULL
15:30 NULL NULL NULL
16:00 NULL NULL NULL
16:30 NULL NULL NULL
17:00 NULL NULL NULL
17:30 NULL NULL NULL
18:00 NULL NULL NULL
18:30 NULL NULL NULL
19:00 NULL NULL NULL
19:30 NULL NULL NULL
20:00 NULL NULL NULL
20:30 NULL NULL NULL
21:00 NULL NULL NULL
21:30 NULL NULL NULL
22:00 NULL NULL NULL(所影响的行数为 25 行)
这个只是用昨天我答的贴中套上去的,一会儿我改造一下,搞成你想要的.
我就是没想到怎么套上去,所以来请教大家的
麻烦,帮我想一下,谢了
(
t_time varchar(5)
)insert into t_time
select '00:00' union
select '00:30' union
select '01:00' union
select '01:30' union
select '02:00' union
select '02:30' union
select '03:00' union
select '03:30' union
select '04:00' union
select '04:30' union
select '05:00' union
select '05:30' union
select '06:00' union
select '06:30' union
select '07:00' union
select '07:30' union
select '08:00' union
select '08:30' union
select '09:00' union
select '09:30' union
select '10:00' union
select '10:30' union
select '11:00' union
select '11:30' union
select '12:00' union
select '12:30' union
select '13:00' union
select '13:30' union
select '14:00' union
select '14:30' union
select '15:00' union
select '15:30' union
select '16:00' union
select '16:30' union
select '17:00' union
select '17:30' union
select '18:00' union
select '18:30' union
select '19:00' union
select '19:30' union
select '20:00' union
select '20:30' union
select '21:00' union
select '21:30' union
select '22:00' union
select '22:30' union
select '23:00' union
select '23:30' union
select '24;00'
create table t_name
(
id int,
name1 varchar(20)
)insert into t_name
select 1, '张三' union
select 2, '李四' union
select 3, '王五'create table t_hairdressing
(
bdate datetime,
edate datetime,
personid int,
pname varchar(20)
)insert into t_hairdressing
select '2006-6-8 10:00', '2006-6-8 11:00', 2, 'M6' union
select '2006-6-8 12:00', '2006-6-8 12:45', 1, '舒缓更生护理' union
select '2006-6-8 13:30', '2006-6-8 15:00', 3, '暗疮修复疗程'select substring(convert(varchar(5),bdate, 108),1,2) + substring( convert(varchar(5),bdate, 108),4,2) bdate,
substring(convert(varchar(5),edate, 108),1,2) + substring( convert(varchar(5),edate, 108),4,2) edate,
name1 ,
pname
from #declare @s varchar(8000)
set @s = ''select @s = @s +',' + ' max(case when convert(varchar(5),b.bdate,108) < a.t_time and a.t_time < convert(varchar(5),b.edate,108) and b.name1 = ''' + name1 +'''then b.pname else '' '' end) as ' + name1 from #
group by name1set @s = 'select a.t_time ' + @s + ' from t_time a left join # b on 1 = 1 group by a.t_time'exec(@s)
是
select bdate , edate ,
name1 , pname
into #
from t_name , t_hairdressing
where t_name.id = t_hairdressing.personid
max(case when convert(varchar(5),b.bdate,108) < a.t_time
应修改为
max(case when convert(varchar(5),b.bdate,108) <= a.t_time
还有大家有效率更高的办法吗
if exists(select * from sysobjects where name='T2') drop table T2
if exists(select * from sysobjects where name='temp') drop table temp
GO/*生成测试用表数据*/
create table t1
(美容师id int,美容师名 varchar(10))
insert into t1 select 1,'张三'
Union all select 2,'李四'
Union all select 3,'王五'create table t2(预约开始时间 smalldatetime,结束时间 smalldatetime,美容师id int,疗程名 varchar(50))
insert t2 select '2006-6-8 10:00','2006-6-8 11:00', 2,'M6'
Union all select '2006-6-8 12:00','2006-6-8 12:45',1,'舒缓更生护理'
Union all select '2006-6-8 13:30','2006-6-8 15:00',3,'暗疮修复疗程'/*--select * from T1
--select * from T2
--select convert(varchar(5),预约开始时间,8) from T2*/
GO
/*--得到一个空表结构 */
Declare @Sql varchar(8000)
set @sql='select 时间段='' '''
select @sql=@sql +','+美容师名+'=max(case 美容师名 when '''+美容师名+''' then '' '' else '' '' end)' from T1 group by 美容师名
exec (@sql+' into temp from T1 where 1>2 group by 美容师id')
GO/*--插入10~22点的值 */
Declare @Dt datetime
Declare @strT varchar(8000)
set @dt='2006-06-08 10:00:00'
while @Dt<='2006-06-08 22:00:00'
BEGIN
set @strT='insert into temp(时间段) values('''+convert(varchar(5),@dt,8)+''')'
exec(@strT)
set @Dt=dateadd(mi,30,@Dt)
END
GOalter table temp alter column 张三 varchar(20)
alter table temp alter column 李四 varchar(20)
alter table temp alter column 王五 varchar(20)
goDECLARE @dStartTime datetime,@dEndTime datetime
DECLARE @cName varchar(50),@lc varchar(50)
DECLARE Tmp_cursor CURSOR FOR
SELECT 预约开始时间,结束时间,美容师名,疗程名
FROM T1,T2 WHERE T1.美容师ID=T2.美容师ID
/*打开游标*/
OPEN Tmp_cursor
/*处理*/
FETCH FROm Tmp_cursor INTO @dStartTime,@dEndTime,@cName,@lc
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @sql varchar(8000)
SET @sql='UPDATE TEMP SET '+@cName+'='''+@lc+''' from TEMP where convert(datetime,cast(''2006-06-08 ''+时间段 as varchar),120) between '''
SET @sql=@sql+ convert(varchar(50),@dStartTime,120) + ''' and '''+convert(varchar(50),@dEndTime,120)+''''
print @sql
exec(@sql)
FETCH NEXT FROM Tmp_cursor INTO @dStartTime,@dEndTime,@cName,@lc
END
/*关闭游标*/
CLOSE Tmp_cursor
DEALLOCATE Tmp_cursor
select * from Temp
/*删除临时数据*/
GO
drop table T1,T2,temp
----- -------------------- -------------------- --------------------
10:00 M6 NULL NULL
10:30 M6 NULL NULL
11:00 M6 NULL NULL
11:30 NULL NULL NULL
12:00 NULL NULL 舒缓更生护理
12:30 NULL NULL 舒缓更生护理
13:00 NULL NULL NULL
13:30 NULL 暗疮修复疗程 NULL
14:00 NULL 暗疮修复疗程 NULL
14:30 NULL 暗疮修复疗程 NULL
15:00 NULL 暗疮修复疗程 NULL
15:30 NULL NULL NULL
16:00 NULL NULL NULL
16:30 NULL NULL NULL
17:00 NULL NULL NULL
17:30 NULL NULL NULL
18:00 NULL NULL NULL
18:30 NULL NULL NULL
19:00 NULL NULL NULL
19:30 NULL NULL NULL
20:00 NULL NULL NULL
20:30 NULL NULL NULL
21:00 NULL NULL NULL
21:30 NULL NULL NULL
22:00 NULL NULL NULL
(所影响的行数为 25 行)
也即是,你这样的功能需求,通常是在程序中实现的。
我这个结果是要显示在一个grid中的,还是在sql中执行完比较高效
(美容师id int,美容师名 nvarchar(10))
insert into t1 select 1,'张三'
Union all select 2,'李四'
Union all select 3,'王五'create table t2(预约开始时间 smalldatetime,结束时间 smalldatetime,美容师id int,疗程名 nvarchar(50))
insert t2 select '2006-6-8 10:00','2006-6-8 11:00', 2,'M6'
Union all select '2006-6-8 12:00','2006-6-8 12:45',1,'舒缓更生护理'
Union all select '2006-6-8 13:30','2006-6-8 15:00',3,'暗疮修复疗程'
GODeclare @Sql varchar(8000)
set @sql='select 时间段='' '''
select @sql=@sql +','+美容师名+'=max(case 美容师名 when '''+美容师名+''' then '' '' else '' '' end)' from T1 group by 美容师名
exec (@sql+' into temp from T1 where 1>2 group by 美容师id')--空表字段要留出足够长度
GO/*--插入10~22点的值 */
Declare @Dt datetime
Declare @SQL2 nVARCHAR(4000)
set @dt='2006-06-08 10:00:00'
print @sql2
while @Dt<='2006-06-08 22:00:00'
BEGIN
set @SQL2=''
select @sql2=@sql2+',(select top 1 疗程名 from (SELECT 预约开始时间 ,结束时间 ,美容师名,疗程名
FROM T1,T2 WHERE T1.美容师ID=T2.美容师ID) a where 美容师名='''+[name]+''' and
convert(datetime,cast(''2006-06-08 ''+'''+convert(nvarchar(10),@dt,8)+''' as nvarchar),120) between
convert(nvarchar(20),预约开始时间,120) and convert(nvarchar(20),结束时间,120))' from syscolumns where id=object_id('temp') and colid>1
--print 'insert temp select '''+convert(nvarchar(20),@dt,8)+''''+@sql2
set @sql2='insert temp select '''+convert(nvarchar(20),@dt,8)+''''+@sql2
exec(@sql2)
set @Dt=dateadd(mi,30,@Dt)
END
GO
select * from Temp
/*删除临时数据*/
GO
drop table T1,T2,temp