当我创建 View 时，mysql 查询没有给我相同的结果

Question

select packages.id AS id,
packages.co_id AS co_id,
(select companies.name from companies where (packages.co_id = companies.id)) AS co_name,
(select companies.tel from companies where (packages.co_id = companies.id)) AS tel,
packages.datetime AS datetime,
(select tracking.datetime from tracking where (packages.id = tracking.pid and action='tookit')) AS tooktime,
(select tracking.datetime from tracking where (packages.id = tracking.pid and action='putit')) AS puttime,
packages.status AS status from packages

当我运行它时，这个查询工作得很好，但是当我将其作为 View 时，它没有显示相同的结果，我在“跟踪”表的字段中得到空值

我重新创建了查询，问题仍然存在

SELECT t1.id , t1.co_id , t2.name, t2.tel, t1.datetime , t3.datetime as tookit, t4.datetime as putit
FROM packages as t1

INNER JOIN companies as t2
ON t1.co_id=t2.id

left JOIN tracking as t3
ON t1.id = t3.pid and t3.action ='tookit'

left JOIN tracking as t4
ON t1.id = t4.pid and t4.action ='putit'

我感谢任何帮助

Answer 1

重写您的查询检查是否有帮助。

    SELECT         packages.id AS id,        packages.co_id AS co_id,        companies.name AS co_name,        companies.tel AS tel,        packages.datetime AS DATETIME,        t1.datetime AS tooktime,        t2.datetime AS puttime,        packages.status AS STATUS     FROM      packages       LEFT JOIN companies         ON packages.co_id = companies.id       LEFT JOIN tracking t1         ON packages.id = t1.pid         AND t1.ACTION = 'tookit'       LEFT JOIN tracking t2         ON packages.id = t2.pid         AND t2.ACTION = 'putit'