MYSQL 分组和计数

Question

我有 4 个表:tblnames、tblcountry 和 tblcountry_link、tblgender。

tblcountry_link 将名称和国家/地区表连接在一起，因为名称可以有多个国家/地区。

表名:

名称 ID |名称 |性别ID

1. 亚瑟2
2. 艾米1
3. 奥布里1
4. 奥布里2

表国家

国家ID |国家 |国籍

1. 阿富汗阿富汗人
2. 阿根廷 阿根廷人

tblcountry_link

名称 ID |国家ID

1. 1 | 1
2. 1 | 2
3. 2 | 1

tbl性别

性别ID |性别

1. 1 |女
2. 2 |男

我想要一个国家/地区列表以及每个国家/地区或国籍的中性名称数量，并以我选择的开头字母 - 因此对于字母 A；

1. 阿富汗 (3)
2. 阿根廷 (5)...

我知道如何通过使用“姓名”列上的“计数”和“拥有”以及按国籍分组来查找男女皆宜的姓名列表。

    {SELECT co.Nationality, COUNT( * ) 
    FROM (
    SELECT n.Name, COUNT( n.Name ) AS Countname, SUBSTR( Name, 1, 1 ) AS firstletter, g.Gender, cl.CountryID, c.Country, c.Nationality
    FROM tblnames AS n
    INNER JOIN tblgender AS g ON n.GenderID = g.GenderID
    INNER JOIN tblcountry_link AS cl ON cl.NameID = n.NameID
    INNER JOIN tblcountry AS c ON cl.CountryID = c.CountryID
    WHERE n.name LIKE CONCAT(  'A',  '%' ) 
    AND c.Nationality =  'Afghan'
    GROUP BY n.Name
    HAVING Countname >1
    ) AS co

这给了我结果阿富汗 (3)

但是，如果我删除Where 子句的阿富汗部分，结果将不正确。我尝试对此查询进行各种调整以获得我想要的结果，但结果看起来从来都不正确。

我还可以获得两个性别的每个国家或国籍(或者如果我将其添加到Where 子句，则实际上是男性或女性)。

    {SELECT cl.CountryID, c.Country, COUNT( n.Name ) AS Countname
    FROM tblnames AS n
    INNER JOIN tblcountry_link AS cl ON cl.NameID = n.NameID
    INNER JOIN tblcountry AS c ON cl.CountryID = c.CountryID
    WHERE n.name LIKE CONCAT( 'A', '%' ) 
    GROUP BY c.Country}

国家ID |国家 |计数名称

1. AF |阿富汗 | 624
2. 增强现实 |阿根廷 | 28
3. 上午 |亚美尼亚 | 5...

但是，我很难按国籍列出男女通用名字的数量。

我尝试将第一个示例中的having子句与后者结合起来，但这不起作用。我尝试加入名称匹配的男性和女性的 2 个子查询，但我无法正确分组和计数。我还尝试使用第一个示例创建派生表，但我只能使用 1 列，而该表有 2 列。

感谢任何帮助。

Answer 1

试试这个。让我知道它是否有效。

SELECT M.Country, MaleCount, FemaleCount FROM 
(SELECT c.country, COUNT( n.Name ) AS MaleCount
    FROM tblnames AS n
    INNER JOIN tblcountry_link AS cl ON cl.NameID = n.NameID
    INNER JOIN tblcountry AS c ON cl.CountryID = c.CountryID
    WHERE n.name LIKE 'A%'
    AND GenderID = 2
    GROUP BY c.Country) AS M,
(SELECT c.country, COUNT( n.Name ) AS FemaleCount
        FROM tblnames AS n
        INNER JOIN tblcountry_link AS cl ON cl.NameID = n.NameID
        INNER JOIN tblcountry AS c ON cl.CountryID = c.CountryID
        WHERE n.name LIKE 'A%'
        AND GenderID = 1
        GROUP BY c.Country) AS F  
WHERE M.country = F.country

如果我做对了，你应该得到包含国家、男性数量和女性数量的结果集，其中名字以 A 开头。可能有更好的方法。如果我想到更好的方法，我会编辑。