mysql - 子查询统计MySQL中的字符串

Question

LEFT JOIN
(
    SELECT user_id, review, COUNT(user_id) totalCount
    FROM reviews
    GROUP BY user_id
) b ON b.user_id= b.user_id 

我正在尝试将 WHERE LENGTH(review) > 100 放入某个地方，但每次我放它都会给我带来问题。

上面的子查询按 user_id 计算所有总评论。我只是想再加一个资格。只计算长度超过 100 的评论。

附带说明一下，我看到了函数 CHAR_LENGTH —— 不确定我是否需要它。

编辑:

这里是完整的查询，可以完美地满足我的需求:

    static public $top_users = "
            SELECT  u.username, u.score,
            (COALESCE(a.totalCount, 0) * 4) +
            (COALESCE(b.totalCount, 0) * 5) +
            (COALESCE(c.totalCount, 0) * 1)  +
            (COALESCE(d.totalCount, 0) * 2)  +
            (COALESCE(u.friend_points, 0)) AS totalScore
            FROM users u 
            LEFT JOIN
                (
                    SELECT user_id, COUNT(user_id) totalCount
                    FROM items 
                    GROUP BY user_id
                ) a ON a.user_id= u.user_id
            LEFT JOIN
                (
                    SELECT user_id, COUNT(user_id) totalCount
                    FROM reviews
                    GROUP BY user_id
                ) b ON b.user_id= u.user_id
            LEFT JOIN
                (
                    SELECT user_id, COUNT(user_id) totalCount
                    FROM ratings
                    GROUP BY user_id
                ) c ON c.user_id = u.user_id
             LEFT JOIN
                (
                    SELECT user_id, COUNT(user_id) totalCount
                    FROM comments
                    GROUP BY user_id
                ) d ON d.user_id = u.user_id

                ORDER BY totalScore DESC LIMIT 25;";

Answer 1

LENGTH() 返回字符串的长度(以字节为单位)。您可能需要 CHAR_LENGTH()，因为它会给您实际的字符。

SELECT user_id, review, COUNT(user_id) totalCount
FROM reviews
WHERE CHAR_LENGTH(review) > 100
GROUP BY user_id, review

您也没有正确使用 GROUP BY。

See the documentation