英文:
Top game with highest sales for each year
问题
以下是翻译好的部分:
我正在使用 SQL Server,有一张关于视频游戏销售的表格,涵盖了 1977 年至 2020 年的数据。以下的查询是我一直在使用以解决问题:**找到每年销售额最高的游戏**。当执行该查询时,输出结果给我多次相同的年份以及视频游戏,它们与它们应该在的地方相隔了 10 年。
我只想在 1977 年至 2020 年之间的每一年中,找到销售额最高的游戏。
期望的结果:
|发布年份|标题|总销售额|
|-|-|-|
|1977|Combat|1.25|
|1978|Space Invaders|2.53|
|1980|Asteroids|4.31|
英文:
I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each year. When executed the output gives me multiple of the same years and video games that are 10yrs ahead apart from where they should be.
SELECT VG1.Name, VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) AS Total_Sales
FROM Video_Game_Sales VG1
INNER JOIN Video_Game_Sales VG2 ON VG1.Name = VG2.Name
INNER JOIN Video_Game_Sales VG3 ON VG2.Global_sales = VG3.Global_sales
INNER JOIN Video_Game_Sales VG4 ON VG3.Release_Date = VG4.Release_Date
GROUP BY VG4.Release_Date, VG1.Name
ORDER BY VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) DESC;
I want only one game for each year between 1977-2020 with the highest total sold.
Desired results:
Release_year | Title | Total_Sales |
---|---|---|
1977 | Combat | 1.25 |
1978 | Space Invaders | 2.53 |
1980 | Asteroids | 4.31 |
答案1
得分: 1
Release_Year | Title | Total_Sales |
---|---|---|
2018 | Red Dead Redemption 2 | 50000000 |
2019 | Call of Duty: Modern Warfare | 30000000 |
2020 | Animal Crossing: New Horizons | 41590000 |
英文:
Assuming there is no duplicate records for each game (otherwise, you'll need to handle this first), the first step is to get the top selling games for each year (we'll filter the desired years on this subquery). Then we join this result back with the original table, in order to get the Name of the game.
Query:
SELECT
top_sellers.Release_Year,
sales.Name AS Title,
top_sellers.Global_sales AS Total_Sales
FROM (
SELECT YEAR(Release_Date) AS Release_Year, MAX(Global_sales) AS Global_sales
FROM Video_Game_Sales
WHERE YEAR(Release_Date) BETWEEN '1977' AND '2020'
GROUP BY YEAR(Release_Date)
) AS top_sellers
JOIN Video_Game_Sales AS sales
ON top_sellers.Global_sales = sales.Global_sales
AND top_sellers.Release_Year = YEAR(sales.Release_Date)
ORDER by Release_Year ASC
Input:
Name | Release_Date | Global_sales |
---|---|---|
Red Dead Redemption 2 | 2018-10-26 | 50000000 |
Super Smash Bros. Ultimate | 2018-12-7 | 30440000 |
Call of Duty: Modern Warfare | 2019-10-25 | 30000000 |
Pokémon Sword / Shield | 2019-11-15 | 25680000 |
Animal Crossing: New Horizons | 2020-03-20 | 41590000 |
Output:
Release_Year | Title | Total_Sales |
---|---|---|
2018 | Red Dead Redemption 2 | 50000000 |
2019 | Call of Duty: Modern Warfare | 30000000 |
2020 | Animal Crossing: New Horizons | 41590000 |
Demo in sqlfiddle.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论