PostgreSQL中关联ro_master、tank_master、du_master三张表并获取指定ID的非冗余查询结果方法

Hey there! Let me break down why you're seeing all those duplicates first: when you join tank_master and du_master directly to ro_master, you're creating a cartesian product for each ro entry. For example, ro_id=7 has 4 tank records and 6 du records—so 4*6=24 rows of the same ro info paired with every tank-du combination. That's why your result set is so bloated.

Here are a few clean solutions to get non-redundant results based on what you need:

Solution 1: Aggregate related data into comma-separated fields #

If you just want a single row per ro entry with all its products and du types listed together, use subqueries with STRING_AGG (works in PostgreSQL; use GROUP_CONCAT instead for MySQL):

SELECT 
    rm.cms_code,
    rm.ro_name,
    rm.ro_address,
    -- Get all distinct products for the ro, joined into a string
    (SELECT STRING_AGG(DISTINCT tm.product, ', ') 
     FROM tank_master tm 
     WHERE tm.cms_code_id = rm.id) AS products,
    -- Get all distinct du types for the ro, joined into a string
    (SELECT STRING_AGG(DISTINCT dm.du_type, ', ') 
     FROM du_master dm 
     WHERE dm.cms_code_id = rm.id) AS du_types
FROM ro_master rm 
WHERE rm.id IN (4,7);

This will return one row per ro, with no repeated ro information.

Solution 2: Use JSON arrays for structured detail #

If you want to keep all individual tank/du records but group them under their ro entry (great for app consumption), use JSON_AGG:

SELECT 
    rm.cms_code,
    rm.ro_name,
    rm.ro_address,
    -- Aggregate all distinct products into a JSON array
    JSON_AGG(DISTINCT tm.product) AS product_list,
    -- Aggregate all distinct du types into a JSON array
    JSON_AGG(DISTINCT dm.du_type) AS du_type_list
FROM ro_master rm 
LEFT JOIN tank_master tm ON rm.id = tm.cms_code_id 
LEFT JOIN du_master dm ON rm.id = dm.cms_code_id 
WHERE rm.id IN (4,7)
GROUP BY rm.id, rm.cms_code, rm.ro_name, rm.ro_address;

If you need the full tank/du object details instead of just individual fields, you can do:

SELECT 
    rm.cms_code,
    rm.ro_name,
    rm.ro_address,
    JSON_AGG(DISTINCT tm) AS tank_details,
    JSON_AGG(DISTINCT dm) AS du_details
FROM ro_master rm 
LEFT JOIN tank_master tm ON rm.id = tm.cms_code_id 
LEFT JOIN du_master dm ON rm.id = dm.cms_code_id 
WHERE rm.id IN (4,7)
GROUP BY rm.id, rm.cms_code, rm.ro_name, rm.ro_address;

Solution 3: Remove exact duplicates only #

If you still want to see each product-du type combination but just eliminate exact duplicate rows, add DISTINCT to your original query:

SELECT DISTINCT
    rm.cms_code,
    rm.ro_name,
    rm.ro_address,
    tm.product,
    dm.du_type 
FROM ro_master rm 
LEFT JOIN tank_master tm ON rm.id = tm.cms_code_id 
LEFT JOIN du_master dm ON rm.id = dm.cms_code_id 
WHERE rm.id IN (4,7);

This will cut down the result set to unique combinations of ro info + product + du_type, but you'll still have multiple rows per ro (one for each unique combination).

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