mysql - SQL 创建唯一值标志

Question

有很多关于在 MySQL 查询中选择唯一值的问题/答案，但我还没有看到任何关于创建唯一值标志的问题/答案。

我有一个可以在查询输出中多次出现的 customer_ID。我想创建一个新列来标记 customer_ID 是否唯一(0 或 1)。

输出应该是这样的:

ID     | Customer ID      | Unique_Flag
1      | 1234             | 1
2      | 2345             | 1
3      | 2345             | 0
4      | 5678             | 1

如果有人需要澄清，请告诉我。

Answer 1

您似乎想将第一次出现的标记为唯一，而不是其他。所以，让我们加入比较值:

select t.*,
       (id = min_id) as is_first_occurrence
from t join
     (select customer_id, min(id) as min_id
      from t
      group by customer_id
     ) tt
     on t.customer_id = tt.customer_id;

对于大多数人来说，“独特”标志意味着总计数为“1”，而不仅仅是第一次出现。如果那是你想要的，那么你可以使用类似的逻辑:

select t.*,
       (id = min_id) as is_first_occurrence,
       (cnt = 1) as is_unique
from t join
     (select customer_id, min(id) as min_id, count(*) as cnt
      from t
      group by customer_id
     ) tt
     on t.customer_id = tt.customer_id;

并且，在 MySQL 8+ 中，您将使用窗口函数:

select t.*,
       (row_number() over (partition by customer_id order by id) = 1) as is_first_occurrence,
       (count(*) over (partition by customer_id) = 1) as is_unique
from t;