on子句MySQL中的未知列'B.in_out‘
提问于 2020-05-06 04:43:08
我已经对如何左连接同一个表感到困惑了，我这样做了左连接查询：
SELECT CASE
WHEN A.time BETWEEN '06:15:00' AND '11:59:00' THEN "IN"
WHEN A.time BETWEEN '12:00:00' AND '23:59:00' THEN "OUT"
WHEN A.time BETWEEN '00:00:00' AND '06:14:00' THEN "OUT"
END in_out, A.no_reg, A.date, A.date_time AS time_in, B.date_time AS time_out
FROM raw_attendance A
LEFT JOIN raw_attendance B
ON A.no_reg = B.no_reg
AND A.date_time < B.date_time
AND B.in_out = 'OUT'
HAVING A.in_out = 'IN'但结果是：
Unknown column 'B.in_out' in 'on clause'有没有人可以帮助我告诉我哪里遗漏了我的查询，所以我犯了一些错误？谢谢
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-05-06 04:51:15
不能在同一级别的WHERE子句中重用select子句中定义的带别名的CASE表达式。以下是我认为您在这里尝试做的一种解决方法：
SELECT
CASE WHEN A.time BETWEEN '06:15:00' AND '11:59:00' THEN 'IN'
WHEN A.time BETWEEN '12:00:00' AND '23:59:00' THEN 'OUT'
WHEN A.time BETWEEN '00:00:00' AND '06:14:00' THEN 'OUT' END in_out,
A.no_reg,
A.date,
A.date_time AS time_in,
B.date_time AS time_out
FROM raw_attendance A
LEFT JOIN raw_attendance B
ON A.no_reg = B.no_reg AND
A.date_time < B.date_time AND
(B.time BETWEEN '00:00:00' AND '06:14:00' OR
B.time BETWEEN '12:00:00' AND '23:59:00')
WHERE
A.time BETWEEN '06:15:00' AND '11:59:00';在这个版本中，我没有尝试重用CASE表达式，而是简单地包含了用于确定IN或OUT结果的原始范围检查。如果您确实希望重用CASE表达式，则可能必须使用子查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61627565
复制相关文章
腾讯云开发者
