MySQL查询两个Workspace ID间的唯一漏洞：跨版本对比需求

Alright, let's figure out how to get those unique vulnerabilities between two workspaces. Here's a breakdown of the approach based on your table setup:

Step 1: Clarify the Table Structures #

First, let's align on your table definitions (I'll write sample DDL for clarity):

-- Table1: Maps Workspace ID to Version Name (Workspace is the primary key)
CREATE TABLE Table1 (
    Workspace INT PRIMARY KEY,
    VersionName VARCHAR(50) NOT NULL
);

-- Table2: Maps Workspace ID to Vulnerabilities (Workspace is a regular column, not a PK)
CREATE TABLE Table2 (
    Workspace INT NOT NULL,
    Vulnerability VARCHAR(50) NOT NULL
);

Step 2: Query for Unique Vulnerabilities Between Two Workspaces #

You have two solid options here, depending on readability and future scalability:

Option 1: Use EXCEPT + UNION ALL (Intuitive for Two Workspaces)

This method explicitly finds vulnerabilities that exist in one workspace but not the other, then combines the two sets of results:

-- Get vulnerabilities only present in Workspace 101
SELECT Vulnerability
FROM Table2
WHERE Workspace = 101
EXCEPT
SELECT Vulnerability
FROM Table2
WHERE Workspace = 102

UNION ALL

-- Get vulnerabilities only present in Workspace 102
SELECT Vulnerability
FROM Table2
WHERE Workspace = 102
EXCEPT
SELECT Vulnerability
FROM Table2
WHERE Workspace = 101;

Option 2: Use GROUP BY + HAVING (Scalable for More Than Two Workspaces)

If you ever need to compare more than two workspaces later, this approach is more flexible. It filters down to vulnerabilities that appear in exactly one of your target workspaces:

SELECT Vulnerability
FROM Table2
WHERE Workspace IN (101, 102) -- Replace with your target workspace IDs
GROUP BY Vulnerability
HAVING COUNT(DISTINCT Workspace) = 1;

The DISTINCT in the count handles cases where the same vulnerability might be listed multiple times for the same workspace in Table2.

Step 3: Add Version Names (Optional) #

If you want to tie the unique vulnerabilities back to their corresponding version names from Table1, just join the results with Table1:

SELECT
    t1.VersionName,
    unique_vulns.Vulnerability
FROM (
    -- Subquery gets unique vulnerabilities and their associated workspace
    SELECT Workspace, Vulnerability
    FROM Table2
    WHERE Workspace IN (101, 102)
    GROUP BY Workspace, Vulnerability
    HAVING COUNT(DISTINCT Workspace) = 1
) AS unique_vulns
JOIN Table1 t1 ON unique_vulns.Workspace = t1.Workspace;

Example Output #

Suppose Table2 has these records:

Both core queries will return:

And the version-included query would return:

That should give you exactly the unique vulnerabilities you're looking for!

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