使用电子邮件和电话对客户进行实体解析

发布于 2025-01-17 03:23:15 字数 2783 浏览 0 评论 0原文

我有一个包含 ID、全名、电子邮件和电话的客户表

| id | full_name      | email                      | phone         |
| -- | -------------- | -------------------------- | ------------- |
| 1  | namita.robin   | [email protected]           | +135698887445 |
| 2  | alfred.buka    | [email protected]       | +236588547811 |
| 3  | karim.tazi     | [email protected]            | +212661845794 |
| 4  |                | [email protected]       | +135698887445 |
| 5  | cheri          | [email protected]       | +135698748788 |
| 6  | rim.rim        | [email protected]       | +245881148787 |

，我想对至少具有相同电子邮件或电话的客户进行分组。此示例的期望输出为：

| id | full_name      | email                      | phone         | master_id |
| -- | -------------- | -------------------------- | ------------- | --------- |
| 1  | namita.robin   | [email protected]           | +135698887445 | 1         |
| 2  | alfred.buka    | [email protected]       | +236588547811 | 2         |
| 3  | karim.tazi     | [email protected]            | +212661845794 | 3         |
| 4  |                | [email protected]       | +135698887445 | 1         |
| 5  | cheri          | [email protected]       | +135698748788 | 1         |
| 6  | rim.rim        | [email protected]       | +245881148787 | 2         |

原文

I have a customers table with id, full_name, email, and phone

| id | full_name      | email                      | phone         |
| -- | -------------- | -------------------------- | ------------- |
| 1  | namita.robin   | [email protected]           | +135698887445 |
| 2  | alfred.buka    | [email protected]       | +236588547811 |
| 3  | karim.tazi     | [email protected]            | +212661845794 |
| 4  |                | [email protected]       | +135698887445 |
| 5  | cheri          | [email protected]       | +135698748788 |
| 6  | rim.rim        | [email protected]       | +245881148787 |

and I want to group customers that have at least an email or phone that is the same.
desired output for this example would be:

| id | full_name      | email                      | phone         | master_id |
| -- | -------------- | -------------------------- | ------------- | --------- |
| 1  | namita.robin   | [email protected]           | +135698887445 | 1         |
| 2  | alfred.buka    | [email protected]       | +236588547811 | 2         |
| 3  | karim.tazi     | [email protected]            | +212661845794 | 3         |
| 4  |                | [email protected]       | +135698887445 | 1         |
| 5  | cheri          | [email protected]       | +135698748788 | 1         |
| 6  | rim.rim        | [email protected]       | +245881148787 | 2         |

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谁人与我共长歌 2025-01-24 03:23:15

您可以使用dense_rank() 为相同的电子邮件类型获取相同的id 值。仅从电子邮件列中获取电子邮件类型并按该类型排序。

select * ,dense_rank() over(order by reverse(Substring(reverse(email),1,Charindex('@', reverse(email))-1)) ) as master_id
from customers 
order by reverse(Substring(reverse(email),1,Charindex('@', reverse(email))-1))

You can use dense_rank() to get the same id value for the same email type. Get only email type from the email column and order by that.

select * ,dense_rank() over(order by reverse(Substring(reverse(email),1,Charindex('@', reverse(email))-1)) ) as master_id
from customers 
order by reverse(Substring(reverse(email),1,Charindex('@', reverse(email))-1))

回复收藏 0 原文

~没有更多了~