34,590
社区成员
发帖
与我相关
我的任务
分享
create table qz
(ID int,NAME varchar(10),SEX varchar(5),mark varchar(10),CreateTime varchar(10))
insert into qz
select 1,'小王','男','其它','14:00' union all
select 2,'小王','男','测试','14:20' union all
select 3,'小王','男','来','13:00' union all
select 4,'小华','男','来','13:10' union all
select 5,'小华','男','来','13:20' union all
select 6,'小李','男','来','13:00'
-- 方法1
select *
from qz a
where not exists(select 1
from qz b
where b.NAME=a.NAME and b.CreateTime>a.CreateTime)
/*
ID NAME SEX mark CreateTime
----------- ---------- ----- ---------- ----------
2 小王 男 测试 14:20
5 小华 男 来 13:20
6 小李 男 来 13:00
(3 row(s) affected)
*/
-- 方法2
select a.*
from qz a
inner join(select NAME,
max(CreateTime) 'maxCreateTime'
from qz
group by NAME) b
on a.NAME=b.NAME and a.CreateTime=b.maxCreateTime
/*
ID NAME SEX mark CreateTime
----------- ---------- ----- ---------- ----------
2 小王 男 测试 14:20
6 小李 男 来 13:00
5 小华 男 来 13:20
(3 row(s) affected)
*/
with a(id,name,sex,mark,createtime) as
(select 1,'小王','男','其它','14:00' union all
select 2,'小王','男','测试','14:20' union all
select 3,'小王','男','来','13:00' union all
select 4,'小华','男','来','13:10' union all
select 5,'小华','男','来','13:20' union all
select 6,'小李','男','来','13:00')
SELECT a.id,a.name,a.sex,a.mark,a.createtime FROM a JOIN
(SELECT name,MAX(createtime)createtime FROM a
GROUP BY name) AS T ON a.name=T.name AND a.createtime=T.createtime
ORDER BY a.id
select t1.*
from table t1,(select Name,Max(CreateTime) CreateTime from table group by Name) t2 where t1.Name=t2.Name and t1.CreateTime=t2.CreateTime
with a(id,name,sex,mark,createtime) as
(select 1,'小王','男','其它','14:00' union all
select 2,'小王','男','测试','14:20' union all
select 3,'小王','男','来','13:00' union all
select 4,'小华','男','来','13:10' union all
select 5,'小华','男','来','13:20' union all
select 6,'小李','男','来','13:00')
select id,name,sex,mark,createtime
from
(select *,ROW_NUMBER()over(partition by name order by createtime desc ) as tid from a) as b
where b.tid=1 order by id