统计command字段删除Users表用户的SQL语句报错求助

Got it, let's figure out why your query isn't working and fix it up properly. There are a couple of clear issues with your current SQL, plus we can tweak it for better clarity and performance.

What's Wrong With Your Original Query #

• Subquery returns multiple columns: The IN clause expects a single list of values (just the telegram_nickname here), but your subquery selects both telegram_nickname and count(command). The database can't match a single column against two values, so this throws an error.
• Condition is reversed: Your goal is to delete users with fewer than 5 Logs entries, but your HAVING clause uses count(command) > 5—that targets users with more than 5 entries, which is the opposite of what you want.
• Unnecessary ORDER BY: Sorting the subquery results does nothing for the IN clause (it only cares about existence, not order). Removing this saves unnecessary processing time.

Fixed SQL Queries #

Option 1: Corrected IN Clause Version

This fixes all the basic issues and matches your core requirement:

DELETE FROM public."Users" 
WHERE telegram_nickname IN (
    SELECT telegram_nickname 
    FROM public."Logs" 
    GROUP BY telegram_nickname 
    HAVING COUNT(command) < 5
);

Option 2: More Efficient EXISTS Version

If your Logs table is large, EXISTS is often faster because it stops searching as soon as it finds a matching row for a user, instead of generating a full list first:

DELETE FROM public."Users" u
WHERE EXISTS (
    SELECT 1
    FROM public."Logs" l
    WHERE l.telegram_nickname = u.telegram_nickname
    GROUP BY l.telegram_nickname
    HAVING COUNT(l.command) < 5
);

Option 3: Include Users With No Logs Entries

If you also want to delete users who have zero entries in Logs (since 0 < 5), the above queries won't catch them. Use this version to cover that edge case:

DELETE FROM public."Users" u
WHERE NOT EXISTS (
    SELECT 1
    FROM public."Logs" l
    WHERE l.telegram_nickname = u.telegram_nickname
)
OR EXISTS (
    SELECT 1
    FROM public."Logs" l
    WHERE l.telegram_nickname = u.telegram_nickname
    GROUP BY l.telegram_nickname
    HAVING COUNT(l.command) < 5
);

Or a more readable LEFT JOIN approach:

DELETE FROM public."Users" u
LEFT JOIN (
    SELECT telegram_nickname, COUNT(command) AS cmd_count
    FROM public."Logs"
    GROUP BY telegram_nickname
) l ON u.telegram_nickname = l.telegram_nickname
WHERE l.cmd_count < 5 OR l.cmd_count IS NULL;

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