时间戳

首页 > 解决方案 > 仅显示唯一值

sql - 仅显示唯一值

问题描述

我正在使用 SQL Server 2019 并寻找一种方法来根据其最新的 FW_Version 仅显示每个 AOC 的最新值。这是我到目前为止的查询，但这显示了一切：

SELECT DISTINCT
    dbo.Model.ModelName AS AOC,
    dbo.Chipset.Chipset,
    dbo.FormFactor.FormFactor AS Form_Factor,
    dbo.ProductRelease.ECO AS Release,
    dbo.ProductRelease.Date AS Release_Date,
    dbo.Intel.FWVersion AS FW_Version,
    dbo.Intel.ETRACKID
FROM
    dbo.Intel
        INNER JOIN dbo.ProductRelease ON dbo.Intel.ProductReleaseID = dbo.ProductRelease.ID
        INNER JOIN dbo.Model ON dbo.ProductRelease.ModelID = dbo.Model.ID
        INNER JOIN dbo.FormFactor ON dbo.Model.FormFactorID = dbo.FormFactor.ID
        INNER JOIN dbo.Chipset ON dbo.Intel.ControllerID = dbo.Chipset.ControllerID AND dbo.Intel.ChipsetID = dbo.Chipset.ID
ORDER BY 
    dbo.Model.ModelName, 
    dbo.Intel.FWVersion DESC, 
    dbo.ProductRelease.Date DESC

我只想显示标有黄色的项目...我怎样才能做到这一点？ 在此处输入图像描述

这是我的表列表，这些表是根据其相应的 ID 加入的

表 1：型号

ID (Primary Key)
ModelName nvarchar(50)
FormFactorID int

表 2：外形尺寸

ID (Primary Key)
FormFactor nvarchar(15)

表 3：产品发布

ID (Primary Key)
ModelID int
Date date
ECO nvarchar(10)

表 4：英特尔

ID (Primary Key)
ProductReleaseID int
ChipsetID int
FWVersion nvarchar(10)
ETRACKID nvarchar(15)

表 5：芯片组

ID (Primary Key)
Chipsetnvarchar(20)

标签： sqlsql-servertsqlgreatest-n-per-groupsql-server-2019

解决方案

一种方法使用窗口函数：

SELECT *
FROM (
    SELECT 
        dbo.Model.ModelName AS AOC,
        dbo.Chipset.Chipset,
        dbo.FormFactor.FormFactor AS Form_Factor,
        dbo.ProductRelease.ECO AS Release,
        dbo.ProductRelease.Date AS Release_Date,
        dbo.Intel.FWVersion AS FW_Version,
        dbo.Intel.ETRACKID,
        ROW_NUMBER() OVER(PARTITION BY dbo.Model.ModelName ORDER BY dbo.ProductRelease.Date DESC) rn
    FROM
        dbo.Intel
        INNER JOIN dbo.ProductRelease ON dbo.Intel.ProductReleaseID = dbo.ProductRelease.ID
        INNER JOIN dbo.Model ON dbo.ProductRelease.ModelID = dbo.Model.ID
        INNER JOIN dbo.FormFactor ON dbo.Model.FormFactorID = dbo.FormFactor.ID
        INNER JOIN dbo.Chipset ON dbo.Intel.ControllerID = dbo.Chipset.ControllerID AND dbo.Intel.ChipsetID = dbo.Chipset.ID
) t
WHERE rn = 1
ORDER BY AOC

推荐阅读