SQL合并数据表时去除重复TS_NAME字段的技术求助

Hey there! Let's tackle this duplicate TS_NAME problem you're facing. From what you described, this is almost certainly happening because your two tables have a one-to-many relationship (one TS_NAME maps to multiple test descriptions/expected values), and your JOIN is returning every matching pair—hence the repeated TS_NAME for each related record. Here are a few solutions depending on exactly what you need for your final output:

Option 1: Show TS_NAME Only Once Per Group (With Empty Values for Subsequent Rows) #

If you want to keep all the test descriptions/expected values but only display TS_NAME on the first row of each group, use a window function like ROW_NUMBER() to tag rows in each TS_NAME group:

SELECT
    -- Show TS_NAME only for the first row in the group, empty string otherwise
    CASE WHEN row_num = 1 THEN TS_NAME ELSE '' END AS TS_NAME,
    TEST_DESC,
    EXPECTED_VALUE
FROM (
    SELECT
        tn.TS_NAME,
        td.TEST_DESC,
        td.EXPECTED_VALUE,
        -- Assign a row number to each record in the same TS_NAME group
        ROW_NUMBER() OVER (PARTITION BY tn.TS_NAME ORDER BY td.TEST_DESC) AS row_num
    FROM test_names tn
    INNER JOIN test_details td ON tn.TS_NAME = td.TS_NAME
) grouped_data;

This works in most modern databases (MySQL 8.0+, PostgreSQL, SQL Server, etc.). Adjust the ORDER BY clause inside OVER() to sort the related records how you prefer.

Option 2: Merge Multiple Descriptions/Values Into a Single Row #

If you'd rather combine all related test descriptions and expected values into one row per TS_NAME, use string aggregation functions (the exact function varies by database):

For MySQL: #

SELECT
    tn.TS_NAME,
    GROUP_CONCAT(td.TEST_DESC SEPARATOR '; ') AS combined_descriptions,
    GROUP_CONCAT(td.EXPECTED_VALUE SEPARATOR '; ') AS combined_expected_values
FROM test_names tn
INNER JOIN test_details td ON tn.TS_NAME = td.TS_NAME
GROUP BY tn.TS_NAME;

For PostgreSQL/SQL Server (2017+): #

SELECT
    tn.TS_NAME,
    STRING_AGG(td.TEST_DESC, '; ') AS combined_descriptions,
    STRING_AGG(td.EXPECTED_VALUE, '; ') AS combined_expected_values
FROM test_names tn
INNER JOIN test_details td ON tn.TS_NAME = td.TS_NAME
GROUP BY tn.TS_NAME;

You can change the separator ('; ') to whatever works best for your use case (like '\n' for line breaks).

Option 3: Keep Only One Record Per TS_NAME #

If you only need a single test description/expected value per TS_NAME (e.g., the first or most recent one), use the window function approach to filter for just the first row in each group:

SELECT
    TS_NAME,
    TEST_DESC,
    EXPECTED_VALUE
FROM (
    SELECT
        tn.TS_NAME,
        td.TEST_DESC,
        td.EXPECTED_VALUE,
        ROW_NUMBER() OVER (PARTITION BY tn.TS_NAME ORDER BY td.TEST_DESC) AS row_num
    FROM test_names tn
    INNER JOIN test_details td ON tn.TS_NAME = td.TS_NAME
) filtered_data
WHERE row_num = 1;

Modify the ORDER BY in the window function to pick the specific record you want (e.g., ORDER BY created_date DESC for the newest entry).

Quick Check: Is Your Source Table Duplicated? #

Before trying the above, double-check if your test_names table already has duplicate TS_NAME values. If it does, you'll want to deduplicate it first:

SELECT DISTINCT tn.TS_NAME, td.TEST_DESC, td.EXPECTED_VALUE
FROM (SELECT DISTINCT TS_NAME FROM test_names) tn
INNER JOIN test_details td ON tn.TS_NAME = td.TS_NAME;

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