php - 根据mysql中特定列数据过滤数据

Question

我的数据库中有以下表格。

表:actor_action

actor_id | time | monday_action_id | tuesday_action_id | .... | friday_action_id
1           1       2                   1                       3
1           2       3                   4                       1
2           1       1                   5                       4

表:操作

action_id | action_name | action_type | action_group_name
1               A1          single          -
2               X2          multiple        X_GROUP
3               B1          single          -
4               X1          multiple        X_GROUP
5               Y1          multiple        Y_GROUP

我想查询给定 actor_id 的以下详细信息(例如:actor_id = 1)。在这里，如您所见，如果 action_type 为“multiple”，我想选择 action_group_name 而不是 action_name 。

预期输出:-

actor_id | time | monday_name | tuesday_name | .... | friday_name
1           1       X_GROUP     A1                      B1
1           2       B1          X_GROUP                 A1

目前我有以下查询，仅选择操作名称。

SELECT a1.action_name AS monday_name,
       a2.action_name AS tuesday_name,
       a3.action_name AS wednesday_name,
       a4.action_name AS thursday_name,
       a5.action_name AS friday_name,
       act.time AS time
FROM actor_action act
LEFT JOIN action a1 ON ct.monday_action_id=a1.action_id 
LEFT JOIN action a2 ON ct.tuesday_action_id=a2.action_id 
LEFT JOIN action a3 ON ct.wednesday_action_id=a3.action_id 
LEFT JOIN action a4 ON ct.thursday_action_id=a4.action_id 
LEFT JOIN action a5 ON ct.friday_action_id=a5.action_id 
WHERE act.actor_id = ?

如何编写一个查询来执行上面提到的过滤。

Answer 1

您只需替换每个单独的列选择，例如

a1.action_name AS monday_name

使用适当的CASE表达式:

CASE WHEN a1.action_type = 'multiple' THEN a1.action_group_name
     ELSE a1.action_name
END AS monday_name

输出(基于您的示例数据)

monday_name     tuesday_name    friday_name     time
X_GROUP         A1              B1              1
B1              X_GROUP         A1              2

Demo on dbfiddle

请注意，根据查询中的表别名，此列的表别名应为 a1，而不是 s1。