id user content
1 aaa test
2 aaa test1
3 bbb test2
4 aaa test3
5 ccc test4
6 bbb test5
7 bbb test6
如上表,需要查询数据。常规写法是 select * from table order by id desc现有个需求,user字段,一个用户名只显示一条最新的。语句该如何写呀?
1 aaa test
2 aaa test1
3 bbb test2
4 aaa test3
5 ccc test4
6 bbb test5
7 bbb test6
如上表,需要查询数据。常规写法是 select * from table order by id desc现有个需求,user字段,一个用户名只显示一条最新的。语句该如何写呀?
select *
from tb t
where not exists (select 1 from tb where user = t.user and id > t.id)
select *
from tb t
where id = (select max(id) from tb where user = t.user)
where not exists(select * from table as b where a.user=b.user and a.id<b.id)
from tb t
where id = (select max(id) from tb where user = t.user)
select * from
tb a
where
not exists(select 1 from tb user=a.user and id>a.id)
--方法二
select * from
tb a where id=(select max(id) from tb user=a.user )
select * from table a where id =(select max(id) from table b where a.user=b.user)