我正在尝试选择userName和groupId重复的所有行,并且userId不是该userName / groupId组合的最大userId。到目前为止,这是我的代码:
select * from userTable u where exists (select * from userTable u1 where userName <> '' and userName is not null and u.userName = u1.userName and u.groupId = u1.groupId and u.userId <> max(u1.userId) group by userName, groupId having count(*) > 1) order by userName
但是,这一行:
and u.userId <> u1.max(userId)
给我一个错误。
什么是进行此查询的正确方法?
SELECT u.* FROM ( SELECT userName, groupId, MAX(userId) AS maxId FROM userTable GROUP BY userName, groupId HAVING COUNT(*) > 1 ) q JOIN userTable u ON u.userName = q.userName AND u.groupId = q.groupId AND u.userId <> q.maxId