mysql - SQL 运行良好，但如果用于创建 View ，我得到 "Error Code: 1242. Subquery returns more than 1 row"

Question

我在使用 MySQL 时遇到了一些奇怪的问题。我有一个相当长的查询(如下所示)，其中包含子查询:

select
    SITEID as ID,
    "Predicted" as KIND,
    ATTRIBUTE as ATTRIBUTE_ID,
    "1" as TYPE,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour where siteid = ID and attribute = ATTRIBUTE_ID and TS = date_format((now() - interval 4 hour), '%Y-%m-%d %H:00:00')) as MINUS4,
    if(PREDICTEDRATE is null, 0, PREDICTEDRATE) as CURRENT,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour where siteid = ID and attribute = ATTRIBUTE_ID and TS = date_format((now() + interval 1 hour), '%Y-%m-%d %H:00:00')) as PLUS1,
from
    predictionperhour
where
    TS = date_format(now(), '%Y-%m-%d %H:00:00')
group by
    SITEID, 
    ATTRIBUTE

如果我在 SQL Workbench 中运行此查询(不限制结果)，我会返回 2027 行，一切正常。现在，如果我使用完全相同的选择来创建 View ，然后尝试从那里进行简单的选择 *，我会收到上述错误“错误代码:1242。子查询返回超过 1 行”。

请问这是怎么发生的？我尝试修改子查询来进行计数，但据我所知，所有结果都是 1...我什至尝试添加 LIMIT 1，但尽管在这种情况下我没有遇到任何错误，但我说的是，我没有从子查询中返回预期结果，而是返回 0。

你能帮我看看问题出在哪里吗？

谢谢

Answer 1

尝试使用表的别名(p0、p1、p2)。如果我们不为其添加别名，则同一表 predictionperhour AS p0 中可能存在来自外部查询而不是内部查询的列 attribute = ATTRIBUTE_ID。

select
    SITEID as ID,
    "Predicted" as KIND,
    ATTRIBUTE as ATTRIBUTE_ID,
    "1" as TYPE,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour AS p1 where p1.siteid = p0.ID and p1.attribute = p0.ATTRIBUTE_ID and TS = date_format((now() - interval 4 hour), '%Y-%m-%d %H:00:00')) as MINUS4,
    if(PREDICTEDRATE is null, 0, PREDICTEDRATE) as CURRENT,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour AS p2 where p2.siteid = p0.ID and p2.attribute = p0.ATTRIBUTE_ID and TS = date_format((now() + interval 1 hour), '%Y-%m-%d %H:00:00')) as PLUS1,
from
    predictionperhour AS p0
where
    TS = date_format(now(), '%Y-%m-%d %H:00:00')
group by
    SITEID, 
    ATTRIBUTE