SQL查询需求：获取邮箱别名域名数量与限额信息

Let's break down your problem and refactor your query to be cleaner and more maintainable. First, let's restate the core requirements to align:

• Count how many aliases a user has per domain (e.g., @random.com)
• For each domain, get the maximum limit from all groups the user belongs to (0 if no group has a limit for that domain)
• Include all domains the user uses (even without limits) and all domains their groups have limits for (even if the user hasn't used any aliases there)

Optimized Query #

SELECT 
    COALESCE(ea.quantity, 0) AS quantity,
    COALESCE(ea.alias_domain, dl.alias_domain) AS alias_domain,
    COALESCE(dl.limit, 0) AS limit
FROM (
    -- Subquery 1: Get count of aliases per domain for the target user
    SELECT 
        REGEXP_SUBSTR(email_alias, '@.+') AS alias_domain,
        COUNT(*) AS quantity
    FROM email_alias
    WHERE person_id = :PERSON_ID
    GROUP BY REGEXP_SUBSTR(email_alias, '@.+')
) ea
FULL OUTER JOIN (
    -- Subquery 2: Get maximum limit per domain from user's groups
    SELECT 
        dl.alias_domain,
        MAX(dl.limit) AS limit
    FROM person p
    JOIN "group" g ON p.internal_id = g.internal_id
    JOIN domain_limit dl ON g.group_id = dl.group_id
    WHERE p.person_id = :PERSON_ID
    GROUP BY dl.alias_domain
) dl ON ea.alias_domain = dl.alias_domain
ORDER BY alias_domain;

Why This Works Better #

1. No nested unions or double grouping: We split the logic into two clear subqueries instead of combining them with UNION and re-grouping, making the code easier to read and debug.
2. Full outer join coverage: Ensures we capture both scenarios:
   • Domains the user has aliases for (even if no group limit exists)
   • Domains the user's groups have limits for (even if the user hasn't created any aliases there)
3. Clean null handling: COALESCE fills in missing values (0 for unused domains, 0 for domains without limits) to match your desired output format perfectly.

Example Output for User 012345678 #

Using your sample data, this query would return:

(Note: This aligns with your sample data—your original expected output might have been a typo, as user 012345678 belongs to retiree which has a @school.edu limit of 5, not 2.)

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