代码之家  ›  专栏  ›  技术社区  ›  Anthony

找出组中存在两个条件的位置

  •  0
  • Anthony  · 技术社区  · 6 年前

    我有一张桌子:

    ref | name
    ===========
    123abc | received
    123abc | pending
    134b   | pending
    156c   | received
    

    我希望能够识别 ref 只有一个 pending 而不是一个 received . 注意可能有多个 receive S和 悬而未决的 S代表同一参考。

    如何输出只有挂起而没有接收到的引用?

    在我的示例中,它将返回:

    134b | pending
    

    我想是这样的:

    SELECT ref, name FROM my_table
    WHERE ref IS NOT NULL
    GROUP BY ref, name
    HAVING ref = 'pending' AND ref = 'received'
    ;
    
    3 回复  |  直到 6 年前
        1
  •  1
  •   Gordon Linoff    6 年前

    select name
    from my_table
    where ref in ('pending', 'received')
    group by name
    having min(ref) = 'pending' and min(ref) = max(ref);
    

        2
  •  1
  •   Caldazar    6 年前

    select distinct ref, name
    from my_table t1
    where t1.name = 'pending' and not exists (select * from my_table t2 where t1.ref=t2.ref and t2.name='received')
    

    https://dbfiddle.uk/?rdbms=postgres_10&fiddle=6fd633fe52129ff3246d8dba55e5fc17

        3
  •  1
  •   Dimitar Spasovski    6 年前

    WITH

    WITH ref_recieved_pending AS (
        SELECT
          ref,
          sum(CASE WHEN name = 'received'
            THEN 1
              ELSE 0 END) as recieved_count,
          sum(CASE WHEN name = 'pending'
            THEN 1
              ELSE 0 END) as pending_count
        FROM test_table_2
        GROUP BY ref
    )
    SELECT DISTINCT
      ref,
      'pending' as pending
    FROM ref_recieved_pending
    WHERE pending_count > 0 AND recieved_count = 0;