group by后对数据进行统计出现两次以上的次数

Question

问题描述

因实际表的内容比较复杂，所以我用下面的表进行代替，但是遇到的难点是一致的。

相关代码

// 请把代码文本粘贴到下方（请勿用图片代替代码）
表的结构如下

create table t_type(
    id int primary key auto_increment,
    type_code TINYINT,
    type_name VARCHAR(22),
    view_user int
);

id type_code type_name view_user
1      1            TV          100
2      2            VIDEO      100
3      3            PIC          100
4      4            NOTE      100
5      1            TV          200
6      2            VIDEO      200
7      1            TV          100
8      2            VIDEO      200

你期待的结果是什么？实际看到的错误信息又是什么？

我想要的结果应该是根据type_code进行分组，然后查询出对应类型的 总的人数，去重后的人数和重复的人数。

以下是我自己尝试的sql：

SELECT
    count( a.view_user ) AS userCount,
    count( DISTINCT a.view_user ) AS realUserCount,
    (
    SELECT
        count( 1 ) 
    FROM
        ( SELECT view_user FROM t_type b WHERE b.type_code = a.type_code GROUP BY view_user HAVING count( * ) > 1 ) tmp 
    ) AS repeatUserCount
FROM
    t_type a 
GROUP BY
    a.type_code;

但是不起作用：
1054 - Unknown column 'a.type_code' in 'where clause', Time: 0.000000s

大佬们请赐教，谢谢了。

Answer 1

子查询的子查询里面用不到最外层的字段，可以改为连表查询，下面是 SQL，不保证效率哈

SELECT
    a.type_code,
    a.userCount,
    a.realUserCount,
    ifnull(b.repeatUserCount, 0) AS repeatUserCount
FROM
    (
        SELECT
            type_code,
            count(view_user) AS userCount,
            count(DISTINCT view_user) AS realUserCount
        FROM
            t_type
        GROUP BY
            type_code
    ) a
LEFT JOIN (
    SELECT
        type_code,
        count(*) AS repeatUserCount
    FROM
        (
            SELECT
                a.type_code,
                a.view_user,
                count(*) AS total
            FROM
                t_type a
            GROUP BY
                a.type_code,
                a.view_user
            HAVING
                total > 1
        ) a
    GROUP BY
        type_code
) b ON a.type_code = b.type_code;

数据

+----+-----------+-----------+-----------+
| id | type_code | type_name | view_user |
+----+-----------+-----------+-----------+
|  1 |         1 | TV        |       100 |
|  2 |         2 | VIDEO     |       100 |
|  3 |         3 | PIC       |       100 |
|  4 |         4 | NOTE      |       100 |
|  5 |         1 | TV        |       200 |
|  6 |         2 | VIDEO     |       200 |
|  7 |         1 | TV        |       100 |
|  8 |         2 | VIDEO     |       200 |
|  9 |         1 | TV        |       200 |
+----+-----------+-----------+-----------+

结果

+-----------+-----------+---------------+-----------------+
| type_code | userCount | realUserCount | repeatUserCount |
+-----------+-----------+---------------+-----------------+
|         1 |         4 |             2 |               2 |
|         2 |         3 |             2 |               1 |
|         3 |         1 |             1 |               0 |
|         4 |         1 |             1 |               0 |
+-----------+-----------+---------------+-----------------+