无法从连接子查询查询的MariaDB SQL语法问题排查

问题根源 #

查询示例1的语法错误出在子查询别名的位置：你把AS subquery放在了WHERE子句后面，这不符合MySQL的语法规则。子查询的别名必须紧跟在定义子查询的闭合括号)之后，而不是内部WHERE条件的末尾。

修正后的查询示例1 #

SELECT * FROM
    (SELECT events.id, events.type, events.timestamp, work_list.task
    FROM 
        events
        INNER JOIN records ON events.record_id = records.id
        INNER JOIN work_list ON records.work_list_id = work_list.id
    WHERE work_list.customer_number = '1234'
    ) AS subquery;

另外，原查询里连接表时套的额外括号(events INNER JOIN ...)是多余的，可以直接去掉，不影响逻辑且更简洁。

结合后续统计逻辑的整合示例 #

按照你后续的统计需求（查询示例3），可以把过滤后的子查询直接替换到the filered subquery here的位置，整合后的完整查询如下：

SELECT *, ((SUM(rate * diff) OVER (ORDER BY startTime
     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)) /
     (SUM(diff) OVER(ORDER BY startTime
     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW))) as rate_WMA
FROM (
    SELECT id, startTime, counts, diff, (counts / diff)*3600 as rate
    FROM (
        SELECT events.id, TIMESTAMPDIFF(SECOND, MIN(events.timestamp), MAX(events.timestamp)) AS diff, SUM(events.change) as counts, MIN(events.timestamp) as startTime
        FROM events
        INNER JOIN records ON events.record_id = records.id
        INNER JOIN work_list ON records.work_list_id = work_list.id
        WHERE work_list.customer_number = '1234'
        GROUP BY events.id 
    ) AS subquery
    WHERE diff > 0 
) AS totaltotal;

内容的提问来源于stack exchange，提问作者Jussi Tamminen