使用 MySQL 子查询和 CASE 语句判断关联状态

---

一、前言

在这篇文章中，我们将详细讲解如何使用 MySQL 的子查询和 CASE 语句来实现复杂的逻辑判断。具体案例是我们有两个表 card_management 和 card_auth_register，其中 card_management 表存储了卡片的基本信息，而 card_auth_register 表存储了卡片的授权状态。我们的目标是为每个卡片创建一个新字段 enable_status_v，并根据以下规则进行赋值：

• 如果该卡片在 card_auth_register 表中有一条记录的 auth_status 为 “已下发”，则 enable_status_v 显示 “已下发”。
• 否则，enable_status_v 显示 “待下发”。

1.表结构及样例数据

假设我们有以下两个表：

1.1card_management

id	card_name
1	Card A
2	Card B
3	Card C

1.2card_auth_register

id	card	auth_status
1	1	已下发
2	1	待下发
3	2	已失效
4	3	待下发

二、SQL 查询实现

为了实现上述逻辑，我们可以使用子查询和 CASE 语句。以下是实现的 SQL 查询：

SELECT cm.*, 
    CASE 
        WHEN EXISTS (SELECT 1 FROM card_auth_register car WHERE car.card = cm.id AND car.auth_status = '已下发') THEN '已下发'
        ELSE '待下发'
    END AS enable_status_v
FROM card_management cm;

1.逐步讲解

2.1 基本选择

首先，我们从 card_management 表中选择所有列：

SELECT cm.*
FROM card_management cm;

这一步只是简单地选择了所有卡片的信息。

2.2 添加 CASE 语句

接下来，我们添加一个 CASE 语句来决定 enable_status_v 的值：

CASE 
    WHEN EXISTS (SELECT 1 FROM card_auth_register car WHERE car.card = cm.id AND car.auth_status = '已下发') THEN '已下发'
    ELSE '待下发'
END AS enable_status_v

2.3解释 CASE 语句

• CASE: 开始一个条件判断。
• WHEN EXISTS: 检查是否存在满足条件的记录。
  • 子查询部分 (SELECT 1 FROM card_auth_register car WHERE car.card = cm.id AND car.auth_status = '已下发')：这个子查询用于检查 card_auth_register 表中是否存在与 card_management 表中的当前卡片 (cm.id) 对应且 auth_status 为 “已下发” 的记录。
  • SELECT 1：子查询的作用仅仅是检查存在性，因此我们不需要实际返回什么数据，1 只是一个占位符。
• THEN '已下发': 如果子查询返回了至少一条记录（即存在这样的记录），那么 enable_status_v 的值为 “已下发”。
• ELSE '待下发': 如果子查询没有返回任何记录，那么 enable_status_v 的值为 “待下发”。
• END AS enable_status_v: 结束 CASE 语句，并命名结果字段为 enable_status_v。

2.4完整查询

把所有部分组合起来：

SELECT cm.*, 
    CASE 
        WHEN EXISTS (SELECT 1 FROM card_auth_register car WHERE car.card = cm.id AND car.auth_status = '已下发') THEN '已下发'
        ELSE '待下发'
    END AS enable_status_v
FROM card_management cm;

2.5查询结果

执行上述查询后，我们会得到如下结果：

id	card_name	enable_status_v
1	Card A	已下发
2	Card B	待下发
3	Card C	待下发

2.结论

通过使用子查询和 CASE 语句，我们可以在 MySQL 中实现复杂的逻辑判断。这种方法不仅灵活，而且在处理包含多表关联的复杂查询时非常高效。希望这篇文章能帮助你更好地理解和应用 MySQL 的高级查询功能。