在SQL Server中合并同表不同行实现签到签出数据关联

Looks like you need to pair check-in (IsCheckIn = 1) and check-out (IsCheckIn = 0) records for the same user/account, merging each pair into a single row. Here's a SQL Server solution that matches your desired output:

Step-by-Step Explanation & Code #

First, we'll use common table expressions (CTEs) to separate and number the check-in and check-out records, then join them by their row number within each user/account group:

WITH CheckInCTE AS (
    -- Isolate check-in records and assign row numbers (latest first)
    SELECT 
        Id,
        UserId,
        IsCheckIn,
        DateTime AS InDateTime,
        Image AS InImage,
        AccountId,
        ROW_NUMBER() OVER (PARTITION BY UserId, AccountId ORDER BY Id DESC) AS PairId
    FROM YourTableName
    WHERE IsCheckIn = 1
),
CheckOutCTE AS (
    -- Isolate check-out records and assign matching row numbers
    SELECT 
        IsCheckIn AS IsCheckOut,
        DateTime AS OutDateTime,
        Image AS OutImage,
        UserId,
        AccountId,
        ROW_NUMBER() OVER (PARTITION BY UserId, AccountId ORDER BY Id DESC) AS PairId
    FROM YourTableName
    WHERE IsCheckIn = 0
)
-- Join the paired records
SELECT 
    ci.Id,
    ci.UserId,
    ci.IsCheckIn,
    ci.InDateTime,
    ci.InImage,
    ci.AccountId,
    co.IsCheckOut,
    co.OutDateTime,
    co.OutImage
FROM CheckInCTE ci
LEFT JOIN CheckOutCTE co 
    ON ci.UserId = co.UserId 
    AND ci.AccountId = co.AccountId 
    AND ci.PairId = co.PairId
ORDER BY ci.Id DESC;

How This Works #

• CheckInCTE: Filters all check-in entries, then uses ROW_NUMBER() to assign a unique PairId to each check-in for the same user/account, ordered by descending Id (so the newest check-in gets PairId = 1).
• CheckOutCTE: Does the same for check-out entries, assigning matching PairIds based on descending Id.
• Join: Links check-in and check-out records that share the same UserId, AccountId, and PairId—this pairs the newest check-in with the newest check-out, the second newest check-in with the second newest check-out, etc.
• Sort: Orders the final result by check-in Id descending to match your sample output.

Adjustments #

• Replace YourTableName with your actual table name.
• If you need to pair records by date instead of Id, change the ORDER BY Id DESC clause in both CTEs to ORDER BY DateTime DESC (or ascending, depending on your business logic for which check-out belongs to which check-in).

内容的提问来源于stack exchange，提问作者Nandan Kyada