2014-10-30 04:19 阅读 344


I am trying to achieve a strange query. I have a table like this:

table People

| id | name | family |
| 1  |  Bob |  Smith |
| 2  |  Joe | Smith  |

I want to return an assoc array like this

id : 1
name : bob
family : smith
familySize : 2

So something like this

"SELECT id, name, family, familySize FROM People"

How can I get the familySize in there? Keep in mind my query may have many families and I want them all to be returned.

Thank you.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    dongqian6554 dongqian6554 2014-10-30 04:26

    The above 2 answers won't work with multiple families.

    You can do something like this:

    SELECT id, name, family, familySize FROM People p1
    JOIN (SELECT COUNT(*) as familySize, family FROM People GROUP BY family) p2
    点赞 5 评论 复制链接分享
  • dongliao3742 dongliao3742 2014-10-30 04:24
         "SELECT id, name, family, count(family) as familySize FROM People group by   
    点赞 7 评论 复制链接分享
  • dscs63759 dscs63759 2014-10-30 04:23

    You can do it like this

    SELECT id, name, family, count(id) as familySize FROM People

    点赞 3 评论 复制链接分享
  • doudg60800 doudg60800 2014-10-30 04:36

    I would like to mention here that what if two or three families have the same surname "Smith". It won't give you the desired results.

    What I suggest you is while inserting the data in the table assign a family head and all the family members have a field called family_ID has primary key as value of family_head.

    This will give you accurate results with 1000's of people. I have made a family portal has over 12k members now and it works fine for me.

    Hope it helps.

    点赞 1 评论 复制链接分享