代码之家 › 专栏 › 技术社区 › Abdulquadir Shaikh

基于条件逻辑为每组获取一行的SQL查询

sql-server sql

Abdulquadir Shaikh · 技术社区 · 6 年前

我有一个表,其中一列中有重复的值。我正试图根据下面描述的一些逻辑消除具有重复条目的行。

RecoveryKey   DateTime      Duration CallDisposition NewTransaction Variable8   CallTypeID
7994113912466 12/4/18 16:26 19       52              Y              152643-5657     -1    
7994113912470 12/4/18 16:26 1168     29              Y              152643-5657   6390
7994113912751 12/4/18 16:51 2686     13              N              152643-5657   6390
7994113912756 12/4/18 13:51 56       2               Y              152643-5658   6390
7994113912756 12/4/18 13:52 125      13              Y              152643-5658   6390
7994113912756 12/4/18 13:55 125      13              N              152643-5658   6390

列 Variable8 前3条记录和后3条记录的值重复。

我想做的是,如果 CallDisposition NewTransaction 如果值为“N”,那么我必须用 CallDisposition公司 = 29.

CallDisposition公司 或 新交易

所以我的最终结果应该是这样的

RecoveryKey   DateTime      Duration CallDisposition NewTransaction Variable8   CallTypeID
7994113912470 12/4/18 16:26 1168     29              Y              152643-5657   6390
7994113912756 12/4/18 13:55 125      13              N              152643-5658   6390

2 回复 | 直到 6 年前

Hogan 6 年前

可以使用窗口函数。假设“29”总是倒数第二条记录,您可以:

select t.*
from (select t.*,
             row_number() over (partition by variable8 order by datetime desc) as seqnum,
             lag(CallDisposition) over (partition by variable8 order by datetime asc) as prev_CallDisposition
      from t
     ) t
where ( prev_CallDisposition = 29 and newTransaction = 'N' and seqnum = 2)  or
      ( prev_CallDisposition <> 29 or prev_CallDisposition is null or newTransaction <> 'N' and seqnum = 1 );

假设29调用可以在任何地方使用,那么使用窗口函数只会稍微复杂一些:

select t.*
from (select t.*,
             sum(case when CallDisposition = 29 and next_newTransaction = 'N' then 1 else 0 end) over (partition by variable8) as num_matching_29
      from (select t.*,
                   row_number() over (partition by variable8 order by datetime desc) as seqnum,
                   lead(newTransaction) over (partition by variable8 order by datetime asc) as next_newTransaction
            from t
           ) t
     ) t
where (CallDisposition = 29 and next_newTransaction = 'N') or
      (num_matching_29 = 0 and seqnum = 1);

Ross Bush 6 年前

我冒昧地添加了一个序列,因为我假设有一些顺序。这将在数据中的任意位置获取29/N组合。

DECLARE @T TABLE (datetime INT , CallDiusposition INT, NewTransaction NVARCHAR(10), Variable8 NVARCHAR(50))
INSERT @T VALUES(1,52,'Y',5657),(2,29,'Y',5657),(3,13,'N',5657),(4,2,'Y',5658),(5,13,'Y',5658),(6,13,'N',5658)

;WITH A AS
(
    SELECT *,
        RN = CASE WHEN LEAD(NewTransaction) OVER (PARTITION BY Variable8 ORDER BY datetime) ='N' AND CallDiusposition=29  THEN -1 ELSE ROW_NUMBER() OVER (PARTITION BY Variable8 ORDER BY datetime) END
    FROM @T
),
B AS
(
    SELECT Match=ROW_NUMBER() OVER (PARTITION BY Variable8 ORDER BY RN ASC),* FROM A 
)
SELECT * FROM B WHERE Match=1

无CTE

SELECT * FROM
(
    SELECT Match=ROW_NUMBER() OVER (PARTITION BY Variable8 ORDER BY RN ASC),* FROM
    (
        SELECT *,
            RN = CASE WHEN LEAD(NewTransaction) OVER (PARTITION BY Variable8 ORDER BY datetime) ='N' AND CallDiusposition=29  THEN -1 ELSE ROW_NUMBER() OVER (PARTITION BY Variable8 ORDER BY datetime) END
        FROM @T
    )AS A
)AS B
WHERE Match=1