如何查询coolTableBro表中fnumber与fname重复的原始行及重复行？

Got it, let's tackle this query problem! What you need is to retrieve every row where the combination of fnumber and fname appears at least twice in coolTableBro—meaning both the original row and all its duplicates get included, not just the duplicate entries alone. Here are two reliable ways to do this:

方法1：使用窗口函数（推荐现代SQL数据库） #

This approach uses a window function to count how many times each (fnumber, fname) pair exists, then filters for pairs with counts > 1. It's clean and efficient for most modern databases (MySQL 8+, PostgreSQL, SQL Server, etc.):

SELECT fnumber, findex, fname
FROM (
    SELECT 
        fnumber, findex, fname,
        -- 按fnumber和fname分组，统计每组的行数
        COUNT(*) OVER (PARTITION BY fnumber, fname) AS pair_count
    FROM coolTableBro
) AS subquery
-- 只保留那些组内行数大于1的行
WHERE pair_count > 1;

原理说明： #

The inner query adds a pair_count column to every row, which tells you how many total rows share the same fnumber and fname. The outer query then filters out any rows where this count is only 1 (i.e., non-duplicate pairs), leaving you with all rows from duplicate pairs.

方法2：使用子查询+IN条件（兼容老版本数据库） #

If you're working with an older database that doesn't support window functions, this method works by first identifying all duplicate (fnumber, fname) pairs, then fetching all rows that match those pairs:

SELECT fnumber, findex, fname
FROM coolTableBro
WHERE (fnumber, fname) IN (
    -- 先找出所有出现次数超过1的fnumber+fname组合
    SELECT fnumber, fname
    FROM coolTableBro
    GROUP BY fnumber, fname
    HAVING COUNT(*) > 1
);

原理说明： #

The subquery groups rows by fnumber and fname, then uses HAVING COUNT(*) > 1 to get only pairs that have duplicates. The main query then pulls every row in the table that matches one of these duplicate pairs.

小提示： #

• For large tables, adding an index on (fnumber, fname) will drastically speed up both queries, since the database can quickly locate and group the duplicate pairs.
• Both methods will return all rows in the duplicate groups—so if a (fnumber, fname) pair appears 5 times, you'll get all 5 rows back, not just 4 duplicates.

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