mysql - 如何构成子查询

Question

我想为此我需要一个子查询，虽然我已经阅读了什么子查询，但我没有找到关于如何编写子查询的帮助。我有兴趣学习如何钓鱼，但我也想尽快钓到鱼，拜托:)

简单，1张数据表:姓氏，(找到或未找到 bool 值)

我想在整个字母表中生成一些已找到的统计数据。

Desired results:
A : 5 of 16 found, or about 31 percent
B : 2 of 4 found, or about 50 percent
C : 30 of 90 found, or about 30 percent
etc

我可以构建简单的 SQL，如果这里需要的话，我需要帮助构建子查询。

我可以写一个查询来列出有多少是通过姓氏的第一个字母找到的:

select substring(lastname,1,1) as lastinitial, count(*) from members where found !=0 and found is not null group by lastinitial;

我可以写一个查询来列出总共有多少，按最后一个首字母:

select substring(lastname,1,1) as lastinitial, count(*) from members group by lastinitial;

但是我如何组合这两个查询来产生所需的结果呢？感谢您的帮助。

Answer 1

您可能不需要子查询。分组可以为您提供每个名称的已找到和未找到。只需将“找到”添加到分组中，您将获得每个名称的两条记录，一条是找到的，另一条是未找到的。您也不需要再次查询总数，只需将找到的和未找到的相加即可。

SELECT SUBSTRING(lastname,1,1) AS lastinitial,
       (CASE WHEN found = 1 THEN 1 ELSE 0 END) AS found_val,
       COUNT(lastname) AS found_count
FROM members
GROUP BY lastinitial, found_val;

如果您希望每个字母都在一行中同时找到找到的和未找到的，请尝试以下操作:

SELECT found_list.lastinitial, found_count, not_found_count
FROM (
    SELECT SUBSTRING(lastname,1,1) AS lastinitial, COUNT(lastname) AS found_count
    FROM members
    WHERE found = 1
    GROUP BY lastinitial
) AS found_list,
(
    SELECT SUBSTRING(lastname,1,1) AS lastinitial, COUNT(lastname) AS not_found_count
    FROM members
    WHERE found IS NULL OR found = 0
    GROUP BY lastinitial
) AS not_found_list
WHERE found_list.lastinitial = not_found_list.lastinitial

如您所见，第一个查询更短、更优雅，而且执行速度更快。