在SQL/PHP中按多个列分组，其中两个列都可以包含相同的数据 [英] group by multiple columns in SQL/PHP, where both columns can include the same data

问题描述

我在php/mySQL中遇到了一个挑战，我找不到其他示例，并且证明了一点挑战.

I have a challenge in php/mySQL, that I cant find any other examples of, and is proving a bit of a challenge.

我有一个包含类别"和子类别"列的表.两者都可以包含相同的数据，也可以为空白，例如:

I have a table that has "category" and a "subcategory" columns. Both could contain the same data, or could be blank, eg:

--------------------------
| category | subcategory |
--------------------------
|   blue   |             |
|   blue   |    green    |
|  green   |     red     |
|  yellow  |     red     |
|    red   |             |
--------------------------

我想对这些结果进行分组和过滤，以显示所有使用的类别的简单列表，例如:

I want to group and filter these results to show a simple list of all categories used, eg:

blue (2)
red (3)
green (2)
yellow (1)

我可以对一列进行分组和排序没问题，但是看不到一种方法来包括两列.

I can group and sort by one column no problem, but cant see a way how to do this including two columns.

SELECT category,subcategory FROM table GROUP BY category,subcategory

我在网上看到的所有示例都涉及按两列进行分组，其中各列不同并且包含不同的数据，例如:名字，姓氏，所以我不认为这是重复的！

All the examples I can see online involve grouping by two columns, where the columns are different and contain different data, eg: firstname, lastname, so I dont think this is a duplication!

在此先感谢您的任何想法或帮助！

Thanks in advance for any thought or assistance with this!

根据以下评论进行编辑，以将当前代码显示为@bang:

edited to show current code to @bang, as per comments below:

$query = "SELECT category, count(*) FROM ( SELECT category from ae_test UNION ALL SELECT subcategory as 'category' from ae_test ) as t GROUP BY category";
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {
extract($row);
echo "$category ($count)<br />\n";
}

推荐答案

然后尝试一下:

SELECT category, count(*)
FROM ( 
    SELECT category from table
    UNION ALL
    SELECT subcategory as 'category' from table
) as t GROUP BY category;

第一个选择获取您的类别，第二个选择获取子类别.

The first select get your category and the second your subcategory.

我使用UNION ALL来获取所有数据，即使它们是重复的.

I use UNION ALL to get all data even if they are duplicated.

在您的php中，请不要使用extract:

In your php please never use extract :

$query = "SELECT category, count(*) FROM ( SELECT category from ae_test UNION ALL SELECT subcategory as 'category' from ae_test ) as t GROUP BY category";
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {

     echo "$row['category'] ($row['count(*)'])<br />\n";
}

这篇关于在SQL/PHP中按多个列分组，其中两个列都可以包含相同的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！