mysql - SQL每天计数值返回错误

Question

我有这个数据集

dia valor valor_b
=== ===== =======
1    1     b1
1    2     b2
1    1     b3
1    2     b3
2    1     b2
2    3     b1
2    1     b1
2    1     b1
2    1     b2
1    3     b3
5    1     b4
5    3     b1

我需要计算每天出现 valor_b 的次数。并以这种结构显示:

valor dia_1 dia_2 dia_3 dia_4 dia_5
===== ===== ===== ===== ===== =====
1     2     4     0     0     1
2     2     0     0     0     0
3     1     1     0     0     1

我第一次这样做是为了显示 value_b

SELECT valor, 
  group_concat(DISTINCT IF(dia=1, valor_b, NULL)) dia_1,
  group_concat(DISTINCT IF(dia=2, valor_b, NULL)) dia_2,
  group_concat(DISTINCT IF(dia=3, valor_b, NULL)) dia_3,
  group_concat(DISTINCT IF(dia=4, valor_b, NULL)) dia_4,
  group_concat(DISTINCT IF(dia=5, valor_b, NULL)) dia_5
FROM test
GROUP BY valor;

但是现在计算 valor_b phpmyadmin 返回这个错误

1111 - Invalid use of group function

这是用过的句子

SELECT valor, 
      group_concat(DISTINCT IF(dia=1, count(valor_b), NULL)) dia_1,
      group_concat(DISTINCT IF(dia=2, count(valor_b), NULL)) dia_2,
      group_concat(DISTINCT IF(dia=3, count(valor_b), NULL)) dia_3,
      group_concat(DISTINCT IF(dia=4, count(valor_b), NULL)) dia_4,
      group_concat(DISTINCT IF(dia=5, count(valor_b), NULL)) dia_5
    FROM test
    GROUP BY valor;

Answer 1

我在您的结果中没有看到串联的字符串。那么为什么要使用 GROUP_CONCAT 呢？你不只是想要:

select
  valor,
  sum(dia = 1) as dia_1,
  sum(dia = 2) as dia_2,
  sum(dia = 3) as dia_3,
  sum(dia = 4) as dia_4,
  sum(dia = 5) as dia_5
from test
group by valor
order by valor;

这在 MySQL 中使用了 true = 1 和 false = 0。

我不确定 "I need counting how times appear valor_b per day" 应该是什么意思。计算 valor_b 不为空的记录？然后你必须将它添加到表达式中:

  sum(dia = 1 and valor_b is not null) as dia_1,

等等