求SQL Server查询语句：根据指定角色名输出SID、角色及存在状态

Got it, let's tackle this. The goal is to pull every SID from your first table, their corresponding Role, and a flag showing if that SID is assigned to the 'Admin' RoleName in the second table. Here's how to make it work:

Assumptions #

First, let's use meaningful names for your tables (replace these with your actual table names if they differ):

• UserRoles: Your first table with columns SID and Role (holds data like 1--A, 2--B)
• RoleAssignments: Your second table with columns RoleName and SID (holds data like Admin--1, Admin--4)

The Query #

SELECT
    ur.SID,
    ur.Role,
    CASE
        WHEN ra.SID IS NOT NULL THEN 'true'
        ELSE 'false'
    END AS [Exists]
FROM
    UserRoles ur
LEFT JOIN
    RoleAssignments ra ON ur.SID = ra.SID AND ra.RoleName = 'Admin'
ORDER BY
    ur.SID;

Breakdown of How It Works #

1. LEFT JOIN: This keeps every row from UserRoles (your first table), even if there's no matching 'Admin' entry in RoleAssignments. That’s how we ensure we get all 5 SIDs in the result.
2. Filter in the JOIN Clause: Adding ra.RoleName = 'Admin' directly to the ON clause means we only join rows where the role is Admin. If we put this in a WHERE clause instead, it would filter out rows with no Admin match—something we don’t want here.
3. CASE Statement: Checks if the joined SID from RoleAssignments exists (isn’t NULL). If it does, returns 'true'; otherwise, 'false'—exactly the flag you need.
4. ORDER BY: Sorts the results by SID to match the output order you specified.

Example Output #

Running this query will give you exactly the format you asked for:

SID | Role | Exists
----|------|-------
1   | A    | true
2   | B    | false
3   | C    | false
4   | D    | true
5   | E    | false

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