SQL多表关联下按特定颜色组合筛选Shape数据的实现问询

Got it, let's break down how to solve this with a single SQL query. The key here is aggregating color data per Shape and using HAVING clauses to enforce your exact conditions—since we need to check the full set of colors associated with each Shape, not just individual rows.

Assumptions About Table Structure #

First, I'll assume your tables follow these key relationships:

• Shape: shape_id (primary key) + other Shape-specific fields
• ShapeDetails: shape_id (foreign key linking to Shape)
• ShapeSize: shape_id (foreign key linking to Shape)
• ShapeColor: shape_id (foreign key linking to Shape), color (varchar field storing color names)

The Query #

SELECT s.shape_id, s.* -- Replace s.* with specific Shape fields you need to retrieve
FROM Shape s
JOIN ShapeDetails sd ON s.shape_id = sd.shape_id
JOIN ShapeSize ss ON s.shape_id = ss.shape_id
JOIN ShapeColor sc ON s.shape_id = sc.shape_id
GROUP BY s.shape_id, s.* -- Adjust based on your DB: MySQL allows s.* if ONLY_FULL_GROUP_BY is disabled; for PostgreSQL/SQL Server, list all selected fields explicitly
HAVING
    -- Condition 1: No "yellow" color is associated with the Shape
    MAX(CASE WHEN sc.color = 'yellow' THEN 1 ELSE 0 END) = 0
    -- Condition 2: At least 1 of the target colors (red/pink/blue) is present
    AND COUNT(DISTINCT CASE WHEN sc.color IN ('red', 'pink', 'blue') THEN sc.color END) >= 1
    -- Condition 3: Only the target colors are present (no other colors outside red/pink/blue)
    AND COUNT(CASE WHEN sc.color NOT IN ('red', 'pink', 'blue') THEN 1 END) = 0
    -- Condition 4: Exactly 1-3 distinct target colors (matches your requirement)
    AND COUNT(DISTINCT CASE WHEN sc.color IN ('red', 'pink', 'blue') THEN sc.color END) <= 3;

How Each Condition Works #

Let's unpack the HAVING clause logic:

• No yellow: The MAX(CASE...) checks if any row for the Shape has "yellow"—if yes, it returns 1, so we filter those Shapes out by requiring this value to be 0.
• At least one target color: COUNT(DISTINCT...) counts unique colors from the red/pink/blue set. We need this to be at least 1 to exclude Shapes with none of these colors.
• Only target colors: The COUNT(CASE...) counts any colors outside red/pink/blue. Requiring this to be 0 ensures no unexpected colors are attached to the Shape.
• 1-3 target colors: The <=3 ensures we only keep Shapes with 1, 2, or all 3 of the target colors (since we already restricted to only those colors, this covers your exact range).

Simplified Alternative (No Duplicate Colors) #

If you can confirm there are no duplicate color entries for a single Shape (i.e., each color appears only once per Shape in ShapeColor), you can simplify the COUNT(DISTINCT) to a regular COUNT:

SELECT s.shape_id, s.*
FROM Shape s
JOIN ShapeDetails sd ON s.shape_id = sd.shape_id
JOIN ShapeSize ss ON s.shape_id = ss.shape_id
JOIN ShapeColor sc ON s.shape_id = sc.shape_id
GROUP BY s.shape_id, s.*
HAVING
    MAX(CASE WHEN sc.color = 'yellow' THEN 1 ELSE 0 END) = 0
    AND COUNT(CASE WHEN sc.color IN ('red', 'pink', 'blue') THEN 1 END) BETWEEN 1 AND 3
    AND COUNT(CASE WHEN sc.color NOT IN ('red', 'pink', 'blue') THEN 1 END) = 0;

This works because without duplicates, the count of target color rows equals the number of unique target colors.

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