mysql - 为什么“有条款”中有未知列
问题描述
以下代码运行良好:
select session_id
from playback
where session_id not in
(select session_id
from playback a join ads b
on timestamp between start_time and end_time
and a.customer_id = b.customer_id)
我试图在子查询中更改以下部分:
join on timestamp between start_time and end_time
and a.customer_id = b.customer_id
至
join on a.customer_id = b.customer_id
having b.timestamp between a.start_time and a.end_time
但出现错误:Unknown column 'b.timestamp' in 'having clause'
在这里使用 have() 有什么问题?
解决方案
当您有没有使用 Group By 时,就会出现此问题。
我没有时间测试这些,但这里有三种可能的方法可以重写你的语句:
没有分组依据
select session_id
from playback
where session_id not in
(select distinct a.session_id
from playback as a
join ads as b
on a.customer_id = b.customer_id
where b.timestamp between a.start_time and a.end_time
)
与分组
select session_id
from playback
where session_id not in
(select a.session_id
from playback as a
join ads as b
on a.customer_id = b.customer_id
group by a.session_id
having b.timestamp between a.start_time and a.end_time
)
通过不使用子查询
select distinct a.session_id
from playback as a
left join ads as b
on b.customer_id = a.customer_id
where a.timestamp not between start_time and end_time
and b.customer_id is not null
推荐阅读
- javascript - For循环Javascript中的添加和减去按钮
- arrays - 使在 main 中初始化的数组可供其他函数访问
- c - 插入节点 - C 中的内存泄漏
- reactjs - Pdftron 设置,获取 Http 404 文件未找到错误,同时参考 index.html 用于反应应用程序
- python - 将序列化程序方法字段添加到 generics.ListAPIView - Django
- c# - 如何为与 windows 和 linux 兼容的 C# 应用程序创建 VPN 客户端
- angular - 从服务器动态添加新路由到 app-routing.module (Angular 11)
- ios - 是否可以通过其“ID”号访问特定的 SceneKit 节点
- google-bigquery - BigQuery 中的 CASE WHEN 比较同一字段中的值
- javascript - 即使数组不为空,函数也给了我一个空数组