[Leetcode 1069] Product Sales Analysis II

原题说明

Table: Sales

+————-+——-+
| Column Name | Type  |
+————-+——-+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+————-+——-+
sale_id is the primary key of this table.
product_id is a foreign key to Product table.
Note that the price is per unit.

Table: Product

+————–+———+
| Column Name  | Type    |
+————–+———+
| product_id   | int     |
| product_name | varchar |
+————–+———+
product_id is the primary key of this table.

Write an SQL query that reports the total quantity sold for every product id.

The query result format is in the following example:

Sales table:
+———+————+——+———-+——-+
| sale_id | product_id | year | quantity | price |
+———+————+——+———-+——-+
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+———+————+——+———-+——-+
Product table:
+————+————–+
| product_id | product_name |
+————+————–+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+————+————–+
Result table:
+————–+—————-+
| product_id   | total_quantity |
+————–+—————-+
| 100          | 22             |
| 200          | 15             |
+————–+—————-+

解题思路

不需要Product table, 直接 group by product_id即可。

示例代码 (mysql)

1 2	# Write your MySQL query statement below select product_id, sum(quantity) as total_quantity from Sales group by product_id;

视频讲解

我们在Youtube上更新了视频讲解，欢迎关注！