各位大大~
表格DB1
name--eng---math
a. 70 60
a. 80 80
a. 70 75
b. 80 90
b. 85 90
b. 70 70 有重複的name,選eng高分跟math高分
最後剩
a. 80 80
b. 85 90 小弟只會一個條件篩選,兩個就不會
請大大幫忙 我的語法
select db1.* from db1,(select gene_name,max(eng) as eng from db1 group by name) B where db1.name=B.name and db1.eng=B.eng
表格DB1
name--eng---math
a. 70 60
a. 80 80
a. 70 75
b. 80 90
b. 85 90
b. 70 70 有重複的name,選eng高分跟math高分
最後剩
a. 80 80
b. 85 90 小弟只會一個條件篩選,兩個就不會
請大大幫忙 我的語法
select db1.* from db1,(select gene_name,max(eng) as eng from db1 group by name) B where db1.name=B.name and db1.eng=B.eng
from tb
group by name
?
name,max(eng) as eng,max(math) as math
from
tb
group by
name
insert into tb values('a' , 70 , 60 )
insert into tb values('a' , 80 , 80 )
insert into tb values('a' , 70 , 75 )
insert into tb values('b' , 80 , 90 )
insert into tb values('b' , 85 , 90 )
insert into tb values('b' , 70 , 70 )
go--1
select name , max(eng) eng, max(math) math from tb group by name
/*
name eng math
---------- ----------- -----------
a 80 80
b 85 90(所影响的行数为 2 行)
*/--2
select name,
eng = (select top 1 eng from tb where name = t.name order by eng desc),
math = (select top 1 math from tb where name = t.name order by math desc)
from tb t
group by name
/*
name eng math
---------- ----------- -----------
a 80 80
b 85 90(所影响的行数为 2 行)
*/drop table tb