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
3
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
3
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)