SELECT * FROM [user] AS A WHERE id=(SELECT MAX(id) FROM [user] WHERE name=A.name)
selet id from user A where not exists ( select 1 from user B where A.name=B.name and A.datetime<B.datetime)
select *from [user] a where not exists(select 1 from [user] where name = a.name and id > a.id)
select * from [user] a where not exists(select 1 from [user] where name=a.name and datatime>a.datatime)
--按某一字段分组取最大(小)值所在行的数据 --(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州) /* 数据如下: name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --创建表并插入数据: create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values('a', 2, 'a2(a的第二个值)') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('b', 1, 'b1--b的第一个值') insert into tb values('b', 3, 'b3:b的第三个值') insert into tb values('b', 2, 'b2b2b2b2') insert into tb values('b', 4, 'b4b4') insert into tb values('b', 5, 'b5b5b5b5b5') go--一、按name分组取val最大的值所在行的数据。 --方法1: select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name --方法2: select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val) --方法3: select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4: select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name /* name val memo ---------- ----------- -------------------- a 3 a3:a的第三个值 b 5 b5b5b5b5b5 */--二、按name分组取val最小的值所在行的数据。 --方法1: select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name --方法2: select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val) --方法3: select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4: select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 */
弄过复杂一点的SELECT T.ID FROM TB T JOIN (SELECT MAX(ID)ID,NAME FROM TB GROUP BY NAME)AS T1 ON T.ID=T1.ID AND T.NAME=T1.NAME
create table test(id int,date datetime) insert into test values(1,'2009-02-03') insert into test values(1,'2009-03-03') insert into test values(2,'2009-02-03') insert into test values(2,'2009-01-03')select id,max(date) from test group by id
declare @t table(id int ,name varchar(5),datatime datetime) insert @t select 1,'ww','2009-02-03' union all select 2,'ww','2009-02-11' union all select 3,'dd' , '2009-02-04' union all select 4, 'dd' , '2009-02-07 'select id from @t a, (select name,max(datatime) time1 from @t group by name) m where a.name=m.name and a.datatime=m.time1id ----------- 2 4(2 行受影响)
FROM [user] AS A
WHERE id=(SELECT MAX(id) FROM [user] WHERE name=A.name)
from user A
where not exists ( select 1 from user B where A.name=B.name and A.datetime<B.datetime)
where not exists(select 1 from [user] where name = a.name and id > a.id)
select * from [user] a where not exists(select 1 from [user] where name=a.name and datatime>a.datatime)
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
比如说name为ww的取最在时间的id=2
JOIN
(SELECT MAX(ID)ID,NAME FROM TB GROUP BY NAME)AS T1
ON T.ID=T1.ID AND T.NAME=T1.NAME
insert into test values(1,'2009-02-03')
insert into test values(1,'2009-03-03')
insert into test values(2,'2009-02-03')
insert into test values(2,'2009-01-03')select id,max(date) from test group by id
insert @t
select
1,'ww','2009-02-03' union all
select
2,'ww','2009-02-11' union all
select
3,'dd' , '2009-02-04' union all
select
4, 'dd' , '2009-02-07 'select id from @t a, (select name,max(datatime) time1 from @t group by name) m where a.name=m.name and a.datatime=m.time1id
-----------
2
4(2 行受影响)