你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。
注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
p.s. left join和普通join 在on的后面跟条件的区别 left on+条件，只改变右边的值，无法改变左表的值，左表值仍然保留，没有where 的直接过滤作用 inner join 后的on + 条件，可以直接过滤条件 等同于where的作用
select book_id from orders where orders.dispatch_date>='2018-06-23' group by book_id having sum(quantity)>=10 select b.book_id, b.`name` from books b left join orders o on b.book_id = o.book_id and o.dispatch_date >= '2018-06-23' -- 这里left join的连接条件有两个，若b中的某些记录不能同时满足，则仍会保留这些记录，只是o中的字段成为NULL -- b中记录的o字段为NULL则表示该书籍在过去一年中订单量为0 where b.available_from <= '2019-05-23' -- 但如果把available放到连接条件中，可能就会出现本该被筛掉的书籍由于left join被保留下来 group by b.book_id, b.`name` -- 这里b.name可有可无 having sum(o.quantity) < 10 or isnull(sum(o.quantity)); -- 如果o.quantity全是null则也符合要求编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。
select round(avg(l)*100,2) as average_daily_percent from( select count(distinct rid)/count(distinct tid) as l from ( select tmp.post_id as tid ,action_date,r.post_id as rid from( select post_id,action_date from actions where extra='spam' ) tmp left join Removals r on tmp.post_id=r.post_id ) tmp2 group by action_date ) tmp3简洁的写法 两层查询即可
SELECT ROUND(AVG(percent),2) average_daily_percent FROM ( SELECT action_date, COUNT(DISTINCT R.post_id) / COUNT(DISTINCT A.post_id) * 100 percent FROM Actions A LEFT JOIN Removals R ON A.post_id = R.post_id WHERE extra='spam' GROUP BY action_date ) T请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。
注意时间判断要放到on里，如果放到where直接筛掉了 ID为3和4的情况 而放在on还会保留ID为3和4的情况且值为null
select user_id buyer_id , join_date,count(o.order_id) as orders_in_2019 from users u left join orders o on u.user_id=o.buyer_id and year(o.order_date)='2019' group by u.user_id编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额 不用子查询的方法
select date_format(trans_date,"%Y-%m") as month, country, count(*) trans_count, sum(state='approved') approved_count, sum(amount) trans_total_amount, sum(case when state='approved' then amount else 0 end) approved_total_amount from transactions group by country, DATE_FORMAT(trans_date,"%Y-%m") select month,country,count(state) trans_count,count(a1) approved_count,sum(amount) trans_total_amount,ifnull(sum(a1),0) approved_total_amount from ( select substring(trans_date,1,7) as month, country , state ,amount , if (state='approved',amount,null) as a1 from Transactions ) tmp group by month,country每组的获胜者是在组内得分最高的选手。如果平局，player_id 最小 的选手获胜。
select group_id,player_id from( select group_id,tmp2.player_id ,row_number() over(partition by group_id order by s desc) rnk from ( select player_id,sum(score) s from (select first_player as player_id ,first_score as score from Matches union all select second_player as player_id ,second_score as score from Matches) tmp group by player_id )tmp2 join Players on tmp2.player_id=players.player_id ) tmp3 where rnk=1简洁写法，分组会取第一行，所以在内层按照分数降序排序
select group_id, player_id from ( select players.*, sum(if(player_id = first_player, first_score, second_score)) score from players join matches on player_id = first_player or player_id = second_player group by player_id order by score desc, player_id ) tmp group by group_id电梯最大载重量为 1000。
写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯 。
自连接，巧妙分组
select a.person_name from queue a, queue b where a.turn >= b.turn group by a.person_id having sum(b.weight) <= 1000 order by a.turn desc limit 1 select person_name from( select *, lead(snk,1 ) over() l1 from( select person_name, sum(weight) over (order by turn) snk from queue ) tmp ) tmp2 where (snk<=1000 and l1>1000) or(snk<=1000 and l1 is null)窗口函数的简单写法，不用再用lead，直接找到小于1000的最大值即可
select person_name from ( select person_name,turn,sum(weight)over(order by turn) as addup_weight from Queue a ) t where addup_weight<=1000 order by turn desc LIMIT 1