select username,min(createtime) createtime as from 表 group by username order by createtime
select username,max(createtime) from tablename group by username order by username
select distinct username from 表 order by createtime
时间最近也就是时间最大了 select username,max(createtime) from table group by username order by createtime
select distinct username from 表 a where createtime=(select max(createtime) from 表 where username=a.username) order by createtime
select distinct username,max(createtime) from 表 order by createtime
--上面的要改一下:select distinct * from 表 a where createtime=(select max(createtime) from 表 where username=a.username) order by createtime
--用distinct 是为了保证,如果createtime要相同,只取一个.如果没有这个问题,不需要distinct--下面是测试: --测试数据 declare @表 table(username varchar(20),createtime datetime) insert into @表 select 'sundy007','2004-01-10 22:59:09.000' union all select 'sundy007','2004-01-11 00:55:43.000' union all select 'sundy007','2004-01-11 02:17:54.000' union all select 'three','2004-01-11 02:20:52.000' union all select 'yishanjushi','2004-01-11 03:00:46.000' union all select 'lll','2004-01-12 01:48:36.000' union all select 'lll','2004-01-16 04:23:59.000'--查询 select distinct * from @表 a where createtime=(select max(createtime) from @表 where username=a.username) order by createtime/*--测试结果 username createtime -------------------- ----------------------------- sundy007 2004-01-11 02:17:54.000 three 2004-01-11 02:20:52.000 yishanjushi 2004-01-11 03:00:46.000 lll 2004-01-16 04:23:59.000(所影响的行数为 4 行) --*/
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中。 select distinct xinghao,max(dd) as dtime from product group by xinghao order by dtime asc
谢了,上面的语句我试了一下,还是决定用 zjcxc(邹建) 的, 不过上面好几个都同不过,如:select username,max(createtime) from tablename group by username order by createtime 列名 'employee_state.createtime' 在 ORDER BY 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。select distinct username from 表 a where createtime=(select max(createtime) from 表 where username=a.username) order by createtime 如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中我看大家还是最好去自己试一下吧。
group by username
order by username
from 表
order by createtime
select username,max(createtime)
from table
group by username
order by createtime
where createtime=(select max(createtime) from 表 where username=a.username)
order by createtime
from 表
order by createtime
where createtime=(select max(createtime) from 表 where username=a.username)
order by createtime
--测试数据
declare @表 table(username varchar(20),createtime datetime)
insert into @表
select 'sundy007','2004-01-10 22:59:09.000'
union all select 'sundy007','2004-01-11 00:55:43.000'
union all select 'sundy007','2004-01-11 02:17:54.000'
union all select 'three','2004-01-11 02:20:52.000'
union all select 'yishanjushi','2004-01-11 03:00:46.000'
union all select 'lll','2004-01-12 01:48:36.000'
union all select 'lll','2004-01-16 04:23:59.000'--查询
select distinct * from @表 a
where createtime=(select max(createtime) from @表 where username=a.username)
order by createtime/*--测试结果
username createtime
-------------------- -----------------------------
sundy007 2004-01-11 02:17:54.000
three 2004-01-11 02:20:52.000
yishanjushi 2004-01-11 03:00:46.000
lll 2004-01-16 04:23:59.000(所影响的行数为 4 行)
--*/
select distinct xinghao,max(dd) as dtime from product group by xinghao order by dtime asc
不过上面好几个都同不过,如:select username,max(createtime) from tablename group by username order by createtime
列名 'employee_state.createtime' 在 ORDER BY 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。select distinct username from 表 a
where createtime=(select max(createtime) from 表 where username=a.username)
order by createtime
如果指定了 SELECT DISTINCT,那么 ORDER BY 子句中的项就必须出现在选择列表中我看大家还是最好去自己试一下吧。