选择服务ID出现多次的最高Id

Question

我在 Web 窗体中继承了一个代码库，并且在使用 SQL 查询时遇到了问题。

SELECT foretag.namn, foretag.epost, foretag.forlangEj, service_fakturering.* 
FROM foretag 
INNER JOIN service ON foretag.id = service.foretagsid 
INNER JOIN service_fakturering ON service.id = service_fakturering.service_id  
    WHERE service_fakturering.giltighets_datum <= DATEADD(D, 30, GETDATE())
    ORDER BY bestallnings_datum DESC, id DESC

在表中service_fakturering有多个行具有相同的service_id

我需要选择最后一个，最大id，用于INNER JOIN service_fakturering ON service.id = service_fakturering.service_id

Answer 1

使用 ROW_NUMBER() 函数以及公用表表达式，可以像这样完成：

WITH cte_service_fakturering AS 
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY service_id ORDER BY id DESC) RN
   FROM service_fakturering 
)
SELECT foretag.namn, foretag.epost, foretag.forlangEj, cte.* 
FROM foretag 
INNER JOIN service ON foretag.id = service.foretagsid 
INNER JOIN cte_service_fakturering cte ON service.id = cte.service_id  AND cte.RN = 1
    WHERE service_fakturering.giltighets_datum <= DATEADD(D, 30, GETDATE())
    ORDER BY bestallnings_datum DESC, id DESC

Answer 2

Sub 选择将对您的service_fakturering行进行分组并为您获取 maxId。然后，这将用于联接回查询并仅筛选您感兴趣的行。

SELECT foretag.namn, foretag.epost, foretag.forlangEj, service_fakturering.* 
FROM foretag 
INNER JOIN service ON foretag.id = service.foretagsid 
INNER JOIN service_fakturering ON service.id = service_fakturering.service_id
INNER JOIN (Select service_fakturering.service_id, Max(service_fakturering.id) as Id
            FROM service_fakturering 
            GROUP BY service_fakturering.service_id) x
          ON x.service_id = service_fakturering.service_id  
          AND x.Id = service_fakturering.Id
WHERE service_fakturering.giltighets_datum <= DATEADD(D, 30, GETDATE())
ORDER BY bestallnings_datum DESC, id DESC

Answer 3

试试这个...您可以使用ROW_NUMBER来表示集合的顺序

WITH CTE
AS (
    SELECT *
        , ROW_NUMBER() OVER (
            PARTITION BY service_id ORDER BY ID DESC
            ) ROWNUM
    FROM service_fakturering
    )
SELECT foretag.namn
    , foretag.epost
    , foretag.forlangEj
    , service_fakturering.*
FROM foretag
INNER JOIN service
    ON foretag.id = service.foretagsid
INNER JOIN service_fakturering
    ON service.id = service_fakturering.service_id
        AND ROW_NUM = 1
WHERE service_fakturering.giltighets_datum <= DATEADD(D, 30, GETDATE())
ORDER BY bestallnings_datum DESC
    , id DESC