我需要幫助才能在同一張桌子上加入 colum，我有一張這樣的桌子：
mat id name date time
4 138644 YACINE 20160201 130938 entry
4 138645 YACINE 20160201 204033 leave
4 149540 YACINE 20160223 80614
4 149541 YACINE 20160223 160224
31 131905 BENHAOUA 20160118 80459 entry
31 131906 BENHAOUA 20160118 154738 leave
31 485939 BENHAOUA 20160406 54113
31 486091 BENHAOUA 20160406 132152
如果你能看到每個名字（墊子）我有兩行相同的日期，第一個日期代表進入時間和第二個離開時間，我希望只有一個相同的名字和相同的日期與進入和離開時間一樣這
mat name date entry leave
4 YACINE 20160201 130938 204033
4 YACINE 20160223 80614 160224
31 BENHAOUA 20160118 80459 154738
31 BENHAOUA 20160406 54113 132152
我嘗試在同一個表中使用內部連接，但結果是錯誤的，我試試這個：
select a.id, a.matricule, a.nom,a.edate, a.etime as entree, b.etime as sortie
from shift1 a
inner join shift2016 b on a.matricule=b.matricule and a.EDATE=b.EDATE and a.etime<>b.etime
order by matricule
uj5u.com熱心網友回復：
請試試這個：-
SELECT MAT
,NAME
,DATE
,MIN(TIME) AS entry
,MAX(TIME) AS leave
FROM Persons
GROUP BY mat
,NAME
,DATE
uj5u.com熱心網友回復：
下面的示例應該產生您需要的輸出。這是使用樞軸運算子實作的。
declare @tbl table(mat int,id int,name varchar(20)
,date int,time int)
insert into @tbl
values(4,138644,'YACINE',20160201,130938)
,(4,138645,'YACINE',20160201,204033)
,(4,149540,'YACINE',20160223,80614)
,(4,149541,'YACINE',20160223,160224)
,(31,131905,'BENHAOUA',20160118,80459)
,(31,131906,'BENHAOUA',20160118,154738)
,(31,485939,'BENHAOUA',20160406,54113)
,(31,486091,'BENHAOUA',20160406,132152)
select *, case when ROW_NUMBER()
over(partition by mat,date order by id) % 2 <> 0 then 'Entry' else 'Leave'
end as [status]
into #temp
from
@tbl
select mat,name,date,Entry,Leave
from
(select mat,name,date,time,status
from
#temp)t
pivot
(max(time) for status in([Entry],[Leave]))p
drop table #temp
uj5u.com熱心網友回復：
根據我的評論，您的查詢將是：
SELECT mat
,name
,DATE
,MIN(TIME) AS entry
,MAX(TIME) AS leave
FROM shift1
GROUP BY mat
,name
,DATE
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451034.html