gpt4 book ai didi

mysql - 获取子查询的最大值后显示该行的其他值

转载 作者:行者123 更新时间:2023-11-29 15:33:05 26 4
gpt4 key购买 nike

所以我有这个查询:

select MAX(C.Sales) as 'NumOfSales'
from
(select COUNT(SalesOrderID) as 'Sales',
TerritoryID
from Sales.SalesOrderHeader
group by TerritoryID) C

现在它只显示 C.Sales 的最大值我希望能够获得最大值的 Territory。

最佳答案

按前 1 行降序排列:

SELECT TOP 1 COUNT(SalesOrderID) AS Sales,
TerritoryID
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY Sales DESC

如果您需要具有相同最大销售额的所有行,则涉及更多:以下是通过一些示例数据实现此目的的方法:

DECLARE @SalesOrerHeader TABLE 
(
SalesOrderID INT IDENTITY(1,1),
TerritoryID INT
)

INSERT INTO @SalesOrerHeader (TerritoryID) VALUES
(1),(1),(2),(2),(2),(2),(3),(4),(4),(4),(5),(5),(5),(5)

DECLARE @final TABLE
(
TerritoryID INT,
NumberOfSales INT
)

INSERT INTO @final
SELECT TerritoryID, COUNT(SalesOrderID) AS NumberOfSales FROM @SalesOrerHeader
GROUP BY TerritoryID

SELECT * FROM @final
WHERE NumberOfSales = (SELECT MAX(NumberOfSales) FROM @final)

输出:

TerritoryID NumberOfSales
2 4
5 4

关于mysql - 获取子查询的最大值后显示该行的其他值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58529587/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com