Sql嵌套组

Question

以下查询返回与性别=男性同名的人数。

select  lookup_name.firstname,count(lookup_name.firstname)
from lookup_name
where gender='M'
group by firstname

类似地，下面的查询返回多个具有相同名称的人，其性别为Female。

select  lookup_name.firstname,count(lookup_name.firstname)
from lookup_name
where gender='F'
group by firstname

我需要编写一个查询，以找出姓名，并以更大的数量告诉性别(无论是男性还是女性)。即数据库中该名称是男性还是女性的可能性更高？

Answer 1

SELECT firstname, Male, Female,
       case when Male=Female then 'indeterminate'
            when Male>Female then 'probably male'
            else 'probably female' end MostProbablySex
FROM (
    select firstname,
           SUM(case when gender='M' then 1 else 0 end) Male,
           SUM(case when gender='F' then 1 else 0 end) Female
    from lookup_name
    group by firstname
) X;

或单次通过:

select firstname,
       CASE SIGN(2.0 * SUM(case when gender='M' then 1 else 0 end) / COUNT(*) - 1)
       WHEN -1 then 'probably female'
       WHEN 0 then 'indeterminate'
       WHEN 1 then 'probably male'
       END
from lookup_name
group by firstname;