处理每个组的列中的值

ycl3bljg  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(138)

我有一个mysql表,其中列出了客户和他们购买的店铺分支,类似于以下内容:

customer_id   |   branch_id   |   is_major_branch
    -----------------------------------------------
        5               24                1
        5               83                0
        5               241               0
        8               66                0
        8               72                0
        9               15                1
        16              31                1
        16              61                1
``` `is_major_branch` 如果那家分店特别大的话是1。
如何删除客户在次要分支机构购物的所有行(是\u major\u branch=0),除非客户只在次要分支机构购物过?结果集示例:

customer_id | branch_id | is_major_branch
-----------------------------------------------
5 241 1
8 66 0
8 72 0
9 15 1
16 31 1
16 61 1

注意customer8是如何只在一个小的分支中购物的,所以我们在删除时忽略了它们。
sdnqo3pr

sdnqo3pr1#

您可以通过以下操作删除行:

delete t
    from t join
         (select customer_id, max(is_major_branch) as max_is_major_branch
          from t
          group by customer_id
         ) tt
         on t.customer_id = tt.customer_id
    where t.is_major_branch = 0 and tt.max_is_major_branch = 1;

如果你只是想 select 查询,然后使用 exists :

select t.*
from t
where not (t.is_major_branch = 0 and
           exists (select 1 from t t2 where t2.customer_id = t.customer_id and t2.is_major_branch = 1)
          );

相关问题