php - 表格中每一列的频率列表

Question

我有一个看起来像这样的表:

r01 r02 r03 r04
1   2   X   X
1   2   X   1
X   1   2   1
X   2   2   2
1   2   1   X
1   1   1   2
1   X   1   1
1   2   X   1
2   2   X   2

我想为每个列(不是行)获取一个频率数组列表，类似于array_count_values()。喜欢

r01: 1 => 6, X => 2, 2 => 1
r02: 1 => 2, X => 1, 2 => 6
r03: 1 => 3, X => 4, 2 => 2
r04: 1 => 4, X => 2, 2 => 3

是否可以回答一个或几个 mysql 问题？我还没有想出和idea。我拥有的唯一解决方案是将所有数据获取到 PHP，然后拥有一个数据数组，并为每一行的相应计数器添加一个。

我可以有 100->20000 行。所以我想要一个 mysql 解决方案，它的扩展性比 PHP 解决方案更好。

-- 编辑

我正在显示一个简化的表格结构，但我想我需要显示完整的表格。

CREATE TABLE IF NOT EXISTS `tips_rows` (
  `row_id` int(11) NOT NULL,
  `r01` enum('1','X','2') NOT NULL,
  `r02` enum('1','X','2') NOT NULL,
  `r03` enum('1','X','2') NOT NULL,
  `r04` enum('1','X','2') NOT NULL,
  `r05` enum('1','X','2') NOT NULL,
  `r06` enum('1','X','2') NOT NULL,
  `r07` enum('1','X','2') NOT NULL,
  `r08` enum('1','X','2') NOT NULL,
  `r09` enum('1','X','2') NOT NULL,
  `r10` enum('1','X','2') NOT NULL,
  `r11` enum('1','X','2') NOT NULL,
  `r12` enum('1','X','2') NOT NULL,
  `r13` enum('1','X','2') NOT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

而 tips_rows 包含值的所有组合。 (160 万行)为此我有一个链接表连接用户到 tips_rows，“tips_rows_users”

因此，此链接表将为许多用户保存每个用户 100-10000 row_id 的数据集。

我找到了一个基于其他 stackoverflow 谷歌搜索的解决方案。也许不是最纯粹的查询，但它有效且速度很快。

 SELECT
    SUM(CASE WHEN r01 = '1' THEN 1 ELSE 0 END) AS r11,
    SUM(CASE WHEN r01 = 'X' THEN 1 ELSE 0 END) AS r1X,
    SUM(CASE WHEN r01 = '2' THEN 1 ELSE 0 END) AS r12,
    SUM(CASE WHEN r02 = '1' THEN 1 ELSE 0 END) AS r21,
    SUM(CASE WHEN r02 = 'X' THEN 1 ELSE 0 END) AS r2X,
    SUM(CASE WHEN r02 = '2' THEN 1 ELSE 0 END) AS r22,
    SUM(CASE WHEN r03 = '1' THEN 1 ELSE 0 END) AS r31,
    SUM(CASE WHEN r03 = 'X' THEN 1 ELSE 0 END) AS r3X,
    SUM(CASE WHEN r03 = '2' THEN 1 ELSE 0 END) AS r32,
    SUM(CASE WHEN r04 = '1' THEN 1 ELSE 0 END) AS r41,
    SUM(CASE WHEN r04 = 'X' THEN 1 ELSE 0 END) AS r4X,
    SUM(CASE WHEN r04 = '2' THEN 1 ELSE 0 END) AS r42,
    SUM(CASE WHEN r05 = '1' THEN 1 ELSE 0 END) AS r51,
    SUM(CASE WHEN r05 = 'X' THEN 1 ELSE 0 END) AS r5X,
    SUM(CASE WHEN r05 = '2' THEN 1 ELSE 0 END) AS r52,
    SUM(CASE WHEN r06 = '1' THEN 1 ELSE 0 END) AS r61,
    SUM(CASE WHEN r06 = 'X' THEN 1 ELSE 0 END) AS r6X,
    SUM(CASE WHEN r06 = '2' THEN 1 ELSE 0 END) AS r62,
    SUM(CASE WHEN r07 = '1' THEN 1 ELSE 0 END) AS r71,
    SUM(CASE WHEN r07 = 'X' THEN 1 ELSE 0 END) AS r7X,
    SUM(CASE WHEN r07 = '2' THEN 1 ELSE 0 END) AS r72,
    SUM(CASE WHEN r08 = '1' THEN 1 ELSE 0 END) AS r81,
    SUM(CASE WHEN r08 = 'X' THEN 1 ELSE 0 END) AS r8X,
    SUM(CASE WHEN r08 = '2' THEN 1 ELSE 0 END) AS r82,
    SUM(CASE WHEN r09 = '1' THEN 1 ELSE 0 END) AS r91,
    SUM(CASE WHEN r09 = 'X' THEN 1 ELSE 0 END) AS r9X,
    SUM(CASE WHEN r09 = '2' THEN 1 ELSE 0 END) AS r92,
    SUM(CASE WHEN r10 = '1' THEN 1 ELSE 0 END) AS r101,
    SUM(CASE WHEN r10 = 'X' THEN 1 ELSE 0 END) AS r10X,
    SUM(CASE WHEN r10 = '2' THEN 1 ELSE 0 END) AS r102,
    SUM(CASE WHEN r11 = '1' THEN 1 ELSE 0 END) AS r111,
    SUM(CASE WHEN r11 = 'X' THEN 1 ELSE 0 END) AS r11X,
    SUM(CASE WHEN r11 = '2' THEN 1 ELSE 0 END) AS r112,
    SUM(CASE WHEN r12 = '1' THEN 1 ELSE 0 END) AS r121,
    SUM(CASE WHEN r12 = 'X' THEN 1 ELSE 0 END) AS r12X,
    SUM(CASE WHEN r12 = '2' THEN 1 ELSE 0 END) AS r122,
    SUM(CASE WHEN r13 = '1' THEN 1 ELSE 0 END) AS r131,
    SUM(CASE WHEN r13 = 'X' THEN 1 ELSE 0 END) AS r13X,
    SUM(CASE WHEN r13 = '2' THEN 1 ELSE 0 END) AS r132
    FROM `tips_rows` AS r
    INNER JOIN tips_rows_users USING (row_id)
    WHERE user__id='{userid}'

这将给我一个结果行作为

r11 r1X r12 r21 r2X r22 r31 r3X r32 r41 r4X r42 r51 r5X r52 r61 r6X r62 r71 r7X r72 r81 r8X r82 r91 r9X r92 r101 r10X r102 r111 r11X r112 r121 r12X r122 r131 r13X r132
40  34  26  48  30  22  69  14  17  70  16  14  15  17  68  28  31  41  80  20  0   49  29  22  38  30  32  69   16   15   29   28   43   19   31   50   13   25   62

我可以在我的 php 模板文件中使用它。

Answer 1

试试这个:

Select 'R01', r01, count(1) from myTable group by r01
UNION 
Select 'R02', r02, count(1) from myTable group by r02 
UNION
Select 'R03', r03, count(1) from myTable group by r03 
UNION
Select 'R04', r04, count(1) from myTable group by r04