mysql - 我们可以在连接表时在连接条件中使用 Case When created 列吗
问题描述
如果我们在连接条件中创建列时使用案例......代码运行。但它是正确的吗?如果是怎么执行的?
select *,
case when position('/' in pax_name)>0
then SUBSTR(pax_name, 1, position('/' in pax_name)- 1)
end as **lastname**,
CASE WHEN position('/' in pax_name)>0
THEN SUBSTR(pax_name, position('/' in pax_name) + 1, LENGTH(pax_name))
END as **firstname**
from o
inner join m
on o.record=m.record
and o.pax_first_name = **firstname**
and o.pax_last_name = **lastname**
解决方案
中定义的列别名在select
同级别的大多数查询中不可用select
。特别是,它们不适用于where
orfrom
子句。
您可以使用以下方法完成此操作having
:
select *,
(case when position('/' in pax_name) > 0
then SUBSTR(pax_name, 1, position('/' in pax_name)- 1)
end) as lastname,
(case when position('/' in pax_name) >0
then substr(pax_name, position('/' in pax_name) + 1, length(pax_name))
end) as firstname
from o inner join
m
on o.record = m.record
having o.pax_first_name = firstname and
o.pax_last_name = lastname;
您可以简化逻辑。我想你只是想要:
select *,
(case when pax_name like '%'
then substring_index(pax_name, '/', 1)
end) as firstname,
(case when pax_name like '%'
then substring_index(pax_name, '/', -1)
end) as lastname
from o inner join
m
on o.record = m.record
having o.pax_first_name = firstname and
o.pax_last_name = lastname;
我还建议放弃拥有,所以:
select *,
(case when pax_name like '%'
then substring_index(pax_name, '/', 1)
end) as firstname,
(case when pax_name like '%'
then substring_index(pax_name, '/', -1)
end) as lastname
from o inner join
m
on o.record = m.record
m.pax_name = concat_ws('/', o.pax_first_name, o.pax_last_name);
推荐阅读
- microsoft-graph-api - 处理会议室
- powershell - 如何使 PowerShell 处理文件名中的 [ 或 ] 好?
- twitter-bootstrap - 如何将 Bootstrap Modal 分为 4 列?
- rxjs - 如何将主题值传递给管道内的 switchMap?
- json - Invoke-RestMethod {"statusCode":400,"error":"Bad Request" } 错误
- java - 如何在 Selenium 的几个地方上传文件?
- java - 我正在尝试从 ResourceResolverFactory 获取对 ResourceResolver 的引用,但得到 Null 指针异常
- express - 在 Apollo / GraphQL 中接收到 webhook 后如何处理突变
- android - 当 Activity 处于前台时,如何在不继续调用 onResume 的情况下正确启动 Activity?
- python-3.x - Python matplotlib 根据来自较大数据集的值缩放 y