一对多关系Case嵌套在If语句中

Question

我有两个表，我想查询。表A包括售出的彩票数量，扫描的彩票数量，账户id，账户名称。表B包含帐户id和帐户组成员关系。不幸的是，Account Group Membership字段有多种用途(我没有能力更改表结构，第三方DB)。我想找到第一个表中每行的帐户组成员资格，但只有当它出现在帐户组的特定列表中，然后如果列表中没有帐户组，它会发送一个not included。

我在想一个If循环内的情况?但似乎无法解决。这是我现在拥有的(它根本不起作用)

select a.num_seats,
a.num_seats_attended,
a.acct_id,
a.owner_name,
if b.membership_id in (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,740) THEN 
(CASE WHEN b.membership_id = 10 then 'Type A'
when b.membership_id = 11 then 'Type B'
when b.membership_id = 12 then 'Type C'
when b.membership_id = 13 then 'Type D'
when b.membership_id = 14 then 'Type E'
when b.membership_id = 15 then 'Type F'
when b.membership_id = 16 then 'Type G'
when b.membership_id = 17 then 'Type H'
when b.membership_id = 18 then 'Type I'
when b.membership_id = 19 then 'Type J'
when b.membership_id = 20 then 'Type K'
when b.membership_id = 21 then 'Type L'
when b.membership_id = 22 then 'Type M'
when b.membership_id = 23 then 'Type N'
when b.membership_id = 24 then 'Type O'
when b.membership_id = 25 then 'Type P'
when b.membership_id = 740 then 'Type Q')
else 'Non Member'
end as membership_level
from table a, table b
where a.acct_id = b.acct_id
Table A
Num_Seats   Num_Seats_Attended  acct_id owner_name
1           1                   345     J Doe
2           1                   673     M Singh
1           0                   354     T Chong
3           3                   767     F McDonald
1           1                   768     C Macron
Table B
acct_id     membership_id
345         1
345         12
673         208
354         13
767         740
767         82
768         7
With results looking like
Num_Seats   Num_Seats_Attended  acct_id owner_name  membership_level
1           1                   345     J Doe       Type C
2           1                   673     M Singh     Not Included
1           0                   354     T Chong     Type D
3           3                   767     F McDonald  Type Q
1           1                   768     C Macron    Not Included

Answer 1

不能在查询中使用IF语句，IF语句只能在存储过程/函数/触发器中使用。您可以使用一个案例用一个当相反:

case when b.membership_id in (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,740) THEN 
(CASE WHEN b.membership_id = 10 then 'Type A'
when b.membership_id = 11 then 'Type B'
when b.membership_id = 12 then 'Type C'
when b.membership_id = 13 then 'Type D'
when b.membership_id = 14 then 'Type E'
when b.membership_id = 15 then 'Type F'
when b.membership_id = 16 then 'Type G'
when b.membership_id = 17 then 'Type H'
when b.membership_id = 18 then 'Type I'
when b.membership_id = 19 then 'Type J'
when b.membership_id = 20 then 'Type K'
when b.membership_id = 21 then 'Type L'
when b.membership_id = 22 then 'Type M'
when b.membership_id = 23 then 'Type N'
when b.membership_id = 24 then 'Type O'
when b.membership_id = 25 then 'Type P'
when b.membership_id = 740 then 'Type Q' END)
else 'Non Member'
end as membership_level

(注意你的CASE没有结束)

或者你可以使用mysql的非标准IF函数(IF(testcondition, truerresult,falseresult)):

IF(b.membership_id in (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,740), 
(CASE WHEN b.membership_id = 10 then 'Type A'
when b.membership_id = 11 then 'Type B'
when b.membership_id = 12 then 'Type C'
when b.membership_id = 13 then 'Type D'
when b.membership_id = 14 then 'Type E'
when b.membership_id = 15 then 'Type F'
when b.membership_id = 16 then 'Type G'
when b.membership_id = 17 then 'Type H'
when b.membership_id = 18 then 'Type I'
when b.membership_id = 19 then 'Type J'
when b.membership_id = 20 then 'Type K'
when b.membership_id = 21 then 'Type L'
when b.membership_id = 22 then 'Type M'
when b.membership_id = 23 then 'Type N'
when b.membership_id = 24 then 'Type O'
when b.membership_id = 25 then 'Type P'
when b.membership_id = 740 then 'Type Q' END),
'Non Member') as membership_level

或者在以下情况下省略in并包含Non Member:

CASE WHEN b.membership_id = 10 then 'Type A'
when b.membership_id = 11 then 'Type B'
when b.membership_id = 12 then 'Type C'
when b.membership_id = 13 then 'Type D'
when b.membership_id = 14 then 'Type E'
when b.membership_id = 15 then 'Type F'
when b.membership_id = 16 then 'Type G'
when b.membership_id = 17 then 'Type H'
when b.membership_id = 18 then 'Type I'
when b.membership_id = 19 then 'Type J'
when b.membership_id = 20 then 'Type K'
when b.membership_id = 21 then 'Type L'
when b.membership_id = 22 then 'Type M'
when b.membership_id = 23 then 'Type N'
when b.membership_id = 24 then 'Type O'
when b.membership_id = 25 then 'Type P'
when b.membership_id = 740 then 'Type Q'
else 'Non Member'
end as membership_level

或更简洁的形式:

CASE b.membership_id
when 10 then 'Type A'
when 11 then 'Type B'
when 12 then 'Type C'
when 13 then 'Type D'
when 14 then 'Type E'
when 15 then 'Type F'
when 16 then 'Type G'
when 17 then 'Type H'
when 18 then 'Type I'
when 19 then 'Type J'
when 20 then 'Type K'
when 21 then 'Type L'
when 22 then 'Type M'
when 23 then 'Type N'
when 24 then 'Type O'
when 25 then 'Type P'
when 740 then 'Type Q'
else 'Non Member'
end as membership_level

或者使用(mysql特定的)ELT和FIELD来进行映射:

coalesce(elt(field(b.membership_id,10,11,12...),'Type A','Type B','Type C'...),'Non Member)

要处理每个帐户多行问题，我建议您首先只加入您想要的成员行，并按acct_id分组，并在成员级别上使用max来确定返回的多个类型中的哪一个是一致的:

select a.num_seats,
a.num_seats_attended,
a.acct_id,
a.owner_name,
max(CASE b.membership_id
when 10 then 'Type A'
when 11 then 'Type B'
when 12 then 'Type C'
when 13 then 'Type D'
when 14 then 'Type E'
when 15 then 'Type F'
when 16 then 'Type G'
when 17 then 'Type H'
when 18 then 'Type I'
when 19 then 'Type J'
when 20 then 'Type K'
when 21 then 'Type L'
when 22 then 'Type M'
when 23 then 'Type N'
when 24 then 'Type O'
when 25 then 'Type P'
when 740 then 'Type Q'
else 'Non Member'
end) as membership_level
from table a
left join table b on b.acct_id = a.acct_id and b.membership_id in (10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,740)
group by a.acct_id