sql-server 仅选择SQL SERVER中具有max(id)的行

6mzjoqzu 于 2022-10-31 发布在 SQL Server

关注(0)|答案(5)|浏览(296)

我有一张表A：

ID     | ProductCatId | ProductCode | Price
1      |       1      |  PROD0001   | 2
2      |       2      |  PROD0005   | 2
3      |       2      |  PROD0005   | 2
4      |       3      |  PROD0008   | 2
5      |       5      |  PROD0009   | 2
6      |       7      |  PROD0012   | 2

我想选择ID、产品类别ID、产品代码、价格，条件为：“如果ProductCatId存在相同的值，那么用max（ID）获取ProductCatId "，如：

ID     | ProductCatId | ProductCode | Price
1      |       1      |  PROD0001   | 2
3      |       2      |  PROD0005   | 2
4      |       3      |  PROD0008   | 2
5      |       5      |  PROD0009   | 2
6      |       7      |  PROD0012   | 2

sql-server

来源：https://stackoverflow.com/questions/38912808/select-only-row-with-maxid-in-sql-server

5条答案

按热度按时间

jogvjijk1#

执行窗口函数和row_number（）

select ID , ProductCatId , ProductCode , Price
  from (
        select ID , ProductCatId , ProductCode , Price, row_number() over (partition by ProductCatId order by ID desc) as rn
         from myTable
        ) as t
  where t.rn = 1

赞(0）回复(0）举报 2022-10-31

gr8qqesn2#

你可以试试这个，

Select Max(ID),ProductCatId,ProductCode,price
From TableName
Group By ProductCatId,ProductCode,price

赞(0）回复(0）举报 2022-10-31

rta7y2nd3#

select 
top 1 with ties
ID,ProductCatId,ProductCode,Price
from
table
order by
row_number() over (partition by productcatid order by id desc)

赞(0）回复(0）举报 2022-10-31

anhgbhbe4#

可以使用row_number()：

select t.*
from (select t.*,
             row_number() over (partition by ProductCatId order by ID desc) as seqnum
      from @Table t
     ) t
where seqnum = 1
order by ID;

赞(0）回复(0）举报 2022-10-31

iq3niunx5#

稍短：

select distinct 
    max(ID) over (partition by ProductCatId, 
                               ProductCode, 
                               Price) as ID,
    ProductCatId, 
    ProductCode, 
    Price,
  from myTable

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server 仅选择SQL SERVER中具有max(id)的行

5条答案

相关问题

热门标签

最新问答