查出所有学生的是男生的班级

1
2
3
4
-- 方法一
select class from user group by class having count(*) = sum(case when gender = 1 then 1 else 0 end);
-- 方法二
select class from user group by class having max(gender)=1 and min(gender)=1;

查找存在重复记录的组

1
select field from table group by field having count(item) <> count(distinct item)

三个人能否坐得下

1
2
3
4
5
6
7
8
9
10
+------+-----------+
| seat | status    |
+------+-----------+
|    1 | 已预订    |
|    2 | 已预订    |
|    3 | 未预定    |
|    4 | 未预定    |
|    5 | 未预定    |
|    6 | 已预订    |
+------+-----------+

查找连续的三个座位为未预定

1
2
3
4
-- s1, s2作为起始和结束座位，使用not exists做减法去除存在已预订的位置
select s2.seat as start_seat, s1.seat as end_seat from seats as s1, seats as s2 where s1.seat = s2.seat + 2 and not exists (
	select * from seats as s3 where s3.seat between s2.seat and s1.seat and s3.status = '已预订'
);

单调递增和单调递减区间

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 股价动态表
+---------------------+-------+
| deal_date           | price |
+---------------------+-------+
| 2020-01-06 00:00:00 |  1000 |
| 2020-01-08 00:00:00 |  1050 |
| 2020-01-09 00:00:00 |  1050 |
| 2020-01-12 00:00:00 |   900 |
| 2020-01-13 00:00:00 |   880 |
| 2020-01-14 00:00:00 |   870 |
| 2020-01-16 00:00:00 |   920 |
| 2020-01-17 00:00:00 |  1000 |
+---------------------+-------+

求股价的递增或递减区间

1
2
-- 先求出所有区间的组合
select s1.deal_date as start_date, s2.deal_date as end_date from stock as s1, stock as s2 where s1.deal_date < s2.deal_date;
1
2
3
4
-- 拿上述结果减去不符合条件的(上一个问题只需根据status去除)
select s1.deal_date as start_date, s2.deal_date as end_date from stock as s1, stock as s2 where s1.deal_date < s2.deal_date and not exists (
	select * from stock as s3, stock as s4 where s3.deal_date between s1.deal_date and s2.deal_date and s4.deal_date between s1.deal_date and s2.deal_date and s3.deal_date < s4.deal_date and s3.price >= s4.price
);