sql 查询去掉某列重复值~

非红色部分是正常的查询语句,在外面在嵌套一个查询语句来达到去掉  dbo.Asset.Id 列重复的效果

红色部分是核心

 

 

 

select * from 
(
    SELECT   TOP (100) PERCENT dbo.AssetCategory.Name AS 资产类型, dbo.Asset.Id AS 资产编号, dbo.Asset.Name AS 资产名称, 
                    dbo.AssetDetail.Brand AS 资产品牌, dbo.DataCenter.Name AS 数据中心, dbo.Cabinet.Name AS 上架机柜, 
                    dbo.Position.Unit AS 机柜位置,row_number() over (partition by dbo.Asset.Id order by dbo.Asset.Id desc)as 新增判断列
    FROM      dbo.Position INNER JOIN
                    dbo.Cabinet ON dbo.Position.CabinetId = dbo.Cabinet.Id INNER JOIN
                    dbo.DataCenter ON dbo.Cabinet.DataCenter_Id = dbo.DataCenter.Id INNER JOIN
                    dbo.Asset ON dbo.Position.AssetId = dbo.Asset.Id INNER JOIN
                    dbo.AssetDetail ON dbo.Asset.Id = dbo.AssetDetail.Id INNER JOIN
                    dbo.AssetCategory ON dbo.Asset.CategoryId = dbo.AssetCategory.Id
    ORDER BY dbo.Position.Id, 资产编号
) as newtable

where newtable.新增判断列=1

 

或者

select * from AssetDemo_01 as TempTable 
where TempTable.Flag='上架' 
and not exists 
    (
        select 1 from AssetDemo_01 
        where AssetDemo_01.Flag='上架' 
        and  AssetDemo_01.Name=TempTable.Name 
        and AssetDemo_01.Id>TempTable.Id
    )