PostgreSQL：如何按选定申请人筛选无重复职位（含未申请/无人申请职位）

Hey there! No worries at all as a first-time questioner—let's get this sorted out for you.

First, let's break down your core requirement clearly: you want all unique positions in your system, including:

• Positions the selected applicant hasn't applied for
• Positions that no one has applied for yet
• Positions the selected applicant (or others) have applied for (with no duplicates)

The reason your EXCEPT approach didn't work is because EXCEPT is designed to find differences between datasets, but we need to start from a full list of all possible positions (not just those present in the application table). Here's how to fix this in PostgreSQL:

Step 1: Set up a dedicated positions table (critical!) #

To include positions that have zero applications, you need a single source of truth for all possible positions. If you don't already have one, create it:

CREATE TABLE positions (
    position_id SERIAL PRIMARY KEY,
    position_name VARCHAR(100) UNIQUE NOT NULL
);

This table will store every position in your system—even if no one has applied to it yet.

Step 2: Query to fetch all unique positions #

Once you have the positions table, use a LEFT JOIN with your application table to get all positions, ensuring no duplicates. Even when targeting a specific applicant, we don't filter by them here because we want every position:

-- Replace @selected_applicant with your dynamic applicant ID/name
SELECT DISTINCT p.position_id, p.position_name
FROM positions p
LEFT JOIN application a ON p.position_id = a.position_id
ORDER BY p.position_name;

Optional: Add a flag for the selected applicant's status #

If you want to track whether the selected applicant has applied to each position, you can extend the query with a simple check:

SELECT 
    p.position_id, 
    p.position_name,
    CASE WHEN EXISTS (
        SELECT 1 FROM application a 
        WHERE a.position_id = p.position_id 
        AND a.applicant = @selected_applicant
    ) THEN TRUE ELSE FALSE END AS has_applied_by_selected
FROM positions p
ORDER BY p.position_name;

If you can't create a positions table (limited workaround) #

If you're unable to add a new table, note that you can't retrieve positions with zero applications (since they don't exist in the application table). But you can still get all unique positions that have been applied for by someone:

SELECT DISTINCT position
FROM application;

This will include positions the selected applicant has and hasn't applied for, but misses any positions with no applications entirely.

Why your EXCEPT approach fell short #

Chances are you tried something like this:

SELECT position FROM application
EXCEPT
SELECT position FROM application WHERE applicant = @selected_applicant;

This only returns positions the selected applicant hasn't applied for—not the full set of positions including those they have applied for and those with no applications. That's why it didn't meet your needs.

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