SQL需求：重复标识符对应列仅显示首行（保留重复项）

核心思路 #

利用窗口函数ROW_NUMBER()对每个发票ID的记录分组排序，当行号为1时显示原字段值，否则返回空值，实现重复列仅首行显示、保留所有数据行的效果。

各SQL方言实现示例 #

MySQL 8.0+/MariaDB 10.2+ #

SELECT
    CASE WHEN rn = 1 THEN invoice_id ELSE NULL END AS invoice_id,
    CASE WHEN rn = 1 THEN project_name ELSE NULL END AS project_name,
    payment_date,
    amount_paid,
    remaining_balance
FROM (
    SELECT
        invoice_id,
        project_name,
        payment_date,
        amount_paid,
        remaining_balance,
        ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY payment_date) AS rn
    FROM your_invoice_table
) AS subquery
ORDER BY invoice_id, payment_date;

SQL Server #

SELECT
    CASE WHEN rn = 1 THEN invoice_id ELSE NULL END AS invoice_id,
    CASE WHEN rn = 1 THEN project_name ELSE NULL END AS project_name,
    payment_date,
    amount_paid,
    remaining_balance
FROM (
    SELECT
        invoice_id,
        project_name,
        payment_date,
        amount_paid,
        remaining_balance,
        ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY payment_date) AS rn
    FROM your_invoice_table
) AS subquery
ORDER BY invoice_id, payment_date;

PostgreSQL #

SELECT
    CASE WHEN rn = 1 THEN invoice_id ELSE NULL END AS invoice_id,
    CASE WHEN rn = 1 THEN project_name ELSE NULL END AS project_name,
    payment_date,
    amount_paid,
    remaining_balance
FROM (
    SELECT
        invoice_id,
        project_name,
        payment_date,
        amount_paid,
        remaining_balance,
        ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY payment_date) AS rn
    FROM your_invoice_table
) AS subquery
ORDER BY invoice_id, payment_date;

注意事项 #

• 替换your_invoice_table为实际表名，可调整ORDER BY payment_date为你需要的排序逻辑（比如按支付记录ID排序）。
• 若需要空值显示为空白字符串而非NULL，可将ELSE NULL替换为ELSE ''（MySQL、SQL Server等支持该写法）。

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