MySQL从表中选择不同的值,按具有这些不同值的行数排序

时间:2021-03-02 20:17:03

I'm currently working on a MySQL query, but I've been having a bit of trouble with it and am looking for help. I have been struggling with it for a while now and have searched for the solution from the Internet and from different coding forums, but, alas, still nothing.

我目前正在研究MySQL查询,但我遇到了一些麻烦,我正在寻求帮助。我一直在努力研究它并从互联网和不同的编码论坛寻找解决方案,但是,唉,仍然没有。

Basically, I have a MySQL table as follows:

基本上,我有一个MySQL表如下:

Table name: 'data'
'id' SMALLINT(5) auto_increment
'value_1' SMALLINT(5)
'value_2' SMALLINT(5)

The column 'value_2' has duplicate values and, so, I need to find the distinct values to process the results. So I created a MySQL query to do that.

列'value_2'具有重复值,因此,我需要找到不同的值来处理结果。所以我创建了一个MySQL查询来做到这一点。

SELECT DISTINCT value_1 FROM data WHERE value_2!="0"

The problem I'm having is that I want to order the results I just had by not the id nor by any other field, but by the number of rows that had those certain distinct values in that column ('value_1').

我遇到的问题是,我想订购我刚刚拥有的结果,而不是id或任何其他字段,而是通过在该列中具有那些特定不同值的行数('value_1')。

Could anyone please assist me?

有人可以帮助我吗?

2 个解决方案

#1


3  

What you'll need is GROUP BY and COUNT.

你需要的是GROUP BY和COUNT。

SELECT `value_1`, COUNT(`value_1`) AS `count` FROM `data` WHERE `value_2` != '0' GROUP BY `value_1` ORDER BY `count`;

For more information, see: http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html.

有关更多信息,请参阅:http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html。

#2


0  

SELECT DISTINCT value_1, COUNT(*) FROM data GROUP BY value_1 ORDER BY 2 DESC;

#1


3  

What you'll need is GROUP BY and COUNT.

你需要的是GROUP BY和COUNT。

SELECT `value_1`, COUNT(`value_1`) AS `count` FROM `data` WHERE `value_2` != '0' GROUP BY `value_1` ORDER BY `count`;

For more information, see: http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html.

有关更多信息,请参阅:http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html。

#2


0  

SELECT DISTINCT value_1, COUNT(*) FROM data GROUP BY value_1 ORDER BY 2 DESC;