作者:whisper
链接:http://proprogrammar.com:443/article/644
声明:请尊重原作者的劳动,如需转载请注明出处
销售表
Sales
:+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) 是销售表 Sales 的主键. product_id 是产品表 Product 的外键. 注意: price 表示每单位价格
产品表
Product
:+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id 是表的主键.
写一条SQL 查询语句获取产品表 Product 中所有的 产品名称 product name 以及 该产品在 Sales 表中相对应的 上市年份 year 和 价格 price。
示例:
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_name | year | price | +--------------+-------+-------+ | Nokia | 2008 | 5000 | | Nokia | 2009 | 5000 | | Apple | 2011 | 9000 | +--------------+-------+-------+
select p.product_name, s.year, s.price
from Product p inner join Sales s
on p.product_id = s.product_id;
简单的内连接
销售表:
Sales
+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ sale_id 是这个表的主键。 product_id 是 Product 表的外键。 请注意价格是每单位的。
产品表:
Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id 是这个表的主键。
编写一个 SQL 查询,按产品 id
product_id
来统计每个产品的销售总量。查询结果格式如下面例子所示:
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 | +--------------+----------------+
select p.product_id, sum(s.quantity) total_quantity
from Product p inner join Sales s
on p.product_id = s.product_id
group by p.product_id
在上题基础上加了分组求和
销售表
Sales
:+-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ sale_id 是此表的主键。 产品 ID 是产品表的外键。 请注意,价格是按每单位计的。
产品表
Product
:+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ 产品 ID 是此表的主键。
编写一个 SQL 查询,选出每个销售产品的 第一年 的 产品 id、年份、数量 和 价格。
查询结果格式如下:
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 | first_year | quantity | price | +------------+------------+----------+-------+ | 100 | 2008 | 10 | 5000 | | 200 | 2011 | 15 | 9000 | +------------+------------+----------+-------+
select p.product_id, s2.year first_year, s2.quantity, s2.price
from Product p inner join (
select product_id, min(year) minYear
from Sales
group by product_id
) s
on p.product_id = s.product_id
inner join Sales s2
on s2.product_id = p.product_id and s2.year = s.minYear
在第一题的基础上求最小年份
再看另一种解法
select product_id,year first_year,quantity,price
from
sales
where
(product_id,year) in(select product_id,min(year) from sales
group by product_id)
这里用多字段in和子查询代替连接求最小年份,是求最值的一种好方法,学一下多字段in的用法
亲爱的读者:有时间可以点赞评论一下
全部评论