如何进行嵌套查询? [英] How to make a nested query?

问题描述

有一个表用户，并且有一个字段Invitation_by_id显示邀请该用户的人的用户ID.需要进行MySQL查询，返回包含用户所有字段的行，以及显示每个用户邀请多少人的Invitations_count字段. 像这样:

Have a table users and there is a field invited_by_id showing user id of the person who invited this user. Need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user. Something like this:

SELECT
    User.*, Count.count
FROM
    users AS User,
    (
        SELECT COUNT(*) AS count FROM users WHERE users.invited_by_id=User.id
    ) AS Count;

这个没有用，所以我需要一个有用的.

This one is not working so I need a working one.

推荐答案

SELECT  u.*,
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   ui.invited_by_id = u.id
        ) AS cnt
FROM    users u    

这篇关于如何进行嵌套查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！