表a两字段,id,points,其中id为主键
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
-------------------
请帮忙?
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
-------------------
请帮忙?
from (select id,max(points) points from tb1 group by id) a
join tb2 b
on a.id=b.id
order by a.points desc
select b.*,a.points from b inner jion (select max(points) points from a group by id) a on a.id=b.id order by a.id desc
on a.id=b.id order by id desc
group by id ,name, phone
select a.id , b.name , b.phone , max(a.points)
from a
inner join b
on a.id = b.id
group by a.id
insert into a
select 1,1 union all
select 1,3 union all
select 2,1 union all
select 2,4 gocreate table b(id int,name varchar(10),phone varchar(10))
insert into b
select 1, 'peter', '8001' union all
select 2, 'merry', '8002' go
select a.id,max(b.name),max(b.phone),max(a.points) from a left join b on a.id=b.id group by a.id/*
id name phone points
1 peter 8001 3
2 merry 8002 4
*/
drop table a,b
insert into a
select 1,1 union all
select 1,3 union all
select 2,1 union all
select 2,4 gocreate table b(id int,name varchar(10),phone varchar(10))
insert into b
select 1, 'peter', '8001' union all
select 2, 'merry', '8002' go
select a.id,b.name,b.phone,a.points from (select id,max(points) points from a group by id ) a left join b on a.id=b.id /*
id name phone points
-----------------------------------
1 peter 8001 3
2 merry 8002 4
*/
drop table a,b
(
id decimal(18,0),
points bigint
)declare @table2 table
(
id decimal(18,0),
name nvarchar(200),
phone bigint
)
insert @table1 select'1', '1'
union select '1' , '3'
union select '2' , '1'
union select'2' , '4 '
insert @table2 select '1' , 'peter' , '8001'
union select '2' , 'merry' , '8002'
--select * from @table1
--select * from @table2select t1.id,max(t2.name),max(t2.phone),max(t1.points) from @table1 as t1 left join @table2 as t2 on t1.id=t2.id group by t1.id
declare @table1 table
(
id decimal(18,0),
points bigint
)declare @table2 table
(
id decimal(18,0),
name nvarchar(200),
phone bigint
)
insert @table1 select'1', '1'
union select '1' , '3'
union select '2' , '1'
union select'2' , '4 '
insert @table2 select '1' , 'peter' , '8001'
union select '2' , 'merry' , '8002'
select t2.id ,t2.name,t2.phone,t1.points from @table2 as t2 inner join @table1 as t1 on t1.id=t2.id
where t1.points in (select max(points) as points from @table1 group by id )