SQL中distinct与order by

最新推荐文章于 2025-05-12 23:24:08 发布

最新推荐文章于 2025-05-12 23:24:08 发布 · 280 阅读

文章标签：

#SQL

hibernate 专栏收录该内容

2 篇文章

订阅专栏

本文探讨了SQL查询中使用distinct关键字的正确方式，并通过具体例子展示了如何避免orderby与distinct之间的逻辑矛盾，确保查询结果准确无误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-----------------------------------------------------------
错误

select distinct ga.id 

from GeneralGranted pro, GeneralMember gm 
left join gm.department d left join gm.institute i left join i.type sys left join gm.university u left join pro.application ga left join pro.generalVariation var left join pro.generalVariation var2 left join pro.subtype so 

where gm.application.id = ga.id and gm.isDirector=1 

group by pro.id, pro.name, gm.member.id, gm.memberName, gm.university.id, gm.agencyName, so.name, ga.disciplineType, ga.year, ga.id, var.id 

having COUNT(var2.id) > 0  

order by pro.name asc, pro.id asc

-----------------------------------------------------------
去掉distinct正确

select ga.id 

from GeneralGranted pro, GeneralMember gm 
left join gm.department d left join gm.institute i left join i.type sys left join gm.university u left join pro.application ga left join pro.generalVariation var left join pro.generalVariation var2 left join pro.subtype so 

where gm.application.id = ga.id and gm.isDirector=1 

group by pro.id, pro.name, gm.member.id, gm.memberName, gm.university.id, gm.agencyName, so.name, ga.disciplineType, ga.year, ga.id, var.id 

having COUNT(var2.id) > 0  

order by pro.name asc, pro.id asc

-----------------------------------------------------------
加上select pro.name , pro.id正确

select distinct ga.id, pro.name , pro.id

from GeneralGranted pro, GeneralMember gm 
left join gm.department d left join gm.institute i left join i.type sys left join gm.university u left join pro.application ga left join pro.generalVariation var left join pro.generalVariation var2 left join pro.subtype so 

where gm.application.id = ga.id and gm.isDirector=1 

group by pro.id, pro.name, gm.member.id, gm.memberName, gm.university.id, gm.agencyName, so.name, ga.disciplineType, ga.year, ga.id, var.id 

having COUNT(var2.id) > 0  

order by pro.name asc, pro.id asc

错误原因在于order by与distinct的逻辑矛盾，distinct ga.id后order by pro.name , pro.id错误