回到SQL.
目录
1.员工奖金
连接题。
# Write your MySQL query statement below
SELECT t1.name,t2.bonus
FROM Employee t1
LEFT JOIN Bonus t2
ON t1.empId=t2.empId
WHERE t2.bonus is null or t2.bonus<1000;
2.每月交易 I
# Write your MySQL query statement below
select
left (trans_date,7) month,
country,
count(state) trans_count,
sum(if(state='approved',1,0)) approved_count,
sum(amount) trans_total_amount,
sum(if(state='approved',amount,0)) approved_total_amount
from Transactions
group by month,country;
ps:
left(x,7)取前7个字符。
count()计数
sum()求和
3.只出现一次的最大数字
排序和分组。
# Write your MySQL query statement below
select (
select t1.num
from MyNumbers t1
group by t1.num
having count(*)=1
order by t1.num desc
limit 1
) as num; 
4.最后一个能进入巴士的人
# Write your MySQL query statement below
select tmp1.person_name
from(
select person_name,@pre:=weight+@pre as weight
from queue,(
select @pre:=0
)tmp
order by turn
) tmp1
where tmp1.weight<=1000
order by tmp1.weight desc
limit 1;
ps:
5. 2016年的投资
# Write your MySQL query statement below
select round(sum(tiv_2016),2) tiv_2016
from
(
select *,
count(*) over (partition by concat(lat,lon)) a,
count(*) over (partition by tiv_2015) b
from Insurance
) t
where a=1 and b>1