mysql - 从具有DISTINCT字段的表中选择字段

我有这种桌子

| user | parents | sons |

with

• parents and sons columuns which can be NULL.
• user column can contains duplicated

I write this query:

SELECT user, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
  FROM my_table
  ORDER BY (parentsEd + sonsEd) DESC

而且有效！
因此，它给重复用户提供了结果。
如何通过使用（parentsEd + sonsEd）DESC ORDER来拥有DISTINC用户？

最佳答案

在order子句前使用GROUP BY，如果要先将结果排序，然后将其分组，然后使用subselect

SELECT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table GROUP BY `user` ORDER BY (parentsEd + sonsEd) DESC

SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table  ORDER BY (parentsEd + sonsEd) DESC


SELECT a.* FROM (
SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table  ORDER BY (parentsEd + sonsEd) DESC ) a GROUP BY a.`user`

关于mysql - 从具有DISTINCT字段的表中选择字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18161732/