时间戳

首页 > 解决方案 > 从查询中获取唯一数据

sql - 从查询中获取唯一数据

问题描述

我从这个查询中得到了一些重复的行，但能够理解如何获取唯一序列号（序列号）的数据。

我需要获得SerialNumber最新的独特someDate

SET NOCOUNT ON;
DECLARE @myDate datetime ;
SELECT @myDate = someDate FROM X ORDER BY someDate DESC;
SELECT
    d.someID,
    sd.Id,
    d.SerialNr as SerialNumber,
    convert(varchar(25), a.someDate, 121) as someDate
FROM A d WITH (NOLOCK)
    INNER JOIN B sd ON d.myName = sd.myName
    INNER JOIN C a ON sd.AuditID = a.AuditID
WHERE a.someDate > @myDate 
GROUP BY d.someID, sd.Id, d.SerialNr, a.someDate

在此查询上方运行时，我得到了此结果，但我需要数据唯一SerialNUmber

----------------------------------------------------------------
someID  | Id         | SerialNumber    | someDate
----------------------------------------------------------------
1086273 | 1811804430 | CNRXY35780      | 2020-01-30 22:11:07.607
1086273 | 1812119814 | CNRXY35780      | 2020-01-30 22:11:07.607
1086274 | 1811804766 | VNB3R56530      | 2020-01-30 22:11:07.607
1086276 | 1811804641 | U64968D8N889217 | 2020-01-30 22:11:07.607
1086276 | 1812119826 | U64968D8N889217 | 2020-01-30 22:11:07.607

我试过这个但出错了

SET NOCOUNT ON;
;with ABC
(
    DECLARE @myDate datetime ;
    select @myDate = someDate from X order by someDate desc;
    SELECT top 1000 
        d.someID,
        sd.Id,
        d.SerialNr as SerialNumber,
        convert(varchar(25), a.someDate, 121) as someDate,
        ROW_NUMBER()OVER(PARTITION BY d.DeviceId,d.SerialNr ORDER BY a.someDate desc) RID
    FROM A d
         INNER JOIN B sd ON d.someID = sd.someID
         INNER JOIN C a ON sd.AuditID = a.AuditID
    WHERE a.someDate > @myDate AND RID = 1
)
    select * from ABC
    where RID = 1
    GROUP BY d.DeviceId,sd.Id,d.SerialNr,a.someDate

这是错误：

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.

我也试过这个

SET NOCOUNT ON;
DECLARE @AuditDate datetime;
select @AuditDate = AuditDate from atr_usageSync order by AuditDate desc;
;with ABC AS
(
    SELECT top 1000 
        d.DeviceId,
        sd.Id,
        d.SerialNr as SerialNumber,
        convert(varchar(25), a.AuditDate, 121) as AuditDate,
        ROW_NUMBER() OVER (PARTITION BY d.DeviceId,d.SerialNr ORDER BY a.AuditDate desc) RID
    FROM Device d
         INNER JOIN SuppliesData sd ON d.DeviceId = sd.DeviceId
         INNER JOIN Audit a ON sd.AuditID = a.AuditID
    WHERE a.AuditDate > @AuditDate
)
    select * from ABC
    where RID = 1
    GROUP BY DeviceId,Id,SerialNumber,AuditDate

这次我得到了这个错误

Msg 8120, Level 16, State 1, Line 4
Column 'ABC.RID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

标签： sqlsql-server

解决方案

  1. 在 CTE 之外声明您的变量。

  2. select @myDate = someDate from X order by someDate desc; -- Should be outside cte , below declare -- @myDate will store only one result here.

  3. AS 不见了。

要么尝试：

SET NOCOUNT ON;
DECLARE @myDate datetime ;
select @myDate = someDate from X order by someDate desc;
;with ABC AS
(


    SELECT top 1000 
        d.someID,
        sd.Id,
        d.SerialNr as SerialNumber,
        convert(varchar(25), a.someDate, 121) as someDate,
        ROW_NUMBER() OVER (PARTITION BY d.DeviceId,d.SerialNr ORDER BY a.someDate desc) RID
    FROM A d
         INNER JOIN B sd ON d.someID = sd.someID
         INNER JOIN C a ON sd.AuditID = a.AuditID
    WHERE a.someDate > @myDate AND RID = 1
)
    select * from ABC
    where RID = 1
    GROUP BY  d.someID,sd.Id,d.SerialNumber,a.someDate

或者

  SET NOCOUNT ON;
    DECLARE @myDate datetime ;
    ;with ABC AS
    (


        SELECT top 1000 
            d.someID,
            sd.Id,
            d.SerialNr as SerialNumber,
            convert(varchar(25), a.someDate, 121) as someDate,
            ROW_NUMBER() OVER (PARTITION BY d.DeviceId,d.SerialNr ORDER BY a.someDate desc) RID
        FROM A d
             INNER JOIN B sd ON d.someID = sd.someID
             INNER JOIN C a ON sd.AuditID = a.AuditID
        WHERE a.someDate > @myDate AND RID = 1
    )
        select * from ABC
        where RID = 1
        GROUP BY  d.someID,sd.Id,d.SerialNumber,a.someDate

并将一些日期传递给@myDate。

推荐阅读