数据库的表(table1)类似下面的sfzh zkzh fs1
320321 123 12
320321 124 15
320322 125 13
320322 126 14 sfzh(身份证号),zkzh(准考证号),fs(分数)
要求取出 所有 相同身份证号下的分数的最大值
我取只能取出一条-_-
select sfzh,zkzh,fs1 from table1 where ?谢谢
320321 123 12
320321 124 15
320322 125 13
320322 126 14 sfzh(身份证号),zkzh(准考证号),fs(分数)
要求取出 所有 相同身份证号下的分数的最大值
我取只能取出一条-_-
select sfzh,zkzh,fs1 from table1 where ?谢谢
from table1 a
where zkzh=(
select top 1 zkzh from table1
where sfzh=a.sfzh
order by fs1 desc
)
where not exists(select 1 from table1 where fs>a.fs and sfzh=a.sfzh)
insert @t select '320321','123',12
insert @t select '320321','124',15
insert @t select '320322','125',13
insert @t select '320322','126',14select * from @t a
where not exists(select 1 from @t where fs>a.fs and sfzh=a.sfzh)------------------
sfzh zkzh fs
320321 124 15
320322 126 14
insert into table1 values('320321', '124', 15 )
insert into table1 values('320322', '125', 13)
insert into table1 values('320322' , '126' , 14)
select sfzh,zkzh,fs1
from table1 a
where not exists(select * from table1 where a.fs1<table1.fs1 and a.sfzh=table1.sfzh )
sfzh zkzh fs1
---------- ---------- -----------
320321 124 15
320322 126 14(所影响的行数为 2 行)