基于一个值删除重复项

Question

    customer id name    Pay_type
    1111    aaaa    regular
    1111    aaaa    late
    1111    aaaa    regular
    1111    aaaa    regular
    2222    bbbb    regular
    2222    bbbb    regular
    2222    bbbb    regular
    3333    cccc    regular
    3333    cccc    late
    4444    dddd    regular
    4444    dddd    regular

我有一个SQL查询，它给我提供了上面的结果，我希望结果能够删除任何有滞纳金的客户。

产出必须是：

customer id name    Pay_type
2222    bbbb    regular
2222    bbbb    regular
2222    bbbb    regular
4444    dddd    regular
4444    dddd    regular

select 
distinct a.customer_id, 
a.name, 
pay_type 
from table a 
left join table b on a.customer_id= b.id 
left join table c on c.id = b.pay_id 
where b.status = 'Done

Answer 1

我这样做是为了反对加入：

select *
from table a
where not exists (
  select null
  from table b
  where
    a.customer_id = b.customer_id and
    b.pay_type = 'late'
)

这与一种独特的或“不存在”的方法相比有其优点，因为它将停止关注它找到匹配。这对于大数据集和小数据集都应该有效地工作。

任何使用distinct的解决方案都必须评估整个数据集，然后删除dupes。

Answer 2

我不知道你的桌子到底是什么样子，但你可以这样做：

WHERE customer_id NOT IN (
    SELECT customer_id
    FROM table_with_customer_and_pay_type
    WHERE pay_type = 'late'
    GROUP BY customer_id )

Answer 3

常见表式变体：

WITH orig_result_set AS (
    select 
    distinct a.customer_id, 
    a.name, 
    pay_type 
    from table a 
    left join table b on a.customer_id= b.id 
    left join table c on c.id = b.pay_id 
    where b.status = 'Done'
),

exclude_late_payments AS (
    SELECT DISTINCT customer_id
    FROM orig_result_set
    WHERE pay_type = 'late'
),

on_time_payments AS (
    SELECT customer_id,
           name,
           pay_type
    FROM orig_result_set
    WHERE customer_id NOT IN exclude_late_payments
)

SELECT *
FROM on_time_payments