sql-包括具有最新所有权的组

hmmo2u0o  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(271)

我的数据集包括对拥有的设备的测试,但是可以购买和转售设备,我们只需要与最近的所有权相关联的测试。
数据集如下所示:

TestID               ProductID       RegistrationID     TestDate
00021357916020      P3K25EL141297        89D617         2019-07-22
00021357915928      P3K25EL141297        89D617         2019-07-15
00021353422334      P3K25EL141297        5PKY90         2019-05-31

我需要细化到只包含注册89d617的测试,因为这些是最新所有权的测试。我们没有提供任何所有权变更的参考信息,注册ID也没有按有意义的顺序增加,所以我还没有找到一种将这些信息分组的方法。
为了澄清-该表需要包括注册ID为89d617的两个条目,因为这两个条目都是最近注册的测试。

jljoyd4f

jljoyd4f1#

FIRST_VALUE() 窗口功能:

SELECT t.TestID, t.ProductID, t.RegistrationID, t.TestDate
FROM (
  SELECT *, FIRST_VALUE(RegistrationID) OVER (PARTITION BY ProductID ORDER BY TestDate DESC) reg
  FROM tablename  
) t
WHERE t.RegistrationID = t.reg

请看演示。
结果:

> TestID      | ProductID     | RegistrationID | TestDate               
> :---------- | :------------ | :------------- | :---------
> 21357916020 | P3K25EL141297 | 89D617         | 2019-07-22
> 21357915928 | P3K25EL141297 | 89D617         | 2019-07-15
xam8gpfp

xam8gpfp2#

DECLARE @t TABLE(TestID NVARCHAR(50), 
                 ProductID NVARCHAR(50), 
                 RegistrationID NVARCHAR(50), 
                 TestDate DATETIME);

INSERT INTO @t
VALUES
    ('00021357916020','P3K25EL141297','89D617','2019-07-22'),
    ('00021357915928','P3K25EL141297','89D617','2019-07-15'),
    ('00021353422334','P3K25EL141297','5PKY90','2019-05-31');

-- Or like this...

SELECT
    t1.*
FROM
    @t t1
CROSS APPLY
    (SELECT TOP 1 RegistrationID 
     FROM @t 
     WHERE ProductID = t1.ProductID 
     ORDER BY TestDate DESC) t2
WHERE
    t2.RegistrationID = t1.RegistrationID;
wj8zmpe1

wj8zmpe13#

你好像想要那些 RegistrationID 有最新的 TestDateProductID . 如果是这样,一个简单的选择是使用相关子查询进行过滤;

select t.*
from mytable t
where t.RegistrationID = (
    select top (1) RegistrationID  
    from mytable t1 
    where t1.ProductID = t.ProductID
    order by TestDate desc
)

此查询将利用上的索引 (RegistrationID, testDate) .

相关问题