sql语句示例
一
一个叫salarys的表
id | salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
写出sql语句,返回工资第二高的id
select id from salarys order by salary desc limit 1,1;
返回:
id |
---|
2 |
二
一张叫scores的表
uid | score | date |
---|---|---|
1 | 2 | 2017-02-28 |
1 | 3 | 2017-03-02 |
3 | 2 | 2017-03-17 |
3 | 1 | 2017-03-17 |
3 | 2 | 2017-03-17 |
4 | 3 | 2017-03-25 |
3 | 5 | 2017-03-27 |
现在需要一份用户列表,这些用户在2017年3月份的31天中,至少要有16天,每天的得分总和大于40分,请用一条sql语句表示。
思路:
限制条件比较多,先易后难,先统计出每人每天的总分数
select uid,sum(score) as sum,date from scores group by date,uid;
uid | sum | date |
---|---|---|
1 | 2 | 2017-02-28 |
1 | 3 | 2017-03-02 |
select uid,sum(score) as sum,date from scores where date between ‘2017-03-01’ and ‘2017-03-31’ group by date,uid having sum>3;
未完待续