mysql - 如何在额外列中显示值的计数

Question

假设我们有一个这样的表:

id  value
 1      a
 2      b
 3      a
 4      a
 5      b

这样查询

SELECT * , COUNT( * )
FROM test
GROUP BY value

给我们一个这样的表:

id  value COUNT(*)
 1      a       3
 2      b       2

这告诉我们表中有三个“a”和两个“b”。

问题是:是否可以进行一个查询(没有嵌套的 SELECT)，它会产生一个像这样的表

id  value count_in_col
 1      a            3
 2      b            2
 3      a            3
 4      a            3
 5      b            2

目标是避免折叠列并将整个列中的“值”元素的数量添加到每一行。

Answer 1

是的，仅使用单个 SELECT 关键字就可以返回指定的结果集。

 SELECT t.id
      , t.value
      , COUNT(DISTINCT u.id) AS count_in_col
   FROM mytable t
   JOIN mytable u
     ON u.value = t.value
  GROUP
     BY t.id

设置测试用例:

 CREATE TABLE `mytable` (`id` INT, `value` VARCHAR(1));
 INSERT INTO `mytable` VALUES (1,'a'), (2,'b'),(3,'a'),(4,'a'),(5,'b');

返回:

    id  value   count_in_col  
------  ------  --------------
     1  a                    3
     2  b                    2
     3  a                    3
     4  a                    3
     5  b                    2

注意:

这假设 id 在表中是唯一的，这将由主键或唯一键约束强制执行。

就性能而言，根据基数，索引 ... ON (value,id) 可能会提高性能。

这种方法(使用 JOIN 来匹配 value 列上的行)确实有可能产生非常大的中间结果集，如果有“很多”行匹配 >值(value)。例如，如果有 1,000 行 value='a'，则这些行的中间结果集将为 1,000*1,000 = 1,000,000 行。

添加谓词(在 ON 子句中)也可以提高性能，但会减少中间结果中的行数。

    ON u.value = t.value
   AND u.id >= t.id

(没有真正的魔法；“技巧”是使用 COUNT(DISTINCT id) 来避免相同的 id 值被多次计算。)