我有这样的表：
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问题吗？我还没有想出这个主意。我唯一的解决方案是将所有数据都放到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_rrows_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模板文件中使用。
试试这个：：
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
假设每列只有4列和3个可能的值，如果你真的希望你的结果显示为"每值一列"：
SELECT 'R01' AS 'Row', SUM(IF(r01 = 1, 1, 0)) as '1',
SUM(IF(r01 = 2, 1, 0)) as '2',
SUM(IF(r01 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R02', SUM(IF(r02 = 1, 1, 0)) as '1',
SUM(IF(r02 = 2, 1, 0)) as '2',
SUM(IF(r02 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R03', SUM(IF(r03 = 1, 1, 0)) as '1',
SUM(IF(r03 = 2, 1, 0)) as '2',
SUM(IF(r03 = 'X', 1, 0)) as 'X'
FROM myTable
UNION SELECT 'R04', SUM(IF(r04 = 1, 1, 0)) as '1',
SUM(IF(r04 = 2, 1, 0)) as '2',
SUM(IF(r04 = 'X', 1, 0)) as 'X'
FROM myTable
生产：
+------+----+----+---+
| ROW | 1 | 2 | X |
+------+----+----+---+
| R01 | 6 | 1 | 2 |
| R02 | 2 | 6 | 1 |
| R03 | 3 | 2 | 4 |
| R04 | 4 | 3 | 2 |
+------+----+----+---+
请参阅http://sqlfiddle.com/#！2/c6376/2
如果数据透视结果表是可以接受的，那么以下查询将执行更好：
SELECT n,
SUM(IF(r01 = n, 1, 0)) AS 'r01',
SUM(IF(r02 = n, 1, 0)) AS 'r02',
SUM(IF(r03 = n, 1, 0)) AS 'r03',
SUM(IF(r04 = n, 1, 0)) AS 'r04'
FROM mytable JOIN (SELECT '1' AS n UNION SELECT '2' UNION SELECT 'X') S
GROUP BY n;
结果：
+----+------+------+------+-----+
| | R01 | R02 | R03 | R04 |
+----+------+------+------+-----+
| 1 | 6 | 2 | 3 | 4 |
| 2 | 1 | 6 | 2 | 3 |
| X | 2 | 1 | 4 | 2 |
+----+------+------+------+-----+
感谢所有不同的解决方案。我现在已经对我的数据做了一些测试。我已经查询了10000行。以下是我的发现。
解决方案1:
Select 'R01', r01, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r01
UNION
Select 'R02', r02, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r02
UNION
Select 'R03', r03, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r03
UNION
Select 'R04', r04, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r04
UNION
Select 'R05', r05, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r05
UNION
Select 'R06', r06, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r06
UNION
Select 'R07', r07, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r07
UNION
Select 'R08', r08, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r08
UNION
Select 'R09', r09, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r09
UNION
Select 'R10', r10, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r10
UNION
Select 'R11', r11, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r11
UNION
Select 'R12', r12, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r12
UNION
Select 'R13', r13, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r13
每次查询大约需要0.1365秒
解决方案2:
SELECT n,
SUM(IF(r01 = n, 1, 0)) AS 'r01',
SUM(IF(r02 = n, 1, 0)) AS 'r02',
SUM(IF(r03 = n, 1, 0)) AS 'r03',
SUM(IF(r04 = n, 1, 0)) AS 'r04',
SUM(IF(r05 = n, 1, 0)) AS 'r05',
SUM(IF(r06 = n, 1, 0)) AS 'r06',
SUM(IF(r07 = n, 1, 0)) AS 'r07',
SUM(IF(r08 = n, 1, 0)) AS 'r08',
SUM(IF(r09 = n, 1, 0)) AS 'r09',
SUM(IF(r10 = n, 1, 0)) AS 'r10',
SUM(IF(r11 = n, 1, 0)) AS 'r11',
SUM(IF(r12 = n, 1, 0)) AS 'r12',
SUM(IF(r13 = n, 1, 0)) AS 'r13'
FROM `tips_rows` AS r
INNER JOIN tips_rows_users USING (row_id)
JOIN (SELECT '1' AS n UNION SELECT '2' UNION SELECT 'X') S
WHERE user_id='27'
GROUP BY n;
第一次查询大约需要0.0997秒。可以缓存在mysql查询缓存中，因此第二次时间大约需要0.0002s
解决方案3：
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='27'
第一次和第二次耗时约0.0587秒。在那之后0.0002秒的问题。
我将采用解决方案3。因为它有最好的最坏情况的时间。但奇怪的是，在缓存之前需要两个查询。