MenuID OperationID MenuDesc MenuHref
A000601 1 AAA
A000601 3 AAA
A000601 4 AAA
B000701 2 BBB
B000701 3 BBB
C000301 2 CCC
D000402 3 DDD
要求:如MenuID相同的则取其OperationID最大的记录
------解决方案--------------------
select * from tablename a
where not exists (
select 1 from tablename
where MenuID=a.MenuID and OperationID> a.OperationID
)
------解决方案--------------------
select a.* from table a,(
select MenuID,max(OperationID) as OperationID from table
group by MenuID ) b
where a.MenuID=b.MenuID and a.OperationID=b.OperationID
------解决方案--------------------
or:
select * from tablename a
where OperationID=(
select top 1 OperationID from tablename
where MenuID=a.MenuID
order by OperationID desc
)
------解决方案--------------------
select a.* from 表 a,(select MenuID,mo=max(OperationID) from 表 group by MenuID) b where a.MenuID=b.MenuID and a.OperationID=b.mo
------解决方案--------------------
select * from 表 as T
where MenuID =(select min(MenuID) from 表 where MenuID=T.MenuID)
------解决方案--------------------
select * from 表 as T
where MenuID =(select max(MenuID) from 表 where MenuID=T.MenuID)