我要评论目录
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
发表评论