SQL查询以显示组的count(*)，同时保留组的所有行 [英] Sql query to show count(*) of groups while keeping all rows of the group

问题描述

我有一个类似于以下表格

I have a table similar the following

id  user_id     father_id
1   1              1
2   2              1
3   3              2
4   4              2
5   5              2
6   6              3
7   7              4

我搜索一个sql查询(对于Laravel 4，更喜欢Fluent或Eloquent)会给我以下结果:

I search for a sql query (prefer Fluent or Eloquent for Laravel 4) to give me the following result:

id  user_id     father_id   family_members
3   3              2             3
4   4              2             3
5   5              2             3
1   1              1             2
2   2              1             2
6   6              3             1
7   7              4             1

可以看出， family_members 是具有相同 father_id

As it can be observed family_members is the count of users who have the same father_id

select id, user_id, father_id, count(*) as family_members from users group by father_id

上面的查询仅保留每个组的第一行，但我想保留所有其他记录，而不仅是第一个；然后首先根据 family_members 对它们进行排序，然后根据 father_id

The query above, just keeps the top row of each group, but I want to keep all the other records and not only the first one; then sorting them first according to the family_members and then according to father_id

我该如何实现?

推荐答案

我不懂Fluent，Eloquent，Laravel 4，但是sql查询就是这样.

I don´t know Fluent, nor Eloquent, nor Laravel 4, but the sql query would be like this.

 SELECT yourTable.*, auxTable.family_members  
   FROM yourTable
    LEFT JOIN
    (
        SELECT father_id, COUNT(id) as family_members
        FROM yourTable
        GROUP BY father_id
    )auxTable ON yourTable.father_id = auxTable.father_id
ORDER BY family_members DESC, yourTable.father_id ASC

这篇关于SQL查询以显示组的count(*)，同时保留组的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！