Year Brand Amount
2018 Apple 45000
2019 Apple 35000
2020 Apple 75000
2018 Samsung 15000
2019 Samsung 20000
2020 Samsung 95000
2018 Nokia 21000
2019 Nokia 17000
2020 Nokia 14000
我希望expexted輸出如下：
Year Brand Amount
2018 Apple 45000
2019 Apple 35000
2020 Samsung 95000
這就是我嘗試過的：
Select Year, Brand, Max(Amount)as HighestPrice
from Practice
Group by Year
但它顯示錯誤：
“列‘Practice.Brand’在選擇串列中無效，因為它既不包含在聚合函式中，也不包含在 GROUP BY 子句中。”
我將非常感謝您的幫助。謝謝
uj5u.com熱心網友回復：
發生錯誤是因為您在 group by 中都需要兩者。我只需撰寫一個標準選擇并加入子查詢中的最大數量并與年份相關聯。
SELECT YEAR
,BRAND
,Amount AS HighestPrice
FROM Practice B
WHERE Amount = (SELECT MAX(Amount) FROM Practice A
WHERE A.YEAR = B.YEAR)
ORDER BY YEAR ASC
uj5u.com熱心網友回復：
一個通用的 SQL 版本是：
select p.Year, p.Brand, p.Amount as HighestPrice
from Practice p
join (
select Year, max(Amount) as Amount
from Practice
group by Year
) as m on m.Amount=p.Amount
uj5u.com熱心網友回復：
您可以使用磁區，例如在 ssms 中：
SELECT Year, Brand, Amount as HighestPrice
FROM(
SELECT
Year, Brand, Amount,
RANK()OVER (PARTITION BY year ORDER BY Amount DESC) AS rn
FROM Practice
) a
WHERE rn = 1
ORDER BY year
uj5u.com熱心網友回復：
你沒有說你使用的是哪個資料庫，所以我假設它是 PostgreSQL。您可以使用DISTINCT ON獲取每年最大金額的行。
例如：
select distinct on (year) * from practice order by year, amount desc
uj5u.com熱心網友回復：
Or you can use cross apply.
select a.year, a.brand, b.amount
from yourtable a
cross apply (select max(amount) as amount from yourtable
where a.year = year
group by year
having max(amount) =a.amount
order by amount desc)b
uj5u.com熱心網友回復：
試試這個： https ://dbfiddle.uk/Cr6kFqaE
select XX.Year,Table1.Brand ,XX.Amount from ( select Year, Max(Amount) as Amount from Table1 group by Year ) as XX left outer join Table1 on Table1.Year= XX.Year and Table1.Amount = XX.數量
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/510534.html
標籤：sql