带有自连接的 sql 查询 [英] sql query with self join

问题描述

给定一个包含以下数据的表(TableA)；

Given a table (TableA) that contains the following data;

Id    Date    Status    RecordId
1    01/06/11    2      REC001
2    01/06/11    2      REC002
3    01/06/11    2      REC003
4    01/07/11    1      REC001

如何返回状态为 2 的所有记录，除了具有给定 RecordId 的记录，其中状态为 2 后跟在以后的日期为 1 的记录(并且没有其他状态为 2 的记录.

How can I return all records with a status of 2 except records with a given RecordId where a status of 2 is followed by a record of 1 at a later date (and there are no further records with a status of 2.

因此，例如，查询应返回 REC002 和 REC003，因为过去 REC001 的状态为 2，但后来被记录 ID 4 取代，状态为 1.如果在稍后的某个时间点为 REC001 添加了另一条状态为 2 的记录，那么它应该再次出现在结果集中(假设没有以后的状态为 1 的记录).

So for example, the query should return REC002 and REC003 as REC001 had a status of 2 in the past, but that was superseeded by record Id 4 with a status of 1 at a later date. If, at some later point in time, another record was added for REC001 with a status of 2, then this should again be present in the result set (assuming there are no later records with a status of 1).

我在这方面的微弱尝试是；

My feeble attempt at messing about with this is;

DECLARE @TableA TABLE
(
    Id INT,
    Dt DATETIME,
    Stat INT,
    RecId VARCHAR(6)
)

INSERT INTO @TableA 

SELECT   1,    DATEADD(day, -5, current_timestamp),  2,   'REC001'
UNION
SELECT   2,    DATEADD(day, -4, current_timestamp),  2,   'REC002'
UNION
SELECT   3,    DATEADD(day, -3, current_timestamp),  2,   'REC003'
UNION
SELECT   4,    DATEADD(day, -2, current_timestamp),  1,   'REC001'

   SELECT * 
     FROM @TableA t1
LEFT JOIN @TableA t2 ON t1.RecId = t2.RecId 
    WHERE t1.Stat = 2 
      AND (t1.Dt >= t2.Dt 
      AND t2.Stat != 1)

这有点工作，但返回 t1.Id = t2.Id 的值.我知道我可以通过我的 where 子句排除这个，但是如果我向表中添加更多记录，它会再次失败.例如;

This kinda works, but returns values where t1.Id = t2.Id. I know I can exclude this through my where clause, but if I add a bunch more records to the table it fails again. For example;

INSERT INTO @TableA 
SELECT   1,    DATEADD(day, -15, current_timestamp),  2,   'REC004'
UNION
SELECT   2,    DATEADD(day, -14, current_timestamp),  2,   'REC002'
UNION
SELECT   3,    DATEADD(day, -13, current_timestamp),  1,   'REC003'
UNION
SELECT   4,    DATEADD(day, -12, current_timestamp),  1,   'REC001'
UNION
SELECT   11,    DATEADD(day, -5, current_timestamp),  2,   'REC004'
UNION
SELECT   21,    DATEADD(day, -4, current_timestamp),  2,   'REC002'
UNION
SELECT   31,    DATEADD(day, -3, current_timestamp),  1,   'REC003'
UNION
SELECT   41,    DATEADD(day, -2, current_timestamp),  1,   'REC001'

任何想法都值得赞赏.

我尝试了给出的两个答案，虽然都没有给我我需要的东西，但它们确实为我指明了正确的方向.使用给出的答案，我想出了以下似乎可以满足我的要求；

I tried the two answers given, and while neither gave me exactly what I needed, they certainly pointed me in the right direction. Using the answers given, I came up with the following that seems to do what I require;

;WITH lastSuccess(recid, dt) AS (
    select recid, max(dt) from @tableA
    where stat = 1
    group by recid
),
lastFailure(recid, dt) AS (
    select recid, max(dt) from @tableA
    where stat = 2
    group by recid
)
select a.* from @tablea a
-- Limit results to those that include a failure
INNER JOIN lastFailure lf ON lf.recid = a.recid AND lf.dt = a.dt
-- If the recid also has a success, show this along with it's latest success date
LEFT JOIN lastSuccess ls ON ls.recid = lf.recid 
-- Limit records to where last failure is > last success or where there is no last success.
WHERE (lf.dt > ls.dt OR ls.dt IS NULL)

我在这里看到的唯一缺点是，如果有两个记录具有完全相同的时间戳，那么它会在结果集中出现两次.例如，如果 Id 21 被复制为 22，那么它会出现两次.这不是一个真正的问题，因为在现实中，时间戳将始终是唯一的.

The only drawback I can see here is if there are two records with exactly the same timestamp then it would appear in the result set twice. For example, if Id 21 was repicated as 22 then, it would appear twice. This isn't a real problem as in reality, the timestamp will always be unique.

推荐答案

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY RecId ORDER BY Dt DESC)
  FROM TableA
)
SELECT
  r1.Id,
  r1.Dt,
  r1.Stat,
  r1.RecId
FROM ranked r1
  INNER JOIN ranked r2 ON r1.RecId = r2.RecId AND r2.rn = 1
WHERE r1.Stat = 2

<小时>

UPDATE 问题更新后

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY RecId ORDER BY Dt DESC)
  FROM TableA
)
SELECT
  Id,
  Dt,
  Stat,
  RecId
FROM ranked
WHERE Stat = 2 AND rn = 1

这篇关于带有自连接的 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！