如何筛选userid出现次数大于指定阈值的行？验证给定SQL语句的有效性

The Scenario #

You have this sample table:

Your goal is to keep all rows where the userid appears more than 2 times (so only rows for userid 2 and 3 should remain). You're asking if this query works:

SELECT *, count(*) FROM marktable GROUP BY userid HAVING count(*) > 1

Short Answer: No, this query won't meet your needs. Here's why: #

1. GROUP BY userid collapses rows, not filters them
   When you group by userid, the query will return only one row per userid, not all the original rows for that user. For example, userid 2 has 3 rows in your sample, but this query would return just one aggregated row for userid 2—this doesn't match your requirement to keep every original row for qualifying users.

2. SELECT * with GROUP BY is invalid (or unreliable) in most SQL dialects
   Databases like PostgreSQL, SQL Server, or MySQL with ONLY_FULL_GROUP_BY enabled will throw an error here. The * includes columns like activity and location that aren't in the GROUP BY clause and aren't wrapped in an aggregate function (like MAX() or MIN()). Even if it runs in some relaxed modes, the values for these non-aggregated columns will be arbitrary, not the original values from all rows.

3. The HAVING condition doesn't match your requirement
   Your example needs userids with more than 2 occurrences, but the query uses count(*) > 1 (which would include userid 4, who has exactly 2 rows—you don't want that).

Correct Ways to Achieve Your Goal #

Option 1: Use a subquery to find valid userids, then join back to the original table

This works in all SQL dialects:

SELECT m.*
FROM marktable m
INNER JOIN (
    -- First get all userids that appear more than 2 times
    SELECT userid
    FROM marktable
    GROUP BY userid
    HAVING COUNT(*) > 2
) AS valid_users ON m.userid = valid_users.userid

Option 2: Use a window function (for modern SQL databases like MySQL 8+, PostgreSQL, SQL Server)

Window functions let you calculate the count per userid without collapsing rows:

SELECT userid, activity, location
FROM (
    SELECT *,
           -- Count how many times each userid appears across all their rows
           COUNT(*) OVER (PARTITION BY userid) AS total_user_rows
    FROM marktable
) AS user_counts
-- Filter to keep only rows where the user has more than 2 entries
WHERE total_user_rows > 2

Both of these queries will return exactly the rows you want: all 3 rows for userid 2 and all 4 rows for userid 3.

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