如何进行嵌套查询？

发布于 2024-08-19 07:56:23 字数 333 浏览 9 评论 0原文

有一个表 users，并且有一个字段 informed_by_id 显示邀请该用户的人的用户 ID。需要创建一个 MySQL 查询，返回包含用户所有字段的行以及显示每个用户邀请了多少人的 informs_count 字段。像这样的事情：

SELECT
    User.*, Count.count
FROM
    users AS User,
    (
        SELECT COUNT(*) AS count FROM users WHERE users.invited_by_id=User.id
    ) AS 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

箹锭⒈辈孓 2024-08-26 07:56:23

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

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

回复收藏 0 原文

冰雪之触 2024-08-26 07:56:23

好的，首先，count 是 sql 中的保留字，因此您不能将其用作表别名（除非您以某种方式引用它但不这样做）。其次，解决此问题的真正方法是在子查询中引入 GROUP BY 子句。

试试这个：

SELECT user3.*, subquery.theCount FROM
    users AS user3
INNER JOIN ( 
    SELECT
        user1.id, count(user2.id) AS theCount
    FROM
        users AS user1
    LEFT OUTER JOIN
        users AS user2 ON user2.invited_by_id=user1.id
    GROUP BY user1.id
) AS subquery ON subquery.id=user3.id;

这里有一个关于 MySQL 的肮脏小秘密：它允许您使用 GROUP BY 语句作弊，并选择不在 GROUP BY 列表中且不在聚合函数。其他 RMDMS 不允许您这样做。

SELECT
    user1.*, count(user2.id) AS theCount
FROM
    users AS user1
LEFT OUTER JOIN
    users AS user2 ON user2.invited_by_id=user1.id
GROUP BY user1.id;

Ok, first of all, count is a reserved word in sql so you can't use it as a table alias (unless you quote it in some way but don't do that). Secondly, the real way to solve this problem is to introduce a GROUP BY clause in your subquery.

Try this:

SELECT user3.*, subquery.theCount FROM
    users AS user3
INNER JOIN ( 
    SELECT
        user1.id, count(user2.id) AS theCount
    FROM
        users AS user1
    LEFT OUTER JOIN
        users AS user2 ON user2.invited_by_id=user1.id
    GROUP BY user1.id
) AS subquery ON subquery.id=user3.id;

Here is a dirty little secret about MySQL: It lets you cheat with the GROUP BY statement and select columns that are not in the GROUP BY list and also not in aggregate functions. Other RMDMSes don't let you do this.

SELECT
    user1.*, count(user2.id) AS theCount
FROM
    users AS user1
LEFT OUTER JOIN
    users AS user2 ON user2.invited_by_id=user1.id
GROUP BY user1.id;

回复收藏 0 原文

~没有更多了~