查找每列中最常见的值

Below is what I have in table myTable.

下面是我在表myTable中的内容。

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

I want output as

我希望输出为

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

value1=1 because 1 is thrice in that column.

value1 = 1,因为该列中的1是三次。

value2=2 because 2 is thrice in that column.

value2 = 2,因为2在该列中是三次。

value3=3 because 3 is thrice in that column.

value3 = 3,因为该列中的3是三次。

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

value4 = 4,因为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.

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

Can I achieve this in mysql?

我可以在mysql中实现这一点吗?

Update 1

Table at SQL Fiddle

Update 2

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

我也在这里列出答案,所以不需要继续js-fiddle。 (根据@ypercube的回答)

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
;

2 个解决方案

#1

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-Fiddle: test-2

#2

-1

This probably is not an ideal way to store data.
You might want to consider to use key-value pair to normalise 4 columns.
(that's mean one row for each column)

这可能不是存储数据的理想方式。您可能需要考虑使用键值对来标准化4列。 (这意味着每列一行)

(You can use UNION to achieve what you are looking for)

(你可以使用UNION来实现你想要的)

(select value1, null, null, null from TABLE group by value1 order by count(*) desc, value1 desc limit 1)
union
(select null, value2, null, null from TABLE group by value2 order by count(*) desc, value2 desc limit 1)
union
(select null, null, value3, null from TABLE group by value3 order by count(*) desc, value3 desc limit 1)
union
(select null, null, null, value4 from TABLE group by value4 order by count(*) desc, value4 desc limit 1)

#1

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
;