求救。這樣的SQL語句怎麼寫。就是把一天的記錄合在一條來顯示。

梦回童年001 2005-01-07 02:30:01
求救。這樣的SQL語句怎麼寫。就是把一天的記錄合在一條來顯示。
我的一個表KQCardRecord,
比如說我的記錄是這樣子的。
編號, 時間 ,            工號, 姓名  ,班次
1 2004-12-24 08:10 044693 小王 ,1
2 2004-12-24 12:17 044693 小王 ,2
3 2004-12-24 13:09 044693 小王 ,1
4 2004-12-24 22:24 044693 小王 ,2
5 2004-12-25 07:41 044693 小王 ,1
6 2004-12-25 12:01 044693 小王 ,2
7 2004-12-25 13:20 044693 小王 ,1
8 2004-12-25 18:50 044693 小王 ,2

9 2004-12-24 08:10 044694 小明 ,1
10 2004-12-24 12:17 044694 小明 ,2
11 2004-12-24 13:09 044694 小明 ,1
12 2004-12-24 22:24 044694 小明 ,2
我希望用SQL語句選出之後變成

工號 , 姓名, 日期, 班次,時間 ,班次 ,時間 ,班次 ,時間 ,班次,時間
044693 , 小王 , 2004-12-24 , 1 , 08:10 ,2 ,12:17 ,1 , 13:09,2 , 22:24
044693 , 小王 , 2004-12-25 , 1 , 07:41 ,2 ,12:01 ,1 , 13:20,2 , 18:50
044694 , 小明 , 2004-12-24 , 1 , 08:10 ,2 ,12:17 ,1 , 13:09,2 , 22:24

當然我一天的班次和打卡時間是不一定的。可能一天打卡六次,也可能打四次,打三次,
但我總是要把一個人,一天的打卡數,和上班時間放在同一條記錄裡表示。
這個的語怎麼寫?
謝謝。
...全文
135 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
尚和空四 2005-01-08
  • 打赏
  • 举报
回复
create proc zcf_f1
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId
pbsql 2005-01-07
  • 打赏
  • 举报
回复
create table #t(id int,dt datetime,gh varchar(10),xm varchar(10),bc int)
insert into #t
select 1, '2004-12-24 08:10','044693', '小王' ,1 union all
select 2, '2004-12-24 12:17','044693', '小王' ,2 union all
select 3, '2004-12-24 13:09','044693', '小王' ,1 union all
select 4, '2004-12-24 22:24','044693', '小王' ,2 union all
select 5, '2004-12-25 07:41','044693', '小王' ,1 union all
select 6, '2004-12-25 12:01','044693', '小王' ,2 union all
select 7, '2004-12-25 13:20','044693', '小王' ,1 union all
select 8, '2004-12-25 18:50','044693', '小王' ,2 union all
select 9, '2004-12-24 08:10','044694', '小明' ,1 union all
select 10, '2004-12-24 12:17','044694', '小明' ,2 union all
select 11, '2004-12-24 13:09','044694', '小明' ,1 union all
select 12, '2004-12-24 22:24','044694', '小明' ,2
go

declare @sql varchar(8000),@n int
select gh,xm,rq=convert(varchar(10),dt,120),sj=convert(varchar(8),dt,108),
bc,sort=(select count(*) from #t where datediff(day,dt,a.dt)=0
and gh=a.gh and xm=a.xm and dt<=a.dt)
into #a
from #t a
select @n=max(sort) from #a
set @sql=''
while @n>0
select @sql=',(select sj from #a
where gh=a.gh and xm=a.xm and sort='+cast(@n as varchar)+' and rq=a.rq)
,(select bc from #a
where gh=a.gh and xm=a.xm and sort='+cast(@n as varchar)+' and rq=a.rq)
'+@sql,@n=@n-1
set @sql='select gh,xm,rq'+@sql+' from #a a group by gh,xm,rq'
exec(@sql)
drop table #t,#a
Softlee81307 2005-01-07
  • 打赏
  • 举报
回复
上面的這條語句可以達到你的要求了,
直接copy進去試就可以了

select * from redata 整個下面的語句是
一條完整的語句
---------------------
Create Table ReData(
Rno int,
Rdate Datetime,
jobno varchar(6),
JobName Varchar(20),
TuNo int
)
Insert into ReData Values(1,'2004/12/24 08:10','044693','小王' ,1)
Insert into ReData Values(2,'2004/12/24 12:17','044693','小王' ,2)
Insert into ReData Values(3,'2004/12/24 13:09','044693','小王' ,1)
Insert into ReData Values(4,'2004/12/24 22:24','044693','小王',2)
Insert into ReData Values(5,'2004/12/25 07:41','044693','小王' ,1)
Insert into ReData Values(6,'2004/12/25 12:01','044693','小王' ,2)
Insert into ReData Values(7,'2004/12/25 13:20','044693','小王' ,1)
Insert into ReData Values(8,'2004/12/25 18:50','044693','小王' ,2)
Insert into ReData Values(9,'2004/12/24 08:10','044694','小明' ,1)
Insert into ReData Values(10,'2004/12/24 12:17','044694','小明' ,2)
Insert into ReData Values(11,'2004/12/24 13:09','044694','小明' ,1)
Insert into ReData Values(12,'2004/12/24 22:24','044694','小明' ,2)

--select * from redata
----------------------下面是你要sql語句--------------------------
select jobno,jobname,fdate,no1,ntime1,no2,ntime2,no3,ntime3,no4,ntime4 from (
select jobno,jobname,fdate=convert(varchar(10),rdate,111),
no1=(select tuno from
(select top 1 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime1=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 1 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no2=(select tuno from
(select top 2 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime2=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 2 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no3=(select tuno from
(select top 3 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 2 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime3=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 3 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 2 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no4=(select tuno from
(select top 4 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 3 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime4=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 4 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 3 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
)
from redata c group by jobno,jobname,convert(varchar(10),rdate,111)

) d group by jobno,jobname,fdate,no1,ntime1,no2,ntime2,no3,ntime3,no4,ntime4

----------------------到此才算一條完全的---------------------
drop table ReData
Softlee81307 2005-01-07
  • 打赏
  • 举报
回复
Create Table ReData(
Rno int,
Rdate Datetime,
jobno varchar(6),
JobName Varchar(20),
TuNo int
)
Insert into ReData Values(1,'2004/12/24 08:10','044693','小王' ,1)
Insert into ReData Values(2,'2004/12/24 12:17','044693','小王' ,2)
Insert into ReData Values(3,'2004/12/24 13:09','044693','小王' ,1)
Insert into ReData Values(4,'2004/12/24 22:24','044693','小王',2)
Insert into ReData Values(5,'2004/12/25 07:41','044693','小王' ,1)
Insert into ReData Values(6,'2004/12/25 12:01','044693','小王' ,2)
Insert into ReData Values(7,'2004/12/25 13:20','044693','小王' ,1)
Insert into ReData Values(8,'2004/12/25 18:50','044693','小王' ,2)
Insert into ReData Values(9,'2004/12/24 08:10','044694','小明' ,1)
Insert into ReData Values(10,'2004/12/24 12:17','044694','小明' ,2)
Insert into ReData Values(11,'2004/12/24 13:09','044694','小明' ,1)
Insert into ReData Values(12,'2004/12/24 22:24','044694','小明' ,2)

select * from redata

select jobno,jobname,fdate,no1,ntime1,no2,ntime2,no3,ntime3,no4,ntime4 from (
select jobno,jobname,fdate=convert(varchar(10),rdate,111),
no1=(select tuno from
(select top 1 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime1=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 1 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no2=(select tuno from
(select top 2 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime2=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 2 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 1 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no3=(select tuno from
(select top 3 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 2 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime3=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 3 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 2 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
no4=(select tuno from
(select top 4 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 3 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
),
ntime4=(select cast(datepart(hh,rdate) as varchar(2))+':'+cast(datepart(mi,rdate) as varchar(2)) from
(select top 4 * from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno ) a
where a.rno not in(select top 3 rno from redata where convert(varchar(10),rdate,111)=convert(varchar(10),c.rdate,111) and jobno=c.jobno)
)
from redata c group by jobno,jobname,convert(varchar(10),rdate,111)

) d group by jobno,jobname,fdate,no1,ntime1,no2,ntime2,no3,ntime3,no4,ntime4


drop table ReData

把上面整個的copy到 sql中直接執行可看到你的效果
wanggangyu 2005-01-07
  • 打赏
  • 举报
回复
帮你求救!!!
梦回童年001 2005-01-07
  • 打赏
  • 举报
回复
How do?
luisic 2005-01-07
  • 打赏
  • 举报
回复
搜索行列转换。

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧