MySQL：计数、分组依据和排序依据：整个表数据？

Question

我有一个包含数据的表：

id   one    two    three   four     five    six
------------------------------------------------
1    12     32     2       5        34       13
2    43     12     3       33       22       17
3    11     31     3       15       13       13
4    43     12     52      73       29       19
5    3      2      2       3        9        9
6    4      1      3       7        2        19
-------------------------------------------------

所以我知道如何按一列进行计数、分组和排序，如下所示：

<代码> 选择一个，数（一） 来自表编号 以一为一组 按计数（一） desc 排序

上面的查询将为我们提供：

one     count(one) 
-----------------
43      2
3       1
4       1
11      1
12      1
-----------------

那么我如何在一个查询中获取所有列的数据？

像这样：

One  Count(one) Two Count(two)  Three   Count(three)    Four    Count(four)
--------------------------------------------------------------------------- 
43   2          12  2           3       3               73       1           
3    1          1   1           2       2               3        1           
4    1          2   1           52      1               5        1           
11   1          31  1           null    null            7        1          
12   1          32  1           null    null            15       1          
null null      null null        null    null            33       1          
---------------------------------------------------------------------------

现在有什么方法可以在一个 sql 查询中完成它吗？可能正在使用连接或内联视图或其他什么？或者这在单个查询中可能吗？

[更新]我想计算每列中的重复值并按降序排序。

[更新] 如果您想使用表数据：

CREATE TABLE IF NOT EXISTS `table_numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  `one` int(2) NOT NULL,
  `two` int(2) NOT NULL,  `three` int(2) NOT NULL,
  `four` int(2) NOT NULL,  `five` int(2) NOT NULL,
  `six` int(2) NOT NULL,  PRIMARY KEY (`id`),
  KEY `col_one` (`one`),  KEY `col_two` (`two`),
  KEY `col_three` (`three`),  KEY `col_four` (`four`),
  KEY `col_five` (`five`),  KEY `col_six` (`six`)
) ;

INSERT INTO `table_numbers` (`id`, `one`, `two`, `three`, `four`, `five`, `six`)
VALUES
(1, 12, 32, 2, 5, 34, 13),(2, 43, 12, 3, 33, 22, 17),(3, 11, 31, 3, 15, 13, 13),
(4, 43, 12, 52, 73, 29, 19),(5, 3, 2, 2, 3, 9, 9),(6, 4, 1, 3, 7, 2, 19);

提前致谢！

杰伊 :-)

Answer 1

在一个查询中获取列数的简单方法：

(select 'one' as col, one as item, count(one) as count from table_numbers group by one) UNION
(select 'two', two, count(two) from table_numbers group by two) UNION
(select 'three', three, count(three) from table_numbers group by three) UNION
(select 'four', four, count(four) from table_numbers group by four) UNION
(select 'five', five, count(five) from table_numbers group by five) UNION
(select 'six', six, count(six) from table_numbers group by six) UNION
ORDER BY col, count DESC

它要复杂得多如果您想在列中进行汇总（例如 3 列）：

SELECT tone.item as One, tone.count as `Count(one)`, ttwo.item as Two, ttwo.count as `Count(two)`, tthree.item as Three, tthree.count as `Count(three)` 
FROM 
  (SELECT @rownumtmp:=@rownumtmp+1 as rownum 
   FROM 
    (SELECT DISTINCT col, count 
     FROM (
      (select 'one' as col, one as item, count(one) as count from table_numbers group by one) UNION
      (select 'two', two, count(two) from table_numbers group by two) UNION
      (select 'three', three, count(three) from table_numbers group by three)) tmp) tmp2, 
    (SELECT @rownumtmp:=0) r) tmp2 LEFT OUTER JOIN 
  (SELECT @rownum1:=@rownum1+1 as rownum, item, count FROM (SELECT one as item, count(one) as count from  table_numbers group by one ORDER BY count DESC, one) d, (SELECT @rownum1:=0) r)  tone ON tmp2.rownum=tone.rownum LEFT OUTER JOIN
  (SELECT @rownum2:=@rownum2+1 as rownum, item, count FROM (SELECT two as item, count(two) as count from  table_numbers group by two ORDER BY count DESC, two) d, (SELECT @rownum2:=0) r)  ttwo ON tmp2.rownum=ttwo.rownum LEFT OUTER JOIN
  (SELECT @rownum3:=@rownum3+1 as rownum, item, count FROM (SELECT three as item, count(three) as count from  table_numbers group by three ORDER BY count DESC, three) d, (SELECT @rownum3:=0) r)  tthree ON tmp2.rownum=tthree.rownum 
WHERE tone.item IS NOT NULL OR ttwo.item IS NOT NULL OR tthree.item IS NOT NULL

上述查询的结果将如下所示：

One  Count(one) Two Count(two)  Three   Count(three)
-----------------------------------------------------   
43   2          12  2           3       3            
3    1          1   1           2       2       
4    1          2   1           52      1        
11   1          31  1           null    null            
12   1          32  1           null    null                
------------------------------------------------------

Answer 2

你碰巧是这个意思吗？：

select one, two, three, four, five, six, count(1) 
from table_numbers group by one, two, three, four, five, six
order by count(1) desc

你想找到重复的吗？

Answer 3

我怀疑在单个查询中执行此操作的可能性！
查询在执行时仅遍历表一次..在您的情况下它需要计数
不止一列。

Answer 4

你是这个意思吗？

SELECT
    one AS val, COUNT(*) AS c
FROM ( 
    SELECT one FROM table_numbers UNION ALL
    SELECT two FROM table_numbers UNION ALL
    SELECT three FROM table_numbers UNION ALL
    SELECT four FROM table_numbers UNION ALL
    SELECT five FROM table_numbers UNION ALL
    SELECT six FROM table_numbers 
) x GROUP BY one ORDER BY c DESC;

Answer 5

我认为可以做到的唯一方法是...将数据从表中导出为一长串整数。即一个平面文件并将数据作为一个字段导入回新表。编写 sql 语句将重复项外推到新字段并进行总计。执行同样的操作，直到字段中导入的数据为空。希望有帮助。