PostgreSQL中查询所有用户与权限的关联及未拥有状态

Got it, let's tackle this problem. You want to list every user paired with every privilege, and clearly mark whether each user actually has that privilege. Here's how to make it happen in PostgreSQL:

The SQL Query #

SELECT
    u.user_id,
    u.name AS user_name,
    p.privilege_id,
    p.name AS privilege_name,
    CASE
        WHEN up.user_id IS NOT NULL THEN 'Yes'
        ELSE 'No'
    END AS has_privilege
FROM
    users u
CROSS JOIN
    privileges p
LEFT JOIN
    user_privilege up 
        ON u.user_id = up.user_id 
        AND p.privilege_id = up.privilege_id
ORDER BY
    u.user_id, p.privilege_id;

Breakdown of How It Works #

• CROSS JOIN: This generates a Cartesian product of all users and all privileges. In plain terms, it creates every possible user-privilege pair—so even users with no privileges (like Paul in your data) will show up with every privilege option.
• LEFT JOIN to user_privilege: We link these generated pairs to your association table. If a user actually has the privilege, the up.user_id column will have a value; if not, it'll be NULL.
• CASE Statement: This translates the NULL check into a human-readable indicator. We use "Yes" when the association exists, "No" otherwise.

Sample Output #

Using your provided table data, this query will return the following result set:

Bonus: Boolean Output Option #

If you'd prefer a boolean (true/false) instead of "Yes"/"No", you can swap the CASE statement for this:

COALESCE((up.user_id IS NOT NULL)::boolean, false) AS has_privilege

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