选择在两列中具有不同值的ID

my table looks the following:

我的表看起来如下:

EAN | Country | Status
1   | Germany | A
1   | France  | B
1   | Spain   | A
2   | Germany | A
2   | France  | A
2   | Spain   | A

I need every ID which Status is "A" in at least one country but is not in at least one other.

我需要至少在一个国家/地区中状态为“A”的每个ID,但至少不在另一个国家/地区。

In this example the result should contain ID 1 because it has the status "A" in Germany and Spain but not in France.

在此示例中,结果应包含ID 1,因为它在德国和西班牙的状态为“A”,而在法国则不包含。

2 个解决方案

#1

Try a self join:

尝试自我加入:

select distinct main.EAN
  from [table] main
  join [table] sub
    on main.EAN = sub.EAN and
       main.Country <> sub.Country and
       main.Status <> sub.Status

[table] has to be replaced with your actual table's name. This should give you the desired results.

[table]必须替换为您的实际表名。这应该会给你想要的结果。

#2

Using a subselect, you can do the following:

使用子选择,您可以执行以下操作:

select
  EAN
from
  (
    select
      EAN,
      sum(case when Status = "A" then 1 else 0 end) statusA,
      sum(case when Status <> "A" then 1 else 0 end) statusOther
    from
      statuses
    group by
      EAN
  ) summary
where
  summary.statusA > 0 and
  summary.statusOther > 0

See an example here.

在这里查看示例。

#1