表a两字段,id,points,其中id为主键
id points
1 1
1 3
2 1
2 4
我要的结果为
不同id的最大points的记录列表
id points
2 4
1 3
--------------------------------
如何写?急昏头了,请帮忙。
id points
1 1
1 3
2 1
2 4
我要的结果为
不同id的最大points的记录列表
id points
2 4
1 3
--------------------------------
如何写?急昏头了,请帮忙。
select id,max(points) as points
from a
group by id
order by id
--或者
select id,max(points) as points
from a
group by id
order by id desc
select id,max(points) from tablename group by id
insert into da_table select 1, 1
insert into da_table select 1 , 3
insert into da_table select 2 , 1
insert into da_table select 2 , 4
select id,max(points)from da_table group by id
Create table da_table (id int, points int )
insert into da_table select 1, 1
insert into da_table select 1 , 3
insert into da_table select 2 , 1
insert into da_table select 2 , 4
select id,max(points)from da_table group by id
/*
1 3
2 4
*/
insert into da_table select 1, 1
insert into da_table select 1 , 3
insert into da_table select 2 , 1
insert into da_table select 2 , 4 --1:
select id,max(points) from da_table group by id--2:
select * from da_table a where not exists (select 1 from da_table where id=a.id and points>a.points)/*
id points
-------------------
1 3
2 4
*/drop table da_table
id points
1 1
1 3
2 1
2 4
忘了一件事,另外一个表b,字段为id,name,phone
id name phone
1 peter 8001
2 merry 8002
-------
要的结果为
id name phone points
2 merry 8002 4
1 peter 8001 3
-------------------
??
select id,max(points) from da_table group by id
declare @a table(id int, points int )
insert @a
select 1, 1 union all
select 1, 3 union all
select 2, 1 union all
select 2, 4 declare @b table(id int,name varchar(10),phone varchar(10) )
insert @b
select 2, 'merry', '8002' union all
select 1, 'peter', '8001'select a.id,b.name,b.phone,max(a.points) as points
from @a a left join @b b
on a.id=b.id
group by a.id,b.name,b.phone
order by a.id desc/* 结果id name phone points
----------------------
2 merry 8002 4
1 peter 8001 3 */