SQL查询获取列的最大值并显示每一行
问题描述:
这是我的场景... 多个项目类型具有多个价格的多个项目。 想要选择显示最高价格的所有类型的项目。无法弄清楚如何获得最大值?SQL查询获取列的最大值并显示每一行
输入:
ProductId ProductType Description Price
1所述的烤豆1.29
1乙烤豆1.98
输出:
ProductId ProductType Description Price
1所述的烤豆1.98
1 B烤豆豆1.98
有什么建议吗?
答
Select ProductId, ProductType, Description, MaxByDesc.MaxPrice
From Product
Join (
Select Description, Max(Price) As MaxPrice
From Product
Group By Description
) As MaxByDesc
On MaxByDesc.Description = Product.Description
如果您使用的是SQL Server 2005或更高版本:
Select ProductId, ProductType, Description
, Max(Price) Over (Partition By Description) As MaxPrice
From Product
答
试试这个:
SELECT ProductId,
ProductType,
Description,
b.price
FROM <YOUR_TABLE> a,
(SELECT MAX(price) price FROM <YOUR_TABLE>) b
对于那些谁爱ANSI语法:
SELECT ProductId,
ProductType,
Description,
b.price
FROM <YOUR_TABLE> a INNER JOIN
(SELECT MAX(price) price FROM <YOUR_TABLE>) b
ON 1=1
答
托马斯,对,除了你需要按类型分组:
Select ProductId, ProductType, Description, MaxByDesc.MaxPrice
From Product
Join (
Select Description, Max(Price) As MaxPrice
From Product
Group By ProductType
) As MaxByDesc
On MaxByDesc.ProductType = Product.ProductType
答
另一种方式:
SELECT ProductId
, ProductType
, Description
, (SELECT MAX(price)
FROM Product pg
WHERE pg.Description = p.Description
) AS MaxPrice
FROM Product p
+0
更新了帖子我投票赞成托马斯的答案,尽管这两者中的任何一个都是。 – 2011-04-12 19:55:07
它的工作原理。非常感谢!我在我的子查询中使用ROW_NUMBER()来获取最新的Price。不知道我可以使用MAX()与分区BY ... – let007live4ever 2011-04-12 18:24:28