时间戳

首页 > 解决方案 > 每次成员再次出现时标记为已流通

sql-server - 每次成员再次出现时标记为已流通

问题描述

我想标记流通量，即每次成员再次出现时。这个列表显示了我想要的结果

Sequence ID Aktivity ID From, Agent ID  To, Agent ID    Movement    Circulated
1           001A9552444 YNGBAN          TOMYAN          1           0
2           001A9552461 TOMYAN          THOKIN          1           0
3           001A9552466 THOKIN          MATLOV          1           0
4           001A9552486 MATLOV          THOKIN          1           1
5           001A9552499 THOKIN          YNGBAN          1           1
6           001A9554213 YNGBAN          IKJER           1           0

创建基表的代码

DROP TABLE IF EXISTS [dbo].[Basetable]
CREATE TABLE [dbo].[Basetable] (
[Incident ID] INT,
[Activity ID] VARCHAR(50),
[Agent ID] VARCHAR(50),
)
INSERT INTO [dbo].[Basetable]
VALUES
(1072326, '001A9552444', 'YNGBAN'),
(1072326, '001A9552461', 'TOMYAN'),
(1072326, '001A9552463', 'THOKIN'),
(1072326, '001A9552464', 'THOKIN'),
(1072326, '001A9552465', 'THOKIN'),
(1072326, '001A9552466', 'THOKIN'),
(1072326, '001A9552468', 'MATLOV'),
(1072326, '001A9552484', 'MATLOV'),
(1072326, '001A9552485', 'MATLOV'),
(1072326, '001A9552486', 'MATLOV'),
(1072326, '001A9552499', 'THOKIN'),
(1072326, '001A9554212', 'YNGBAN'),
(1072326, '001A9554213', 'YNGBAN'),
(1072326, '001A9568252', 'IKJER')

以下是到目前为止的代码。我不想做 Group By 和 Count 并且尝试了 Left Join 和 Outer Apply 但还没有找到一个好的解决方案。

WITH X AS (
SELECT
   ROW_NUMBER() OVER (ORDER BY [Activity ID]) AS [Sequence ID]
   ,[Incident ID]
   ,[Activity ID]
   ,[From, Agent ID]
   ,[To, Agent ID] 
   ,1 AS [Movement]
FROM (
   SELECT
         [Incident ID]
         ,[Activity ID]
         ,[Agent ID] AS [From, Agent ID] 
         ,LEAD([Agent ID],1,0) OVER (PARTITION BY [Incident ID] ORDER BY [Activity ID]) AS [To, Agent ID] 
   FROM [dbo].[Basetable]
   ) AS X
WHERE
   [To, Agent ID] != '0' AND [From, Agent ID] != [To, Agent ID]
)
SELECT
   X1.[Sequence ID]
   ,X1.[Incident ID]
   ,X1.[Activity ID]
   ,X1.[From, Agent ID]
   ,X1.[To, Agent ID] 
   ,X1.[Movement]
FROM X AS X1

编辑：添加了用于创建基表的代码并清理了主要代码。

标签： sql-servertsql

解决方案

-- Your existing query
;WITH X AS (
SELECT
   ROW_NUMBER() OVER (ORDER BY [Activity ID]) AS [Sequence ID]
   ,[Incident ID]
   ,[Activity ID]
   ,[From, Agent ID]
   ,[To, Agent ID] 
   ,1 AS [Movement]
FROM (
   SELECT
         [Incident ID]
         ,[Activity ID]
         ,[Agent ID] AS [From, Agent ID] 
         ,LEAD([Agent ID],1,0) OVER (PARTITION BY [Incident ID] ORDER BY [Activity ID]) AS [To, Agent ID] 
   FROM [dbo].[Basetable]
   ) AS X
WHERE
   [To, Agent ID] != '0' AND [From, Agent ID] != [To, Agent ID]
),
Y AS
(
SELECT
   X1.[Sequence ID]
   ,X1.[Incident ID]
   ,X1.[Activity ID]
   ,X1.[From, Agent ID]
   ,X1.[To, Agent ID] 
   ,X1.[Movement]
FROM X AS X1
)
-- Added this part
SELECT  Y.*, CASE WHEN Z.CNT > 0 THEN 1 ELSE 0 END AS Circulated
FROM    Y
    CROSS APPLY
    (
        -- Count the occurrence of the To Agent ID appear before as `From`
        SELECT  CNT = COUNT(*)
        FROM    X
        WHERE   X.[Sequence ID]     < Y.[Sequence ID]
        AND     X.[From, Agent ID]  = Y.[To, Agent ID]
    ) Z

推荐阅读