--用一个函数,一个存储过程实现,大概思路 -- 以1日为例 -- 函数大致如下 函数名:Add 输入参数:办工地点、员工姓名、时间 功能:对办工地点分类,追加“员工(时间)”信息 具体不详-- 以下部分为存储过程 DECLARE @name VARCHAR(4); DECLARE @am VARCHAR(4); DECLARE @pm VARCHAR(4);-- 定义游标 DECLARE __cursor__ CURSOR FOR SELECT 员工姓名,1am,1pm FROM 那个表;-- open OPEN __cursor__; FETCH NEXT FROM __cursor__ INTO @name,@am,@pm;WHILE @@FETCH_STATUS=0 BEGIN IF @am=@pm DBO.Add(@am,@name,'全天'); ELSE BEGIN DBO.Add(@am,@name,'上午'); DBO.Add(@pm,@name,'下午'); END FETCH NEXT FROM __cursor__ INTO @name,@am,@pm; END-- close CLOSE __cursor__; DEALLOCATE __cursor__;
我的sqlserver不支持中文(还不知道怎么做),也不支持数字开头的标志符。本人接触这个东西不久,结果也不怎么符合要求,只是希望对lz有点帮助create table kq ( Ename char(1), am1 varchar(3), pm1 varchar(3), am2 varchar(3), pm2 varchar(3), am3 varchar(3), pm3 varchar(3) ) insert into kq values('A', 'jys' , 'jys' , 'zb' , 'xj' , 'zb' , 'xj') insert into kq values('B', 'zb' , 'xj' , 'xj' , 'jys' ,'jys' , 'jys' ) insert into kq values('C', 'jys' , 'zb' , 'zb' , 'xj' , 'xj' , 'jys' )insert @tmp select Ename ,cast(substring(Etime,3,1) as int),Eplace,case substring(Etime,1,2)when 'am'then 1 when 'pm' then 2 end from ( --这里做了行转列,数据插入@tmp中 select Ename,Etime,Eplace from kq unpivot ( Eplace for Etime in ([am1],[pm1],[am2],[pm2],[am3],[pm3]) ) as upt) as u declare @j int set @j = 1 while(@j < 4) -- 每一天输出 begin print cast(@j as char(1))+'ri:'+char(10) select place +':',sname,sj from ( select date,place ,sname,(case shijian when 1 then 'shangwu' when 2 then 'xiawu' when 3 then 'quantian' end) as sj from ( select date,place,sname ,sum(shijian) as shijian from @tmp group by date,place,sname ) as A ) as B where B.date = @j order by place set @j=@j+1 end结果如下,不符合规范。 1ri: sname sj ---- ----- -------- jys: A quantian jys: C shangwu xj: B xiawu zb: B shangwu zb: C xiawu(5 row(s) affected)2ri: sname sj ---- ----- -------- jys: B xiawu xj: A xiawu xj: B shangwu xj: C xiawu zb: A shangwu zb: C shangwu(6 row(s) affected)3ri: sname sj ---- ----- -------- jys: B quantian jys: C xiawu xj: A xiawu xj: C shangwu zb: A shangwu(5 row(s) affected)
-- 以1日为例
-- 函数大致如下
函数名:Add
输入参数:办工地点、员工姓名、时间
功能:对办工地点分类,追加“员工(时间)”信息
具体不详-- 以下部分为存储过程
DECLARE @name VARCHAR(4);
DECLARE @am VARCHAR(4);
DECLARE @pm VARCHAR(4);-- 定义游标
DECLARE __cursor__ CURSOR FOR
SELECT 员工姓名,1am,1pm FROM 那个表;-- open
OPEN __cursor__;
FETCH NEXT FROM __cursor__ INTO @name,@am,@pm;WHILE @@FETCH_STATUS=0
BEGIN
IF @am=@pm
DBO.Add(@am,@name,'全天');
ELSE
BEGIN
DBO.Add(@am,@name,'上午');
DBO.Add(@pm,@name,'下午');
END FETCH NEXT FROM __cursor__ INTO @name,@am,@pm;
END-- close
CLOSE __cursor__;
DEALLOCATE __cursor__;
比如说1AM ,1PM,这两个栏位的?
如果用union来做,其实是要进行相乘的次数,数据量大的情况下,和用临时表/游标相比,速度慢的很明显。
建议不要用union来做多重的查询嵌套
(
Ename char(1),
am1 varchar(3),
pm1 varchar(3),
am2 varchar(3),
pm2 varchar(3),
am3 varchar(3),
pm3 varchar(3)
)
insert into kq values('A', 'jys' , 'jys' , 'zb' , 'xj' , 'zb' , 'xj')
insert into kq values('B', 'zb' , 'xj' , 'xj' , 'jys' ,'jys' , 'jys' )
insert into kq values('C', 'jys' , 'zb' , 'zb' , 'xj' , 'xj' , 'jys' )insert @tmp select Ename ,cast(substring(Etime,3,1) as int),Eplace,case substring(Etime,1,2)when 'am'then 1 when 'pm' then 2 end from
(
--这里做了行转列,数据插入@tmp中
select Ename,Etime,Eplace from kq
unpivot
(
Eplace for Etime in ([am1],[pm1],[am2],[pm2],[am3],[pm3])
) as upt) as u
declare @j int
set @j = 1
while(@j < 4) -- 每一天输出
begin print cast(@j as char(1))+'ri:'+char(10)
select place +':',sname,sj from
(
select date,place ,sname,(case shijian when 1 then 'shangwu' when 2 then 'xiawu' when 3 then 'quantian' end) as sj
from
(
select date,place,sname ,sum(shijian) as shijian from @tmp group by date,place,sname
) as A
) as B
where B.date = @j order by place set @j=@j+1
end结果如下,不符合规范。
1ri: sname sj
---- ----- --------
jys: A quantian
jys: C shangwu
xj: B xiawu
zb: B shangwu
zb: C xiawu(5 row(s) affected)2ri: sname sj
---- ----- --------
jys: B xiawu
xj: A xiawu
xj: B shangwu
xj: C xiawu
zb: A shangwu
zb: C shangwu(6 row(s) affected)3ri: sname sj
---- ----- --------
jys: B quantian
jys: C xiawu
xj: A xiawu
xj: C shangwu
zb: A shangwu(5 row(s) affected)