mysql - 是否有更好/更简洁的方式来编写此查询？

Question

我正在编写一个股票数据库查询，我正在寻找每年(2010 年 - 2016 年)价格涨幅排名前 5 位的股票。现在我是这样写的:

(
   SELECT Ticker, Year, MAX(High - Low)
   FROM Stocks
   WHERE Year = 2010
   GROUP BY Ticker
   ORDER BY MAX(High - Low) DESC
   LIMIT 5
)
UNION ALL
(
   SELECT Ticker, Year, MAX(High - Low)
   FROM Stocks
   WHERE Year = 2011
   GROUP BY Ticker
   ORDER BY MAX(High - Low) DESC
   LIMIT 5
)
UNION ALL

并在 2016 年之前每年重复一次。

建表语句为:

CREATE TABLE Stocks (
    Year   DATE
    Ticker VARCHAR(8),
    High   FLOAT,
    Low    FLOAT,
    Volume FLOAT,
    PRIMARY KEY(Ticker, Year)
);

结果数据集将是这样的:

Year    Ticker    MAX(High - Low)
---------------------------------
2010    GOOG      100
2010    FB        99
2010    AAPL      98
2010    NFLX      97
2010    ABCD      96
2011    FB        120
2011    ABCD      112
etc... (Top 5 in terms of high-low per year)

有没有更好更简洁的写法？

Answer 1

你可以试试IN语句。

SELECT Ticker, Year, MAX(High - Low)
FROM Stocks
WHERE Year IN ('2010', '2011', '2012', '2013', '2014', '2015', '2016')
GROUP BY Ticker
ORDER BY MAX(High - Low) DESC

希望这对您有所帮助。