T-Sql
如何將游標轉換為 CTE？T-SQL
為了澄清，我添加了更多資訊並刪除了“簡化”程式碼。
我有帶有日期時間打卡資訊的表格。
CREATE TABLE [dbo].[AT_Punches]( [PunchID] [int] IDENTITY(1,1) NOT NULL, [Facility] [char](3) NULL, [person_code] [varchar](12) NULL, [TimeStamp] [datetime] NULL, [AltJob] [varchar](12) NULL, [INOUT] [char](3) NULL, [edit_user] [varchar](20) NULL, [edit_datetime] [datetime] NULL, [create_datetime] [datetime] NULL, CONSTRAINT [PK__AT_Punch] PRIMARY KEY CLUSTERED ( [PunchID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] GO -- TEST DATA INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('ABC','JDOE','2020-07-06 15:03:48.960','RN','In','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('ABC','JDOE','2020-07-07 03:43:38.000','RN','Out','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('XYZ','KLO','2020-07-07 06:18:08.000','RN','In','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('XYZ','JSMITH','2020-07-07 15:01:40.000','RN','In','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('XYZ','JSMITH','2020-07-08 03:43:38.000','RN','Out','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('XYZ','ABARR','2020-07-08 04:12:33.000','RN','In','tester',GETDATE(),GETDATE()) INSERT INTO [dbo].[AT_Punches] ([Facility],[person_code],[TimeStamp],[AltJob],[INOUT],[edit_user],[edit_datetime],[create_datetime]) VALUES ('XYZ','ABARR','2020-07-08 07:19:22.000','RN','Out','tester',GETDATE(),GETDATE())我需要將這些記錄變成一份報告，將拳頭配對，同時記下“失去的”拳頭。它看起來像這樣。
Facility Person Vendor Job In Out Hours ABC Jane Doe (JDOE) 12345 RN 7/6/2020 3:00 PM 7/7/2020 3:45 AM 12.75 XYZ Kim Lo (KLO) 34567 RN 7/7/2020 6:15 AM *** MISSING *** 0.00 XYZ Jon Smith (JSMITH) 34567 RN 7/7/2020 3:00 PM 7/8/2020 3:45 AM 12.75 XYZ Angela Barr (ABARR) 34567 RN 7/8/2020 4:15 AM 7/8/2020 7:15 AM 3.00我目前正在使用 sp 中的游標來解決這個問題，但執行時間太長。處理 700 條記錄大約需要 45 秒。我覺得完成得更快。這是那個sp。
CREATE PROCEDURE [dbo].[sp_Missing_Punch_Formatter] @StartDate date, @EndDate date, @faccode varchar(4), @userID varchar(20) WITH EXEC AS CALLER AS DECLARE @boolIN BIT DECLARE @PunchesCursor AS CURSOR DECLARE @strLastStamp DATETIME DECLARE @strLastOut VARCHAR (25) DECLARE @strLastPerson VARCHAR (12) DECLARE @strLastRecNo INT DECLARE @counter INT DECLARE @facility CHAR (3) DECLARE @vendorid VARCHAR (8) DECLARE @person_code VARCHAR (12) DECLARE @altjob VARCHAR (12) DECLARE @Inout CHAR (3) DECLARE @timestamp DATETIME DECLARE @InPunch VARCHAR (25) DECLARE @OutPunch VARCHAR (25) DECLARE @is_round char(1) BEGIN /********************* delete temp work table data *********************/ DELETE FROM atrax.dbo.at_punchwork WHERE userid = @userid AND faccode = @faccode; /********************* reformat and calc punches *********************/ SET @PunchesCursor = CURSOR FOR SELECT facility, vendorid, person_code, altjob, Inout, timestamp, InPunch, OutPunch, is_round FROM (SELECT p.facility, a.vendorid, p.person_code, p.altjob, p.timestamp, p.INOUT, isnull (format (p.timestamp, 'g'), '****') AS InPunch, '****' AS OutPunch, 'Y' as is_round FROM atrax.dbo.AT_Punches p JOIN atrax.dbo.AT_person e ON p.Facility = e.Facility AND p.person_code = e.Person_code JOIN atrax.dbo.AT_agency a ON e.VendorID = a.VendorID WHERE p.INOUT = 'In' UNION ALL SELECT p.facility, a.vendorid, p.person_code, p.altjob, p.timestamp, p.INOUT, '****' AS InPunch, isnull (format (timestamp, 'g'), '****') AS OutPunch, 'Y' as is_round FROM atrax.dbo.AT_Punches p JOIN atrax.dbo.AT_person e ON p.Facility = e.Facility AND p.person_code = e.Person_code JOIN atrax.dbo.AT_agency a ON e.VendorID = a.VendorID WHERE p.INOUT = 'Out') AS t WHERE timestamp >= @startdate AND timestamp <= dateadd(DD,1,@enddate) AND facility = @faccode ORDER BY facility, VendorID, person_code, timestamp; SET @strLastPerson = ''; SET @strLastStamp = ''; SET @strLastOut = ''; SET @boolIN = 0; SET @strLastRecNo = 0; SET @counter = 0; -- Open PUNCHES for Processing OPEN @PunchesCursor FETCH NEXT FROM @PunchesCursor INTO @facility, @vendorid, @person_code, @altjob, @Inout, @timestamp, @InPunch, @OutPunch, @is_round WHILE @@FETCH_STATUS = 0 BEGIN SET @counter += 1; -- Record # = counter -- start new record for new person if not first record in IF @person_code <> @strLastPerson AND @counter <> 1 BEGIN -- if record already pending finish last record IF @boolIN = 1 BEGIN -- if record has been started then finish with it, save out data from in punch (missing) BEGIN TRANSACTION; UPDATE atrax.dbo.at_punchwork SET timestampout = @strLastStamp, OutPunch = @strLastOut WHERE recno = @strLastRecNo AND userid = @userid AND faccode = @faccode; COMMIT; -- Reset Vars SET @boolIN = 0 --False SET @strLastStamp = '' SET @strLastOut = '' END END -- end new person IF @Inout = 'In' BEGIN -- Process the in record IF @boolIN = 1 BEGIN -- if record has been started then finish with it, save out data from in punch (missing) BEGIN TRANSACTION; UPDATE atrax.dbo.at_punchwork SET timestampout = @strLastStamp, OutPunch = @strLastOut WHERE recno = @strLastRecNo AND userid = @userid AND faccode = @faccode; COMMIT; SET @boolIN = 0 --False SET @strLastStamp = '' SET @strLastOut = '' END -- boolin -- Start a new IN for current record SET @boolIN = 1 --true INSERT INTO atrax.dbo.at_punchwork (recno, faccode, vendorid, person_code, WorkedJob, timestamp, Inout, InPunch, userid, is_round) VALUES (@Counter, @faccode, @vendorid, @person_code, @Altjob, @timestamp, @Inout, @inpunch, @userid, @is_round); -- save out data from this record SET @strLastStamp = @timestamp SET @strLastOut = @outpunch END ELSE -- INout = Out BEGIN -- process out record IF @boolIN = 1 BEGIN -- Record all ready started -- Add Out part from curr record UPDATE atrax.dbo.at_punchwork SET timestampout = @timestamp, OutPunch = @outpunch WHERE recno = @strLastRecNo AND userid = @userid AND faccode = @faccode; SET @strLastStamp = '' SET @strLastOut = '' SET @boolIN = 0 END ELSE BEGIN -- add whole out Record (no in found). INSERT INTO atrax.dbo.at_punchwork (recno, faccode, vendorid, person_code, WorkedJob, timestamp, Inout, InPunch, timestampout, OutPunch, userid, is_round) VALUES (@counter, @faccode, @vendorid, @person_code, @Altjob, @timestamp, @inout, @inpunch, @timestamp, @outpunch, @userid, @is_round); SET @strLastStamp = '' SET @strLastOut = '' SET @boolIN = 0 END -- bool IN END -- INOUT SET @strLastRecNo = @counter; SET @strLastPerson = @person_code; FETCH NEXT FROM @PunchesCursor INTO @facility, @vendorid, @person_code, @altjob, @Inout, @timestamp, @InPunch, @OutPunch, @is_round END --end loop END GO有沒有更快的方法來做這種事情，也許沒有游標？
修改後的答案，給出了問題的澄清
問題基本上是，對於 person_code 和設施的給定組合，當您對記錄進行排序時
$$ timestamp $$順序，您要確保顯示 inout 值為 ‘in’ 的每一行後跟 ‘out’，反之亦然 - 並且最後一行顯示 ‘out’。 這是使用的解決方案
lead- 這是一個 T-SQL 函式，用於從比目前行更靠後的行中讀取數據值。我們基本上用它來檢查：下一行是同一個人-設施組合嗎？如果是這樣，那麼下一行的 INOUT 值是否與該行不同？如果是這樣，那就太好了。如果不是，那就有問題了。但是，如果下一行是針對某些不同的人員-設施組合，那麼在目前行上，INOUT 值是否“out”？如果沒有，那麼他們還沒有打卡。select *, case when lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) = Facility and lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) = person_code and lead(INOUT,1,'') over (order by person_code, Facility, [TimeStamp]) = INOUT then 'boohoo - next row is the same' when lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) = Facility and lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) = person_code and lead(INOUT,1,'') over (order by person_code, Facility, [TimeStamp]) <> INOUT then 'hooray - next row is different' when ( lead(Facility,1,'') over (order by person_code, Facility, [TimeStamp]) <> Facility or lead(person_code,1,'') over (order by person_code, Facility, [TimeStamp]) <> person_code ) and INOUT = 'In' then 'boohoo - Not clocked out yet...' else 'hooray - last row for this person-location - and they clocked out :)' end from at_punches order by person_code, Facility, [TimeStamp];初步答案
（這個答案是當我認為他們只需要顯示每個人的下班次數不匹配的任何行時。我會在此處留下答案，儘管這很簡單）。
試試下面的。
select * from tblPunches where (select count(1) from tblPunches p2 where p2.userID = tblPunches.userID and p2.punchType = 'IN') <> (select count(1) from tblPunches p3 where p3.userID = tblPunches.userID and p2.punchType = 'OUT')在我看來 - 如果給定使用者的“IN”和“OUT”拳數不相等，那就有問題了，不是嗎？
以上不包含確保“OUT”打孔與特定“IN”打孔相關的邏輯 - 例如，在同一天，在 24 小時內或其他任何時間 - 但您的問題似乎也沒有暗示該要求。
PS。在我寫這個答案時，您發布了一條澄清評論，您希望根據時間戳遍歷行。如果該使用者在 IN 和 OUT 出拳之間有任何不匹配，則上述內容將返回給定使用者的所有行。
如果您只想返回IN 連續出現兩次或 OUT 連續出現兩次（按時間戳排序時）的行，請參閱我最近對類似問題的回答。我真的在五分鐘前發布了這個，這種方法只會為你拉出有問題的單行。