題目
銷(xiāo)售表:Sales
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
sale_id 是這個(gè)表的主鍵。
product_id 是 Product 表的外鍵胞谭。
請(qǐng)注意價(jià)格是每單位的酌住。
產(chǎn)品表:Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id 是這個(gè)表的主鍵。
編寫(xiě)一個(gè) SQL 查詢,按產(chǎn)品 id product_id 來(lái)統(tǒng)計(jì)每個(gè)產(chǎn)品的銷(xiāo)售總量垃环。
查詢結(jié)果格式如下面例子所示:
Sales 表:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
+---------+------------+------+----------+-------+
Product 表:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
+------------+--------------+
Result 表:
+--------------+----------------+
| product_id | total_quantity |
+--------------+----------------+
| 100 | 22 |
| 200 | 15 |
+--------------+----------------+
解答
對(duì)Sales表 對(duì)product_id分組后對(duì)quantity求和即可
select product_id, sum(quantity) as total_quantity
from Sales
group by product_id;