链接的 SQL Server 记录

debugcn 发表于 Dev

约瑟夫

我正在使用 SQL Server 2012 我有包含此信息的票证

Ticket No| Date | Closed
T001     | D1   | 
T002     | D2   |
T003     | D3   | Yes
T004     | D4   | Yes 
T005     | D5   |
T006     | D6   | Yes

我想按“案例”对它们进行分组，关闭后的第一张票是打开“案例”和以下所有直到关闭的票都是“案例”的一部分

Case No | Ticket No| Date | Closed
C001    | T001     | D1   | 
C001    | T002     | D2   |
C001    | T003     | D3   | Yes
C002    | T004     | D4   | Yes 
C003    | T005     | D5   |
C003    | T006     | D6   | Yes

我已经尝试了几个小时（使用滞后、排名等），但我找不到办法做到这一点。我找不到根据需要生成案例号的方法你有什么想法吗？非常感谢

这是查询：

SELECT
    CONCAT('C' , ID_TICKET) As ID_CASE_TEMP
    ,ID_TICKET
    ,DT_TICKET
    ,IIF(IsNull(LAG(IsNull(LB_CLOSING,'No')) OVER (ORDER BY DT_TICKET),'Yes') = 'Yes','Yes','No') As LB_OPENNING
    ,IsNull(LB_CLOSING,'No') As LB_CLOSING
    ,LAG(ID_TICKET) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET
    ,LAG(DT_TICKET) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET_DATE
    ,LAG(IsNull(LB_CLOSING,'No')) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET_CLOSING
    ,ROW_NUMBER() OVER (ORDER BY DT_TICKET) As CD_ROW_NUMBER
FROM 
    TICKETS

普拉迪普

这是使用LAG窗口函数的一种方法

SELECT 'C' + RIGHT(Concat('00', 1+Sum(CASE WHEN prev_val = 'Yes' THEN 1 ELSE 0 END)
                                        OVER (ORDER BY [date])), 3) AS [Case No], 
       [Ticket No],
       [Date],
       [Closed]
FROM   (SELECT *, Lag(closed) OVER( ORDER BY [date]) AS prev_val 
        FROM   table1) a