MySQL技术问询：如何找出仅提交1份申请的申请人姓名

Hey there! Since you're new to MySQL, let's walk through what's wrong with your current query and how to fix it step by step.

What's Wrong With Your Current Code? #

Your subquery:

SELECT COUNT(anumber) AS count FROM APPLIES GROUP BY anumber HAVING (count = 1)

does correctly spot applicants who submitted exactly one application—but it only returns the count of applications, not the actual anumber (application ID) needed to link back to the APPLICANT table. Without that anumber, your outer query has no way to connect the count to a specific applicant's name.

Correct Query Options #

Here are two straightforward, working ways to get the result you need:

Option 1: Use a JOIN (Great for Clarity)

First, fetch the list of anumbers linked to only one application, then join that list to the APPLICANT table to pull in the corresponding names:

SELECT a.lname, a.fname
FROM APPLICANT a
JOIN (
    -- Subquery to isolate applicants with exactly 1 application
    SELECT anumber
    FROM APPLIES
    GROUP BY anumber
    HAVING COUNT(*) = 1
) AS single_apps ON a.anumber = single_apps.anumber;

• COUNT(*) counts all rows in each group, which is more reliable here than COUNT(anumber) (it works even if anumber had NULL values, though that's unlikely for an ID field).
• We alias the subquery result as single_apps and link it to APPLICANT using the shared anumber field—this is the critical connection your original query was missing.

Option 2: Use an IN Subquery (More Concise)

If you prefer a shorter syntax, you can filter the APPLICANT table directly using a subquery in the WHERE clause:

SELECT lname, fname
FROM APPLICANT
WHERE anumber IN (
    SELECT anumber
    FROM APPLIES
    GROUP BY anumber
    HAVING COUNT(*) = 1
);

This works by first getting all anumbers tied to one application, then selecting only those matching applicants from the APPLICANT table.

Key Takeaway #

Always make sure your subqueries return the fields needed to link to other tables (in this case, anumber). Without that connection, the database can't map application counts to actual people!

内容的提问来源于stack exchange，提问作者Tyrionus