MySQL技术问题：关联表行数为1时筛选web_users表数据

Got it, let's get this query working for you. Based on your description, you need to pull users from web_users who have exactly one matching row in web_users_branches—which should only return the user with client_code = '0002'.

Here are a few reliable approaches to achieve this:

Method 1: Subquery with Grouped Count #

This method first calculates how many branch entries each user has, then joins back to the main users table to filter for users with exactly 1 entry.

SELECT wu.*
FROM web_users wu
INNER JOIN (
    -- Count branch entries per user (adjust the grouping field if your join key is not client_code)
    SELECT client_code, COUNT(*) AS branch_count
    FROM web_users_branches
    GROUP BY client_code
    HAVING COUNT(*) = 1 -- Keep only users with exactly 1 branch entry
) branch_counts ON wu.client_code = branch_counts.client_code;

Why this works: #

• The subquery groups web_users_branches by client_code (or your actual user identifier like user_id) and counts entries per group.
• The HAVING clause filters groups down to only those with a count of 1.
• Joining this result back to web_users gives you the full user details for matching entries.

Method 2: Join + Group By + Having #

You can also join the two tables directly, then group by user and filter the count of branch entries:

SELECT wu.*
FROM web_users wu
INNER JOIN web_users_branches wub ON wu.client_code = wub.client_code
-- Group by all non-aggregated fields from web_users (adjust based on your database's GROUP BY rules)
GROUP BY wu.id, wu.client_code, wu.username -- add all other web_users columns here if needed
HAVING COUNT(wub.id) = 1;

Note: #

• If your database enforces strict GROUP BY rules (like PostgreSQL or MySQL with ONLY_FULL_GROUP_BY enabled), you need to include all columns from web_users that you're selecting in the GROUP BY clause, or just group by the primary key (e.g., wu.id) if your database supports that.

Common Issues That Cause Empty Results #

If your original query returned nothing, check these:

• Wrong join key: Ensure you're using the correct field to link web_users and web_users_branches (e.g., maybe it's user_id instead of client_code—adjust the queries above accordingly).
• Using WHERE instead of HAVING: You can't use COUNT(*) = 1 in a WHERE clause because it's an aggregate function—always use HAVING after GROUP BY to filter grouped results.
• Case sensitivity: If client_code is a string field, make sure your values (like '0002') match the case stored in the database.

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