环境:sqlserver2000monthattendance表:
11月份有记录2921条,结构如下:
stafferid name 1 2 3 4 。。31天
003313 张三 上午 下午
003313 张三 夜班
003313 张三 夜班
003313 张三
003313 张三
003313 张三
002187 李四 早班
002187 李四 夜班想合并成这样:
stafferid name 1 2 3 4 。。31天
003313 张三 上夜 下午 夜
002187 李四 早夜找了一个合并和函数来做,发送速度慢的我接受不了,最少有个几分钟
而直接查询连一秒都不到,请问还有其他的办法没有
11月份有记录2921条,结构如下:
stafferid name 1 2 3 4 。。31天
003313 张三 上午 下午
003313 张三 夜班
003313 张三 夜班
003313 张三
003313 张三
003313 张三
002187 李四 早班
002187 李四 夜班想合并成这样:
stafferid name 1 2 3 4 。。31天
003313 张三 上夜 下午 夜
002187 李四 早夜找了一个合并和函数来做,发送速度慢的我接受不了,最少有个几分钟
而直接查询连一秒都不到,请问还有其他的办法没有
CREATE FUNCTION moreThenBantye(@sid varchar(10),@Flg int,@uid varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @sql1 VARCHAR(1000),@sql2 VARCHAR(1000),@sql3 VARCHAR(1000),@sql4 VARCHAR(1000),
@sql5 VARCHAR(1000),@sql6 VARCHAR(1000),@sql7 VARCHAR(1000),@sql8 VARCHAR(1000),
@sql9 VARCHAR(1000),@sql10 VARCHAR(1000),@sql11 VARCHAR(1000),@sql12 VARCHAR(1000),
@sql13 VARCHAR(1000),@sql14 VARCHAR(1000),@sql15 VARCHAR(1000),@sql16 VARCHAR(1000),
@sql17 VARCHAR(1000),@sql18 VARCHAR(1000),@sql19 VARCHAR(1000),@sql20 VARCHAR(1000),
@sql21 VARCHAR(1000),@sql22 VARCHAR(1000),@sql23 VARCHAR(1000),@sql24 VARCHAR(1000),
@sql25 VARCHAR(1000),@sql26 VARCHAR(1000),@sql27 VARCHAR(1000),@sql28 VARCHAR(1000),
@sql29 VARCHAR(1000),@sql30 VARCHAR(1000),@sql31 VARCHAR(1000) SELECT @sql1= ISNULL(@sql1+'','')+ isnull(LEFT([1],1),''),
@sql2= ISNULL(@sql2+'','')+ isnull(LEFT([2],1),''),
@sql3= ISNULL(@sql3+'','')+ isnull(LEFT([3],1),''),
@sql4= ISNULL(@sql4+'','')+ isnull(LEFT([4],1),''),
@sql5= ISNULL(@sql5+'','')+ isnull(LEFT([5],1),''),
@sql6= ISNULL(@sql6+'','')+ isnull(LEFT([6],1),''),
@sql7= ISNULL(@sql7+'','')+ isnull(LEFT([7],1),''),
@sql8= ISNULL(@sql8+'','')+ isnull(LEFT([8],1),''),
@sql9= ISNULL(@sql9+'','')+ isnull(LEFT([9],1),''),
@sql10=ISNULL(@sql10+'','')+isnull(LEFT([10],1),''),
@sql11=ISNULL(@sql11+'','')+isnull(LEFT([11],1),''),
@sql12=ISNULL(@sql12+'','')+isnull(LEFT([12],1),''),
@sql13=ISNULL(@sql13+'','')+isnull(LEFT([13],1),''),
@sql14=ISNULL(@sql14+'','')+isnull(LEFT([14],1),''),
@sql15=ISNULL(@sql15+'','')+isnull(LEFT([15],1),''),
@sql16=ISNULL(@sql16+'','')+isnull(LEFT([16],1),''),
@sql17=ISNULL(@sql17+'','')+isnull(LEFT([17],1),''),
@sql18=ISNULL(@sql18+'','')+isnull(LEFT([18],1),''),
@sql19=ISNULL(@sql19+'','')+isnull(LEFT([19],1),''),
@sql20=ISNULL(@sql20+'','')+isnull(LEFT([20],1),''),
@sql21=ISNULL(@sql21+'','')+isnull(LEFT([21],1),''),
@sql22=ISNULL(@sql22+'','')+isnull(LEFT([22],1),''),
@sql23=ISNULL(@sql23+'','')+isnull(LEFT([23],1),''),
@sql24=ISNULL(@sql24+'','')+isnull(LEFT([24],1),''),
@sql25=ISNULL(@sql25+'','')+isnull(LEFT([25],1),''),
@sql26=ISNULL(@sql26+'','')+isnull(LEFT([26],1),''),
@sql27=ISNULL(@sql27+'','')+isnull(LEFT([27],1),''),
@sql28=ISNULL(@sql28+'','')+isnull(LEFT([28],1),''),
@sql29=ISNULL(@sql29+'','')+isnull(LEFT([29],1),''),
@sql30=ISNULL(@sql30+'','')+isnull(LEFT([30],1),''),
@sql31=ISNULL(@sql31+'','')+isnull(LEFT([31],1),'')
FROM monthattendance WHERE stafferid=@sid and userid=@uid
RETURN CASE @Flg WHEN 1 THEN @sql1 WHEN 2 THEN @sql2 WHEN 3 THEN @sql3 WHEN 4 THEN @sql4 WHEN 5 THEN @sql5
WHEN 6 THEN @sql6 WHEN 7 THEN @sql7 WHEN 8 THEN @sql8 WHEN 9 THEN @sql9 WHEN 10 THEN @sql10
WHEN 11 THEN @sql11 WHEN 12 THEN @sql12 WHEN 13 THEN @sql13 WHEN 14 THEN @sql14 WHEN 15 THEN @sql15
WHEN 16 THEN @sql16 WHEN 17 THEN @sql17 WHEN 18 THEN @sql18 WHEN 19 THEN @sql19 WHEN 20 THEN @sql20
WHEN 21 THEN @sql21 WHEN 22 THEN @sql22 WHEN 23 THEN @sql23 WHEN 24 THEN @sql24 WHEN 25 THEN @sql25
WHEN 26 THEN @sql26 WHEN 27 THEN @sql27 WHEN 28 THEN @sql28 WHEN 29 THEN @sql29 WHEN 30 THEN @sql30
WHEN 31 THEN @sql31
END END
就是这样
CREATE FUNCTION moreThenBantye(@sid varchar(10),@Flg int,@uid varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @sql VARCHAR(1000)
SELECT @sql=ISNULL(@sql+'','')+
ISNULL(CASE @Flg
WHEN 1 THEN LEFT( [1],1)
WHEN 2 THEN LEFT( [2],1)
WHEN 3 THEN LEFT( [3],1)
WHEN 4 THEN LEFT( [4],1)
WHEN 5 THEN LEFT( [5],1)
WHEN 6 THEN LEFT( [6],1)
WHEN 7 THEN LEFT( [7],1)
WHEN 8 THEN LEFT( [8],1)
WHEN 9 THEN LEFT( [9],1)
WHEN 10 THEN LEFT([10],1)
WHEN 11 THEN LEFT([11],1)
WHEN 12 THEN LEFT([12],1)
WHEN 13 THEN LEFT([13],1)
WHEN 14 THEN LEFT([14],1)
WHEN 15 THEN LEFT([15],1)
WHEN 16 THEN LEFT([16],1)
WHEN 17 THEN LEFT([17],1)
WHEN 18 THEN LEFT([18],1)
WHEN 19 THEN LEFT([19],1)
WHEN 20 THEN LEFT([20],1)
WHEN 21 THEN LEFT([21],1)
WHEN 22 THEN LEFT([22],1)
WHEN 23 THEN LEFT([23],1)
WHEN 24 THEN LEFT([24],1)
WHEN 25 THEN LEFT([25],1)
WHEN 26 THEN LEFT([26],1)
WHEN 27 THEN LEFT([27],1)
WHEN 28 THEN LEFT([28],1)
WHEN 29 THEN LEFT([29],1)
WHEN 30 THEN LEFT([30],1)
WHEN 31 THEN LEFT([31],1)
END,'')
FROM monthattendance WHERE stafferid=@sid and userid=@uid
RETURN @sql
END
脚本如下:5000条记录约2秒查出
declare @Shift table
(Id int identity,
Name nvarchar(250),
Shift1 nvarchar(50),
Shift2 nvarchar(50),
Shift3 nvarchar(50),
Shift4 nvarchar(50),
Shift5 nvarchar(50),
Shift6 nvarchar(50),
Shift7 nvarchar(50)
)insert into @Shift( Name, Shift1)
select N'张三',N'上午'insert into @Shift( Name, Shift1)
select N'张三',N'下午'insert into @Shift( Name, Shift1)
select N'张三',N'夜班'insert into @Shift( Name, Shift2)
select N'张三',N'下午'insert into @Shift( Name, Shift3)
select N'张三',N'夜班'insert into @Shift( Name, Shift4)
select N'张三',N'下午'insert into @Shift( Name, Shift5)
select N'张三',N'上午'insert into @Shift( Name, Shift6)
select N'张三',N'下午'insert into @Shift( Name, Shift7)
select N'张三',N'上午'
insert into @Shift( Name, Shift7)
select N'李四',N'下午'insert into @Shift( Name, Shift6)
select N'李四',N'下午'insert into @Shift( Name, Shift5)
select N'李四',N'夜班'insert into @Shift( Name, Shift4)
select N'李四',N'上午'insert into @Shift( Name, Shift3)
select N'李四',N'上午'insert into @Shift( Name, Shift2)
select N'李四',N'上午'insert into @Shift( Name, Shift1)
select N'李四',N'上午'--select * from @Shift
--插入5000条记录
declare @i int
declare @Name nvarchar(255)
set @i=0
while @i<5000
begin
set @Name=NewID() --随机生成一个人名
insert into @Shift( Name, Shift1)
select @Name,N'上午'insert into @Shift( Name, Shift1)
select @Name,N'下午'insert into @Shift( Name, Shift1)
select @Name,N'夜班'insert into @Shift( Name, Shift2)
select @Name,N'下午'insert into @Shift( Name, Shift3)
select @Name,N'夜班'insert into @Shift( Name, Shift4)
select @Name,N'下午'insert into @Shift( Name, Shift5)
select @Name,N'上午'insert into @Shift( Name, Shift6)
select @Name,N'下午'insert into @Shift( Name, Shift7)
select @Name,N'上午'set @i=@i+1
endselect getdate()
select Name,
case shift1 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift1,
case shift2 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift2,
case shift3 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift3,
case shift4 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift4,
case shift5 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift5,
case shift6 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift6,
case shift7 when 1 then N'上午' when 2 then N'下午' when 3 then N'白班(上午,下午)' when 4 then N'夜班' when 5 then N'上午,夜班' when 6 then N'下午,夜班' when 7 then N'全班(上午,下午,夜班)' else N'缺勤' end shift7
from
(select Name,
sum(case shift1 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift1,
sum(case shift2 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift2,
sum(case shift3 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift3,
sum(case shift4 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift4,
sum(case shift5 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift5,
sum(case shift6 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift6,
sum(case shift7 when N'上午' then 1 when N'下午' then 2 when N'夜班' then 4 when null then 0 else 0 end) shift7
from @Shift
group by Name
) ShiftSum
select getdate()
select distinct name,tt=(
select isnull([字段1],'') from monthattendance
where name=t.name
FOR XML PATH('')
)
from monthattendance t