目录
1 找出合作过至少三次的演员和导演的 id 对(actor_id, director_id)
2 获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。
3 查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。
4 报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
6统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）
8 case 行转列 使得 每个月 都有一个部门 id 列和一个收入列
9找出每次的 query_name 、 quality 和 poor_query_percentage。
1 找出合作过至少三次的演员和导演的 id 对(actor_id, director_id)
示例 ：
输入：
ActorDirector 表：
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
输出：
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
解释：
唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。
sql:
select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*)>=32 获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。
示例 1：
输入：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 | +------------+--------------+
输出：
+--------------+-------+-------+ | product_name | year | price | +--------------+-------+-------+ | Nokia | 2008 | 5000 | | Nokia | 2009 | 5000 | | Apple | 2011 | 9000 | +--------------+-------+-------+
sql:
select product_name,year,price
from Sales
left join Product
on Sales.product_id=Product.product_id3 查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。
查询结果的格式如下：
Project 表： +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee 表： +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ Result 表： +-------------+---------------+ | project_id | average_years | +-------------+---------------+ | 1 | 2.00 | | 2 | 2.50 | +-------------+---------------+ 第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50
sql 1：
select project_id,round(avg(experience_years),2) average_years
from Employee left join Project on Employee.employee_id = Project.employee_id
where Employee.employee_id and Project.project_id is not null
group by Project.project_id;sql 2：
SELECT project_id,round(avg(experience_years),2) average_years
FROM Project p
JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY project_id注意：
round(number,digits)
digits>0，四舍五入到指定的小数位
digits=0, 四舍五入到最接近的整数
digits<0 ，在小数点左侧进行四舍五入
如果round()函数只有number这个参数，等同于digits=0
4 报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
示例 1:
输入： Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+ Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+ 输出： +-------------+--------------+ | product_id | product_name | +-------------+--------------+ | 1 | S8 | +-------------+--------------+ 解释: id 为 1 的产品仅在 2019 年春季销售。 id 为 2 的产品在 2019 年春季销售，但也在 2019 年春季之后销售。 id 为 3 的产品在 2019 年春季之后销售。 我们只返回 id 为 1 的产品，因为它是 2019 年春季才销售的产品。
sql 1:
select p.product_id,p.product_name
from Product as p , Sales as s
where p.product_id = s.product_id
group by product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31';sql 2:
select product_id, product_name
from Sales join Product
using(product_id)
group by product_id
having sum(sale_date between "2019-01-01" and "2019-03-31") = count(sale_date)注意：
在 SQL 中，使用 JOIN 进行表连接操作时，可以选择使用 USING、ON 或 WHERE 这三种关键词指定连接条件。USING 适用于两个表之间有同名列的情况，可以简化语法；ON 适用于没有同名列或需要指定非等值匹配的情况，使用更灵活；WHERE 可以实现连接效果，但不如使用 ON 或 USING 方便和直观。
5查询每位玩家 第一次登录平台的日期
查询结果的格式如下所示：
Activity 表： +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result 表： +-----------+-------------+ | player_id | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+
sql 1:
select player_id,event_date as first_login
from Activity
group by player_id
having min(event_date);sql 2:
select player_id,min(event_date) as first_login
from Activity
group by player_id6统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）
示例 1:
输入： Activity table: +---------+------------+---------------+---------------+ | user_id | session_id | activity_date | activity_type | +---------+------------+---------------+---------------+ | 1 | 1 | 2019-07-20 | open_session | | 1 | 1 | 2019-07-20 | scroll_down | | 1 | 1 | 2019-07-20 | end_session | | 2 | 4 | 2019-07-20 | open_session | | 2 | 4 | 2019-07-21 | send_message | | 2 | 4 | 2019-07-21 | end_session | | 3 | 2 | 2019-07-21 | open_session | | 3 | 2 | 2019-07-21 | send_message | | 3 | 2 | 2019-07-21 | end_session | | 4 | 3 | 2019-06-25 | open_session | | 4 | 3 | 2019-06-25 | end_session | +---------+------------+---------------+---------------+ 输出： +------------+--------------+ | day | active_users | +------------+--------------+ | 2019-07-20 | 2 | | 2019-07-21 | 2 | +------------+--------------+ 解释：注意非活跃用户的记录不需要展示。
sql:
select activity_date as day,count( distinct user_id) as active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
group by day7请查询出所有浏览过自己文章的作者，结果按 id 升序排列
示例 1：
输入： Views 表： +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 5 | 2019-08-01 | | 1 | 3 | 6 | 2019-08-02 | | 2 | 7 | 7 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | | 4 | 7 | 1 | 2019-07-22 | | 3 | 4 | 4 | 2019-07-21 | | 3 | 4 | 4 | 2019-07-21 | +------------+-----------+-----------+------------+ 输出： +------+ | id | +------+ | 4 | | 7 | +------+
sql：
select distinct author_id as id
from Views
where author_id=viewer_id
order by id asc注意：
升序：
select 字段1, 字段2, ... from 表名 order by 要排序的字段;
或：
select 字段1,字段2, ... from 表名 order by 要排序的字段名 asc;降序关键字为desc
8 case 行转列 使得 每个月 都有一个部门 id 列和一个收入列
示例 1：
输入： Department table: +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+ 输出： +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ 解释：四月到十二月的收入为空。 请注意，结果表共有 13 列（1 列用于部门 ID，其余 12 列用于各个月份）
sql:
select
id
, sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue
, sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue
, sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue
, sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue
, sum(case `month` when 'May' then revenue else null end) as May_Revenue
, sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue
, sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue
, sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue
, sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue
, sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue
, sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue
, sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id注意：
CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END THEN后边的值与ELSE后边的值类型应一致，否则会报错
9找出每次的 query_name 、 quality 和 poor_query_percentage。
quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。
位置:position,列的值为 1 到 500 。 评分:rating,列的值为 1 到 5 。 评分小于 3 的查询被定义为质量很差的查询。 示例 1： 输入： Queries table: +------------+-------------------+----------+--------+ | query_name | result | position | rating | +------------+-------------------+----------+--------+ | Dog | Golden Retriever | 1 | 5 | | Dog | German Shepherd | 2 | 5 | | Dog | Mule | 200 | 1 | | Cat | Shirazi | 5 | 2 | | Cat | Siamese | 3 | 3 | | Cat | Sphynx | 7 | 4 | +------------+-------------------+----------+--------+ 输出： +------------+---------+-----------------------+ | query_name | quality | poor_query_percentage | +------------+---------+-----------------------+ | Dog | 2.50 | 33.33 | | Cat | 0.66 | 33.33 | +------------+---------+-----------------------+ 解释： Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50 Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33 Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66 Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
注意：
判断不等于空应是is not null，而！=null会报错
10查找每种产品的平均售价
average_price 应该 四舍五入到小数点后两位。
示例 1：
输入： Prices table: +------------+------------+------------+--------+ | product_id | start_date | end_date | price | +------------+------------+------------+--------+ | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | +------------+------------+------------+--------+ UnitsSold table: +------------+---------------+-------+ | product_id | purchase_date | units | +------------+---------------+-------+ | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | +------------+---------------+-------+ 输出： +------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+ 解释： 平均售价 = 产品总价 / 销售的产品数量。 产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96 产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
sql:
select Prices.product_id,Round(if(UnitsSold.product_id is null,0,sum(units*price )/ sum(units)),2) average_price
from Prices left join UnitsSold
on Prices.product_id=UnitsSold.product_id
where UnitsSold.purchase_date>=Prices.start_date and UnitsSold.purchase_date<=Prices.end_date or UnitsSold.product_id is null
group by Prices.product_id注意：
IF 表达式
IF( expr1 , expr2 , expr3 )expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3
600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
