SQL - zhongjiajie/zhongjiajie.github.com GitHub Wiki
用于记录常见较为复杂sql
查找每个城市最新的记录
-- 测试例子
create table test_group_max(
id int primary key auto_increment,
city varchar(30),
info varchar(30),
crttime timestamp not null default current_timestamp
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
truncate table test_group_max;
insert into test_group_max(city, info, crttime) values
('A', 'abc', '2018-11-01 02:37:46'),
('B', '123', '2018-11-01 02:38:46'),
('C', '456', '2018-11-01 02:39:46'),
('A', '789', '2018-11-01 02:40:46'),
('B', 'def', '2018-11-01 02:41:46'),
('C', 'ghi', '2018-11-01 02:42:46'),
('A', 'xyz', '2018-11-01 02:43:46'),
('B', 'zxc', '2018-11-01 02:44:46'),
('C', 'qwe', '2018-11-01 02:45:46'),
('A', '!@#', '2018-11-01 02:46:46');| id | city | pop | info | crttime |
|---|---|---|---|---|
| 1 | 北京 | 100 | info1 | 时间戳 |
| 2 | 北京 | 100 | info2 | 时间戳 |
| 3 | 上海 | 100 | info3 | 时间戳 |
| 4 | 上海 | 100 | info4 | 时间戳 |
-- 1
select t1.*
from table t1
inner join (
select t2.city
, max(crttime) as max_crttime
from table t2
group by t2.city
) mc
on t1.city = mc.city
and t1.crttime = mc.max_crttime
-- 2 Joining with simple **group-identifier**, **max-value-in-group** Sub-query
select t1.*
from table t1
where t1.crttime = (
select max(t2.crttime)
from table t2
where t1.city = t2.city
)
-- 3
select t1.*
from table t1
where t1.id = (
select t2.id
from table t2
where t2.city = t1.city
order by t2.crttime desc
limit 1
)
-- 4 left Joining with self, tweaking join conditions and filters
-- The second join condition is having left side value less than right value
-- When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.
select a.*
from yourtable a
left outer join yourtable b
on a.id = b.id and a.rev < b.rev
where b.id is null;其中方法1 方法2 方法4对大部分问题都适用,但是如果每个城市有多条最近的记录就不适用了,方法三可以消除重复的最近时间,保证只有一条记录,或者将方法1进行修改:
select max(t1.id)
, t1.city
, max(t1.pop)
, max(t1.info)
, t1.crttime
from table t1
join (
select t2.city
, max(crttime) as max_crttime
from table t2
group by t2.city
) mc
on t1.city = mc.city
and t1.crttime = mc.max_crttime
group by t1.city, t1.crttime和上面的内容类似,但是找出前n条的记录
-- 找出前2条记录, 其中最大的时间记录匹配的是 0
select t1.id, t1.city, t1.info, t1.crttime, count(t2.city) as rank
from test_group_max t1
left outer join test_group_max t2
on t1.city = t2.city
and t1.crttime < t2.crttime
group by t1.id, t1.city, t1.info, t1.crttime
having count(t2.city) < 2
order by t1.city, t1.crttime desc