这是我的查询:
SELECT user.*, @UserID:=user.uid,
(SELECT count(c1) FROM (
SELECT COUNT(*) AS c1 FROM user_group
WHERE user_group.uid=@UserID
GROUP BY user_group.uid
) x1) AS x2
FROM user
问题在于子查询中未传递
@UserID
参数。这是我的查询:
SELECT user.*, @UserID:=user.uid,
(SELECT count(c1) FROM (
SELECT COUNT(*) AS c1 FROM user_group
WHERE user_group.uid=@UserID
GROUP BY user_group.uid
) x1) AS x2
FROM user
@UserID
参数。SELECT user.*,
(SELECT COUNT(*) AS c1 FROM user_group
WHERE user_group.uid=user.uid
GROUP BY user_group.uid) AS GroupCount
FROM user;
使用 JOIN
怎么样?我认为结果和您想要的一样。我猜测您想要获取用户所拥有的组的总数。对吗?
SELECT a.*,
COALESCE(b.totalCount, 0) totalCount,
COALESCE(b.totalType, 0) totalType
FROM user a
LEFT JOIN
(
SELECT uid,
COUNT(*) totalCount,
COUNT(DISTINCT type) totalType
FROM user_Group
GROUP BY uid
) b ON a.uid = b.uid