在每一列中查找最常见的值 [英] Finding most common values in each column

问题描述

下面是表myTable中的内容.

+--------+--------+--------+--------+
| value1 | value2 | value3 | value4 |
+--------+--------+--------+--------+
|   9    |   4    |   3    |   3    |
|   1    |   2    |   9    |   3    |
|   1    |   2    |   3    |   4    |
|   1    |   2    |   3    |   4    |
+--------+--------+--------+--------+

我希望输出为

+--------+--------+--------+--------+
| value1 | value2 | value3 | value4 |
+--------+--------+--------+--------+
|   1    |   2    |   3    |   4    |
+--------+--------+--------+--------+

value1=1，因为该列中的1是三次.

value1=1 because 1 is thrice in that column.

value2=2因为该列中的2是三次.

value2=2 because 2 is thrice in that column.

value3=3，因为该列中的3是三次.

value3=3 because 3 is thrice in that column.

value4=4，因为该列中的4是两次，这是该列中所有公用数的最大值.

value4=4 because 4 is twice in that column and that is maximum number of all common number in that column.

注意:value4的最常见值为3和4.在输出中我应该得到4，因为4在3,4中最大.

NOTE : value4 has most common values as 3 and 4. In output I should get 4 as 4 is the greatest in 3,4.

我可以在mysql中实现吗?

Can I achieve this in mysql?

我也在这里列出了答案，因此无需继续使用js-fiddle. (按照@ypercube答案)

I am listing answer here also so that no need to go on js-fiddle. (as per @ypercube answer)

SELECT
  ( SELECT value1
    FROM myTable
    GROUP BY value1
    ORDER BY COUNT(*) DESC
           , value1 DESC
    LIMIT 1
  ) AS value1,

  ( SELECT value2
    FROM myTable
    GROUP BY value2
    ORDER BY COUNT(*) DESC
           , value2 DESC
    LIMIT 1
  ) AS value2,

  ( SELECT value3
    FROM myTable
    GROUP BY value3
    ORDER BY COUNT(*) DESC
           , value3 DESC
    LIMIT 1
  ) AS value3,

  ( SELECT value4
    FROM myTable
    GROUP BY value4
    ORDER BY COUNT(*) DESC
           , value4 DESC
    LIMIT 1
  ) AS value4
;

推荐答案

SELECT
  ( SELECT value1
    FROM myTable
    GROUP BY value1
    ORDER BY COUNT(*) DESC
           , value1 DESC
    LIMIT 1
  ) AS value1,

  ( SELECT value2
    FROM myTable
    GROUP BY value2
    ORDER BY COUNT(*) DESC
           , value2 DESC
    LIMIT 1
  ) AS value2,
...
  ( SELECT valueN
    FROM myTable
    GROUP BY valueN
    ORDER BY COUNT(*) DESC
           , valueN DESC
    LIMIT 1
  ) AS valueN
;

SQL小提琴:测试2

这篇关于在每一列中查找最常见的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！