我有一張桌子，我在那里保存產品[prod]以及它們的銷售和[market]購買位置以及買賣價格[buy / sell]。我想獲得每種產品的最低購買價格和最高銷售價格以及市場名稱。我怎樣才能在 MS SQL 中完成它？
我的桌子看起來像這樣；
id | prod |market| buy | sell|
--- ------ ------ ----- -----
1 | a | x | 25 | 26 |
2 | b | x | 15 | 16 |
3 | c | x | 17 | 19 |
4 | a | y | 24 | 25 |
5 | b | y | 14 | 17 |
6 | c | y | 19 | 24 |
7 | a | z | 23 | 24 |
8 | b | z | 17 | 18 |
9 | c | z | 18 | 22 |
這就是我想要的桌子的樣子；
|prod|MarketMin|MarketMax| Min Buy |Max Sell |
---- --------- --------- ---------- ---------
| a | z | x | 23 | 26 |
| b | y | z | 14 | 24 |
| c | x | y | 17 | 24 |
uj5u.com熱心網友回復：
您可以RANK()在子查詢中使用來實作這一點：
SELECT mn.prod, mn.MarketMin, sq.MarketMax, mn.MinBuy, sq.MaxSell
FROM
(
SELECT prod, market AS MarketMin, buy AS MinBuy, RANK() OVER(PARTITION BY prod ORDER BY buy ASC) as buy_rnk
from MARKETVALUES
) as mn
INNER JOIN
(
SELECT prod, MarketMax, MaxSell
FROM
(
SELECT prod, market AS MarketMax, sell AS MaxSell, RANK() OVER(PARTITION BY prod ORDER BY sell DESC) as sell_rnk
from MARKETVALUES
) as mx
WHERE sell_rnk =1
) as sq
ON mn.prod=sq.prod
WHERE buy_rnk=1
看db<>fiddle中的demo
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/392998.html
標籤：sql sql-server 查询语句 最大限度 分钟
上一篇：如何在ASP.NETCore中生成具有特定范圍的主鍵？[復制]
下一篇：按序列號和按標志分組的SUM值