如何在T-SQL中选择具有最大值的列？

2024-04-30 • 问答

我正在寻找销售量最高的月份，我可以使用以下方法显示每个月的销售量：

SELECT Month(orderdate)  AS Month,Year(orderdate)   AS Year,Sum(salesorderid) AS Sales 
FROM   sales.salesorderheader 
GROUP  BY Month(orderdate),Year(orderdate)

哪个显示

如何在T-SQL中选择具有最大值的列？

但是我只想选择销售最高的月份。

hellobaby12321 回答：如何在T-SQL中选择具有最大值的列？

如果我是正确的话，您希望以最大的销售额打印单个结果这里的“ salesorderid”是该月的总销售额

尝试添加MAX

SELECT Month(orderdate)  AS Month,Year(orderdate)   AS Year,MAX(Sum(salesorderid)) AS Sales 
FROM   sales.salesorderheader 
GROUP  BY Month(orderdate),Year(orderdate)

或者尝试使用HAVING子句

SELECT Month(orderdate)  AS Month,Sum(salesorderid) AS Sales 
FROM   sales.salesorderheader 
GROUP  BY Month(orderdate),Year(orderdate) 
HAVING MAX(Sum(salesorderid))

因为总和（salesorderid）是可能有效的总销售额...

,

您必须先group year来month。然后使用count()而不是sum()（似乎您的列是SalesOrderID），但是如果您尝试获取sum of sales，则应该类似于sum(salesamount)。还要更改您的column aliases，即sql keywords。

select month(OrderDate) as oMonth,year(OrderDate) as oYear,count(1) as SalesCount
from SalesOrderHeader
group by year(OrderDate),month(OrderDate)

获取most number of sales。

select top 1 * from (
    select month(OrderDate) as oMonth,count(1) as SalesCount
    from SalesOrderHeader
    group by year(OrderDate),month(OrderDate)) t1
order by t1.SalesCount desc

,

只需使用top (1)：

SELECT TOP (1) Month(orderdate)  AS Month,Sum(salesorderid) AS Sales 
FROM sales.salesorderheader 
GROUP BY Month(orderdate),Year(orderdate) 
ORDER BY Sales DESC;

,

您可以尝试

使用ORDER BY“ 字段名称” DESC
如果字段“ salesorderid ”中没有键入编号，则ORDER BY函数将无法运行。因此，您可以在使用CAST("field name" AS Decimal)

检查以下代码段

SELECT Month(a.orderdate)  AS Month,Year(a.orderdate)   AS Year,Sum(a.salesorderid) AS Sales                                               
FROM   sales.salesorderheader  a
GROUP BY Month(a.orderdate),Year(a.orderdate) 
ORDER BY CAST(a.Sum(a.salesorderid),AS Decimal) DESC

本文链接：https://www.f2er.com/3155361.html