我想查看按總凈價排序的前 300 件商品，我該怎么做？
使用 SSMS 2014
如果我洗掉 group by 我得到錯誤：列 'orderitems.orderid' 在選擇串列中無效，因為它不包含在聚合函式或 GROUP BY 子句中。
請參閱下面的作業：
select top 300
orderitems.orderid, orders.traderid, orders.orderdate,
SUM(orderitems.nettprice) AS nettprice
from orderitems
INNER JOIN orders ON orders.tradertype = 'S' AND orders.id =
orderitems.orderid
where orderitems.ordertype = 'PO'
group by orderitems.orderid, orders.traderid, orders.orderdate,
orderitems.nettprice
order by orderitems.nettprice DESC
uj5u.com熱心網友回復：
您需要按SUM價值訂購。您可以將其ORDER BY顯式放入，也可以使用沒有表參考的SELECT列名（換句話說，您在 中使用的列別名）SELECT
我強烈建議您使用短表別名來使您的代碼更具可讀性
select top 300
oi.orderid,
o.traderid,
o.orderdate,
SUM(oi.nettprice) AS nettprice
from orderitems AS oi
INNER JOIN orders AS o ON o.tradertype = 'S' AND o.id = oi.orderid
where oi.ordertype = 'PO'
group by
oi.orderid, o.traderid, o.orderdate
order by
nettprice DESC
-- alternatively
order by
SUM(oi.nettprice) DESC
uj5u.com熱心網友回復：
也許您在這里需要的是一個視窗SUM，然后是一個TOP. 該PARTITION BY條款基于以下評論：
SELECT TOP (300)
oi.orderid,
o.traderid,
o.orderdate,
SUM(oi.nettprice) OVER (PARTITION BY oi.itemnumber, oi.partid, oi.quantity) AS totalnettprice
FROM dbo.orderitems oi
INNER JOIN dbo.orders o ON o.id = oi.orderid
WHERE o.tradertype = 'S'
AND oi.ordertype = 'PO'
ORDER BY oi.totalnettprice DESC;
uj5u.com熱心網友回復：
很簡單：
select top 300
itm.orderid,
ord.traderid,
ord.orderdate,
SUM(itm.nettprice) AS price
from orderitems itm
INNER JOIN orders ord ON
ord.tradertype = 'S' AND ord.id = itm.orderid
where itm.ordertype = 'PO'
group by
itm.orderid,
ord.traderid,
ord.orderdate
order by price DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/361415.html
標籤：sql sql-server
上一篇：SQLx到Mssql連接字串