SQL Server FORMAT(SUM(Value*Value)，'C')不允许我按DESC正确排序

f3temu5u 于 2022-12-17 发布在其他

关注(0)|答案(1)|浏览(131)

我目前正尝试以降序方式列出OrderRevenue，但在测试查询时，它列出的值降序不正确。
我尝试使用的方法：

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName + ' ' + E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, FORMAT(SUM(d.Quantity*p.Price), 'C') AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName + ' ' + E.LastName, C.CustomerName, S.ShipperName, P.Price
ORDER BY OrderRevenue DESC

它为我打印的内容：
| 订单收入|
| - ------|
| 九百九十七元五角|
| 九百九十七元五角|
| 九百八十六元五角|
| 九十八元|
| 九百七十七元二角|
正如您所看到的，它的值没有正确地降序。我是否错误地使用了Format（）方法？

我使用的SQL架构是公共的，因此如果对您有帮助，请随时在W3上测试您的查询结果：https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_datepart

如果您能解释一下为什么SQL Server不喜欢我的代码，我将非常感激。谢谢！

sql-server

来源：https://stackoverflow.com/questions/74807887/formatsumvaluevalue-c-does-not-allow-me-to-order-by-desc-properly

1条答案

按热度按时间

jk9hmnmh1#

可以按以下顺序添加此ORDER BY SUM(d.Quantity*p.Price) DESC

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName + ' ' + E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, FORMAT(SUM(d.Quantity*p.Price), 'C') AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName + ' ' + E.LastName, C.CustomerName, S.ShipperName, P.Price
ORDER BY SUM(d.Quantity*p.Price) DESC

或者你也可以这样做

select *,FORMAT(f1.OrderRevenue, 'C') as OrderRevenue from (SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName + ' ' + E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, SUM(d.Quantity*p.Price) AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName + ' ' + E.LastName, C.CustomerName, S.ShipperName, P.Price) f1
ORDER BY f1.OrderRevenue DESC

赞(0）回复(0）举报 2022-12-17

我来回答

SQL Server FORMAT(SUM(Value*Value)，'C')不允许我按DESC正确排序

1条答案

相关问题

热门标签

最新问答