bugfix> sql > 投稿

これが私の問題です。

例として4つのテーブルがあります(形状-> ShapeDetails-> ShapeSize-> ShapeColor)

基本的に、新しいShapeを作成するたびに、新しい行ShapeColorが作成されます(選択した各色に対して、テーブルShapeColorに新しい行が作成されます)

だから、Shapeには多くの色を付けることができますが、色の「名前」に基づいて一致させたいのですが、色のいずれかが「赤」、「ピンク」、「青」である場合、この場合、「ピンク」、「青」、「黄色」は一致しません。

私はそれらを1色および/または3色の間で一致させたいと思います。 例(色:赤、ピンク、青、黄):

  • 0 0 0 0(これらの色のいずれも= NOK)resulstSetにない
  • 1 0 0 0(赤のみで、ピンクブルーイエロー= OKではない)resultSetで一致
  • 0 1 0 0(ピンクのみで、赤青黄= OKではない)一致
  • 1 1 0 0(赤ピンクのみで、青と黄色ではない= OK)一致
  • 1 0 1 0 ...
  • 1 1 1 1(これらすべての色= NOK)はresultsSetにありません

ところで、私はShapeColorに到達するためにいくつかの結合を使用します

1つのSQLクエリでこの問題をどのように解決できるかわかりません。どんな種類の助けも大歓迎です

回答 3 件
  • SELECT s.id
    FROM shape s INNER JOIN shape_color sc ON s.id=sc.id
    GROUP BY s.id
    HAVING 
      SUM(CASE WHEN sc.color='red' OR sc.color='pink' OR sc.color='blue' 1 ELSE 0 END) >0
      AND
      HAVING SUM(CASE WHEN sc.color='yellow' 1 ELSE 0 END) = 0
    
    

    HAVINGはグループ化された結果に適用されます 有効な色の合計が>の場合 0は、3つの有効な色の1つが形状に存在することを意味します 無効な色の合計が>の場合 0 thahは黄色が存在することを意味します

    したがって、有効な色が1つ(またはそれ以上)存在し、無効な色が存在しない形状が得られます

    更新:

    SELECT s.id
    FROM shape s INNER JOIN shape_color sc ON s.id=sc.id
    GROUP BY s.id
    HAVING 
      SUM(CASE WHEN sc.color='red' OR sc.color='pink' OR sc.color='blue' OR sc.color='yellow' 1 ELSE 0 END) >0
    AND
      SUM(CASE WHEN sc.color='red' OR sc.color='pink' OR sc.color='blue' OR sc.color='yellow' 1 ELSE 0 END) <=3
    
    

  • 色が独立していると仮定すると、次のことができます。

    SELECT sc.shape_id
    FROM shape_color sc join
         color c
         ON sc.color_id = c.color_id
    WHERE c.color in ('red', 'pink', 'blue', 'yellow')
    GROUP BY sc.shape_id
    HAVING COUNT(*) < 4;
    
    

    (列名は明確ではありませんが、アイデアを得る必要があります。)

    shape_color を見て 、あなたは最初の条件を排除しています-色が必要です。これは、4色未満であることを確認するだけです。暗黙的に、少なくとも1つありますが、 HAVING COUNT(*) > 0 AND COUNT(*) < 4 を追加できます 。

  • GROUP BYとHAVINGを使用した解決策がわかりにくい場合は、LISTAGGを使用して文字列の色を最初に単純に連結します。

    INNER JOIN  色なしの形状を除去するために使用されます(ここでは4-以下のサンプルデータを参照)。

    また、 WHERE  述語は、関連する色のみを制限します。

    select s.shape_id,
    LISTAGG(c.color, ',') WITHIN GROUP (ORDER BY c.color) color_lst
    from shape s
    join shape_color c
    on s.shape_id = c.shape_id
    where c.color in ('red', 'pink', 'blue', 'yellow')
    group by s.shape_id
    ;
      SHAPE_ID COLOR_LST                  
    ---------- ---------------------------
             1 blue,pink,red,yellow         
             2 red                          
             3 blue,pink,yellow
    
    

    あなたの仕事は、4色すべての唯一の否定的なケースを排除するのと同じくらい簡単です。

    with colors as (
    select s.shape_id,
    LISTAGG(c.color, ',') WITHIN GROUP (ORDER BY c.color) color_lst
    from shape s
    join shape_color c
    on s.shape_id = c.shape_id
    where c.color in ('red', 'pink', 'blue', 'yellow')
    group by s.shape_id
    )
    select shape_id
    from colors
    where color_lst != 'blue,pink,red,yellow'
      SHAPE_ID
    ----------
             2 
             3
    
    

    シェイプ内の色が一意であることを確認する必要があります。そうでない場合は、形状内の色を区別するサブクエリを追加する必要があります。同じことが HAVING にも有効です  ベースのソリューション

    サンプルデータ

    create table shape  as
    select 1 shape_id from dual union all
    select 2 shape_id from dual union all
    select 3 shape_id from dual union all
    select 4 shape_id from dual;
    create table shape_color as
    select 1 shape_id, 'red' color from dual union all
    select 1 shape_id, 'pink' color from dual union all
    select 1 shape_id, 'blue' color from dual union all
    select 1 shape_id, 'yellow' color from dual union all
    select 2 shape_id, 'red' color from dual union all
    select 3 shape_id, 'pink' color from dual union all
    select 3 shape_id, 'blue' color from dual union all
    select 3 shape_id, 'yellow' color from dual;
    
    

あなたの答え