如何编写SQL语句筛选COST_TYPE='B'计数>1的唯一SHIPMENT_GID

Hey there! I see where your original query is tripping up—let's break down the issues and get you a working solution.

What's Wrong with the Original Query? #

• You're trying to count COST_TYPE = 'B' entries without grouping by SHIPMENT_GID, so the database can't calculate the count per individual shipment.
• Using count(COST_TYPE = 'B') isn't the most reliable way to count matching entries across all databases (some treat boolean results differently).
• Using shipment_gid = (subquery) will throw an error if the subquery returns more than one result—you need IN instead, or an EXISTS clause.

Working Solutions #

Option 1: Get Only the Unique Shipment GIDs

If you just need the list of SHIPMENT_GIDs that have more than one COST_TYPE='B' entry:

SELECT DISTINCT SHIPMENT_GID
FROM shipment_cost
WHERE COST_TYPE = 'B'
GROUP BY SHIPMENT_GID
HAVING COUNT(*) > 1;

This groups the data by SHIPMENT_GID, counts how many 'B' entries each has, and filters for groups with counts greater than 1.

Option 2: Get All Records for Those Shipment GIDs

If you want all rows from the table for the qualifying SHIPMENT_GIDs (like your original SELECT * attempt):

SELECT *
FROM shipment_cost
WHERE SHIPMENT_GID IN (
    SELECT SHIPMENT_GID
    FROM shipment_cost
    WHERE COST_TYPE = 'B'
    GROUP BY SHIPMENT_GID
    HAVING COUNT(*) > 1
);

The subquery first identifies the valid SHIPMENT_GIDs, then the main query pulls all related records.

Option 3: Use EXISTS for Better Performance (Large Datasets)

If your table has a lot of data, EXISTS can be more efficient because it stops checking as soon as it finds a match:

SELECT sc.*
FROM shipment_cost sc
WHERE EXISTS (
    SELECT 1
    FROM shipment_cost sc2
    WHERE sc2.SHIPMENT_GID = sc.SHIPMENT_GID
      AND sc2.COST_TYPE = 'B'
    GROUP BY sc2.SHIPMENT_GID
    HAVING COUNT(*) > 1
);

All these queries will correctly return SHIPMENT_GID=12233 (since it has two 'B' cost entries) as the result.

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