使用group by，count，group by，alias执行高级sql查询

I need to execute this query it's like ( main cats and sub cats )

我需要执行这个查询就像(主要的猫和子猫)

Here is my schema:

这是我的架构:

++++++++++++++++++++++++++++++++
+  id | Country     |  Parent  |
++++++++++++++++++++++++++++++++
+  1  |  India      |          |
++++++++++++++++++++++++++++++++
+  2  |  Usa        |          |
++++++++++++++++++++++++++++++++
+  3  | California  |   Usa    |
++++++++++++++++++++++++++++++++
+  4  | New York    |   Usa    |
++++++++++++++++++++++++++++++++
+  5  | New Delhi   |  India   |
++++++++++++++++++++++++++++++++
+  6  | France      |          |
++++++++++++++++++++++++++++++++

And I want to get the result like this:

我希望得到这样的结果:

+++++++++++++++++++++++++++++++++++++++++++++++++
+  id | Country     | Num |  Childs             |
+++++++++++++++++++++++++++++++++++++++++++++++++
+  1  |  India      |  1  |  New Delhi          |
+++++++++++++++++++++++++++++++++++++++++++++++++
+  2  | Usa         |  2  | California,New York |
+++++++++++++++++++++++++++++++++++++++++++++++++
+  3  | France      |  0  |                     |
+++++++++++++++++++++++++++++++++++++++++++++++++

2 个解决方案

#1

Try this:

SELECT t1.id, t1.Country, 
    (SELECT COUNT(id) FROM world t2
     WHERE t2.Parent = t1.Country) as Num,
    (SELECT GROUP_CONCAT(Country) FROM world t3
     WHERE t3.Parent = t1.Country) as Childs
FROM world t1
WHERE t1.Parent IS NULL

HOW IT WORKS

My query is made of a main part and two subqueries (the ones between parenthesis); you can think of a subquery as a normal query executed in a main query returing to this some field (column). Subqueries can interact with main query because they can use main fields in SELECT or WHERE.

我的查询是由一个主要部分和两个子查询(括号之间的那些)组成的;您可以将子查询视为在主查询中执行的常规查询,该查询返回到某个字段(列)。子查询可以与主查询交互,因为它们可以使用SELECT或WHERE中的主要字段。

Main part gets all base countries (the ones without parent)

主要部分获得所有基地国家(没有父母的国家)

   SELECT t1.id, t1.Country
   FROM world t1
   WHERE t1.Parent IS NULL

First subquery gets (for every item in main part) the count of countries whose parent is main item

第一个子查询获取(对于主要部分中的每个项目)父项为主项目的国家/地区的计数

SELECT COUNT(id) FROM world t2
WHERE t2.Parent = t1.Country

Note that in WHERE clause I ask to match subquery id with parent id, so I get only the count of countries related to main item.
Second subquery is similar to first one, but a different field is returned using GROUP_CONCAT function to get all subcountries separated with commas.

请注意,在WHERE子句中,我要求将子查询ID与父ID匹配,因此我只获得与主项目相关的国家/地区的计数。第二个子查询与第​​一个子查询类似,但使用GROUP_CONCAT函数返回一个不同的字段,以便用逗号分隔所有子语句。

#2

Check out GROUP_CONCAT.

查看GROUP_CONCAT。