我有两个表:1. 带有事务 ID 和起点和终点的事务表 2. 带有点名称的立表
我需要以这种格式针对每个事务创建一个条目:事务 ID、起点、起点名称、终点、终点名称
select t.id, t.start_point, s.name, t.end_point, s1.name
from transaction t
left join stand s on s.stand_id = t.start_point and s.state = 2
left join stand s1 on s.stand_id = t.end_point and s1.state = 2
where t.state = 2 and date(t.created_at) = curdate()-1
and t.start_point is not null and t.end_point is not null
我的结果计数应该是 3660,但我得到的是 8462。我得到 3660,而没有 s1 left join。如何更改此查询?
您有一个错误的s
-而不是s1
在left join stand s1 on s1.stand_id = t.end_point and s1.state = 2
试
select t.id, t.start_point, s.name, t.end_point, s1.name
from transaction t
left join stand s on s.stand_id = t.start_point and s.state = 2
left join stand s1 on s1.stand_id = t.end_point and s1.state = 2
where t.state = 2 and date(t.created_at) = curdate()-1
and t.start_point is not null and t.end_point is not null
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句