如何在SQL查询中返回工作表是教师点赞还是自建的标识？

Hey there! Let's get that status flag working for you. The trouble with your initial IF statement was likely that you weren't explicitly checking which condition each row matched. Here are two straightforward, efficient solutions to add the "liked vs self-created" identifier:

This method is clean and performant because EXISTS stops searching as soon as it finds a match, avoiding unnecessary full scans. It also makes the logic easy to follow:

SELECT 
    w.title,
    w.worksheet_id,
    CASE
        -- First check if the worksheet was created by teacher 5
        WHEN w.teacher_id = 5 THEN '自行创建'
        -- Then check if teacher 5 liked this worksheet
        WHEN EXISTS (
            SELECT 1 
            FROM likes l 
            WHERE l.worksheet_id = w.worksheet_id 
              AND l.teacher_id = 5
        ) THEN '教师点赞'
        -- This ELSE won't trigger since our WHERE clause filters valid rows
        ELSE '其他'
    END AS status_flag
FROM worksheets w
WHERE 
    -- Keep only worksheets created by teacher 5 OR liked by teacher 5
    w.teacher_id = 5 
    OR EXISTS (
        SELECT 1 
        FROM likes l 
        WHERE l.worksheet_id = w.worksheet_id 
          AND l.teacher_id = 5
    );

If you prefer joining tables over subqueries, this approach links your worksheets to the likes records for teacher 5, letting you check for matching likes directly:

SELECT 
    w.title,
    w.worksheet_id,
    CASE
        WHEN w.teacher_id = 5 THEN '自行创建'
        -- If there's a matching like record, it means teacher 5 liked it
        WHEN l.worksheet_id IS NOT NULL THEN '教师点赞'
        ELSE '其他'
    END AS status_flag
FROM worksheets w
LEFT JOIN likes l 
    ON w.worksheet_id = l.worksheet_id 
    AND l.teacher_id = 5 -- Only join likes from teacher 5
WHERE 
    w.teacher_id = 5 
    OR l.worksheet_id IS NOT NULL; -- Filter for liked worksheets

Both solutions will correctly label each worksheet, and they fix the issue by explicitly checking which of your original conditions (created vs liked) applies to each row.

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