----建立表
create table table1(Emp varchar(10),[time] datetime )
-----插入数据
insert into table1
select '0001', '2005-06-01 08:30'
union select '0001', '2005-06-01 12:30'
union select '0001','2005-06-01 14:30'
union select '0001','2005-06-01 18:30'
union select '0001','2005-06-01 20:30'
union select '0001','2005-06-03 08:30'
union select '0001','2005-06-03 12:30'
union select '0001','2005-06-03 14:30'
union select '0001','2005-06-03 18:30'
union select '0001','2005-06-03 20:30'
union select '0002','2005-06-01 08:40'
union select '0002','2005-06-01 12:40'
union select '0002' ,'2005-06-01 14:40'
union select '0002','2005-06-01 18:40'
union select '0002','2005-06-01 20:40'
----建立函数
CREATE FUNCTION [dbo].[get_date] (@str_1 varchar(10),@newdate datetime)
RETURNS varchar(2000) AS
BEGIN
declare @str_2 VARCHAR(2000)
declare @name1 datetime
select @str_2=''
select @name1=''
declare aa CURSOR for
SELECT [time] FROM table1 WHERE Emp = @str_1 and convert(char(10),[time],120)=convert(char(10),@newdate,120)
open aa
fetch next from aa into @name1
WHILE @@FETCH_STATUS = 0
begin
select @str_2=@str_2+' '+convert(char(10),@name1,108)
fetch next from aa into @name1
end
return @str_1+' '+convert(char(10),@name1,120) + @str_2
END-----查询
select dbo.get_date(emp,min([time])) from table1 group by emp,convert(char(10),[time],120) order by emp
-----结果
0001 2005-06-01 08:30:00 12:30:00 14:30:00 18:30:00 20:30:00
0001 2005-06-03 08:30:00 12:30:00 14:30:00 18:30:00 20:30:00
0002 2005-06-01 08:40:00 12:40:00 14:40:00 18:40:00 20:40:00 (所影响的行数为 3 行)
create table table1(Emp varchar(10),[time] datetime )
-----插入数据
insert into table1
select '0001', '2005-06-01 08:30'
union select '0001', '2005-06-01 12:30'
union select '0001','2005-06-01 14:30'
union select '0001','2005-06-01 18:30'
union select '0001','2005-06-01 20:30'
union select '0001','2005-06-03 08:30'
union select '0001','2005-06-03 12:30'
union select '0001','2005-06-03 14:30'
union select '0001','2005-06-03 18:30'
union select '0001','2005-06-03 20:30'
union select '0002','2005-06-01 08:40'
union select '0002','2005-06-01 12:40'
union select '0002' ,'2005-06-01 14:40'
union select '0002','2005-06-01 18:40'
union select '0002','2005-06-01 20:40'
----建立函数
CREATE FUNCTION [dbo].[get_date] (@str_1 varchar(10),@newdate datetime)
RETURNS varchar(2000) AS
BEGIN
declare @str_2 VARCHAR(2000)
declare @name1 datetime
select @str_2=''
select @name1=''
declare aa CURSOR for
SELECT [time] FROM table1 WHERE Emp = @str_1 and convert(char(10),[time],120)=convert(char(10),@newdate,120)
open aa
fetch next from aa into @name1
WHILE @@FETCH_STATUS = 0
begin
select @str_2=@str_2+' '+convert(char(10),@name1,108)
fetch next from aa into @name1
end
return @str_1+' '+convert(char(10),@name1,120) + @str_2
END-----查询
select dbo.get_date(emp,min([time])) from table1 group by emp,convert(char(10),[time],120) order by emp
-----结果
0001 2005-06-01 08:30:00 12:30:00 14:30:00 18:30:00 20:30:00
0001 2005-06-03 08:30:00 12:30:00 14:30:00 18:30:00 20:30:00
0002 2005-06-01 08:40:00 12:40:00 14:40:00 18:40:00 20:40:00 (所影响的行数为 3 行)
RETURNS varchar(2000) AS
BEGIN
declare @str_2 VARCHAR(2000)
declare @name1 datetime
select @str_2=''
select @name1=''
declare aa CURSOR for
SELECT [time] FROM table1 WHERE Emp = @str_1 and convert(char(10),[time],120)=convert(char(10),@newdate,120)
open aa
fetch next from aa into @name1
WHILE @@FETCH_STATUS = 0
begin
select @str_2=@str_2+' '+convert(char(10),@name1,108)
fetch next from aa into @name1
end
return @str_1+' '+convert(char(5),@name1,120) + @str_2--------改为char(5)
END-----查询
select dbo.get_date(emp,min([time])) from table1 group by emp,convert(char(10),[time],120) order by emp
-----的到结果
0001 2005-06-01 08:30 12:30 14:30 18:30 20:30
0001 2005-06-03 08:30 12:30 14:30 18:30 20:30
0002 2005-06-01 08:40 12:40 14:40 18:40 20:40(所影响的行数为 3 行)
(Emp Varchar(10),
[time] SmallDateTime)Insert TEST Values('0001', '2005-06-01 08:30')
Insert TEST Values('0001', '2005-06-01 12:30')
Insert TEST Values('0001', '2005-06-01 14:30')
Insert TEST Values('0001', '2005-06-01 18:30')
Insert TEST Values('0001', '2005-06-01 20:30')
Insert TEST Values('0001', '2005-06-03 08:30')
Insert TEST Values('0001', '2005-06-03 12:30')
Insert TEST Values('0001', '2005-06-03 14:30')
Insert TEST Values('0001', '2005-06-03 18:30')
Insert TEST Values('0001', '2005-06-03 20:30')
Insert TEST Values('0002', '2005-06-01 08:40')
Insert TEST Values('0002', '2005-06-01 12:40')
Insert TEST Values('0002', '2005-06-01 14:40')
Insert TEST Values('0002', '2005-06-01 18:40')
Insert TEST Values('0002', '2005-06-01 20:40')
GO
Create Function GetTime(@Emp Varchar(10),@Date Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S=Convert(Varchar(16),Min([time]),120) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0
Select @S=@S+' '+Convert(Varchar(5),[time],108) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0 And [time]>(Select Min([time]) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0) Order By [time]
Return @S
End
GO
Select Emp,dbo.GetTime(Emp,Convert(Varchar(10),Min([time]),120)) As [time] from TEST Group By Emp,Convert(Varchar(10),[time],120) Order By Emp
GO
Drop Table TEST
Drop Function GetTime
GO
/*
Emp time
0001 2005-06-01 08:30 12:30 14:30 18:30 20:30
0001 2005-06-03 08:30 12:30 14:30 18:30 20:30
0002 2005-06-01 08:40 12:40 14:40 18:40 20:40
*/
Create table TEST
(Emp Varchar(10),
[time] SmallDateTime)
--插入数据
Insert TEST Values('0001', '2005-06-01 08:30')
Insert TEST Values('0001', '2005-06-01 12:30')
Insert TEST Values('0001', '2005-06-01 14:30')
Insert TEST Values('0001', '2005-06-01 18:30')
Insert TEST Values('0001', '2005-06-01 20:30')
Insert TEST Values('0001', '2005-06-03 08:30')
Insert TEST Values('0001', '2005-06-03 12:30')
Insert TEST Values('0001', '2005-06-03 14:30')
Insert TEST Values('0001', '2005-06-03 18:30')
Insert TEST Values('0001', '2005-06-03 20:30')
Insert TEST Values('0002', '2005-06-01 08:40')
Insert TEST Values('0002', '2005-06-01 12:40')
Insert TEST Values('0002', '2005-06-01 14:40')
Insert TEST Values('0002', '2005-06-01 18:40')
Insert TEST Values('0002', '2005-06-01 20:40')
GO
--建立函数
Create Function GetTime(@Emp Varchar(10),@Date Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S=@Emp+' '+Convert(Varchar(16),Min([time]),120) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0
Select @S=@S+' '+Convert(Varchar(5),[time],108) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0 And [time]>(Select Min([time]) from TEST Where Emp=@Emp And DateDiff(d,@Date,[time])=0) Order By [time]
Return Stuff(@S,1,1,'')
End
GO
--测试
Select dbo.GetTime(Emp,Convert(Varchar(10),Min([time]),120)) As [time] from TEST Group By Emp,Convert(Varchar(10),[time],120) Order By [time]
--删除测试环境
Drop Table TEST
Drop Function GetTime
--结果
/*
time
001 2005-06-01 08:30 12:30 14:30 18:30 20:30
001 2005-06-03 08:30 12:30 14:30 18:30 20:30
002 2005-06-01 08:40 12:40 14:40 18:40 20:40
*/
你的语句的效率一定比用游标好吗?