UNION ALL 中的 MySQL 语法错误

Question

我有一个使用多个 UNION ALL 的查询。我遇到语法错误，但找不到它。我认为这与使用多个工会有关。我只使用MySQL。如有任何帮助，我们将不胜感激。

错误 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 41 行的“ALL(SELECT a.”附近使用的正确语法

 SELECT   Sum(counter)                           AS counter,
         DATE_FORMAT(dtime,'%Y-%m-%d 00:00:00') AS dtime
FROM     (
                SELECT counter,
                       dtime
                FROM   MinutesVisible
                WHERE  dtime >= '2015:3:1 00:00:00'
                AND    dtime <= '2015:3:16  23:59:59'
                AND    dtime < NOW()
                AND    id='980371'
                AND    counter != 0
                UNION ALL 
                          (
                                 SELECT a.counter,
                                        a.dtime
                                 FROM   MinutesVisible a,
                                        calendar b
                                 WHERE  a.dtime = b.dtime
                                 AND    b.dtime >= '2015:3:1 00:00:00'
                                 AND    b.dtime <= '2015:3:16  23:59:59'
                                 AND    b.dtime < NOW()
                                 AND    id='979661')
                   UNION
                            (
                                   SELECT '0'  AS counter,
                                          b.dtime
                                   FROM   calendar b
                                   WHERE  dtime >= '2015:3:1  00:00:00'
                                   AND    dtime <= '2015:3:16  23:59:59'
                                   AND    dtime < NOW()
                                   AND    dtime NOT IN
                                          (
                                                 SELECT dtime
                                                 FROM   MinutesVisible
                                                 WHERE  dtime >= '2015:3:1 00:00:00'
                                                 AND    dtime <= '2015:3:16 23:59:59'
                                                 AND    dtime < NOW()
                                                 AND    id='979661' ))
                   ORDER BY dtime
                   AND      counter != 0
                   UNION ALL (this is line 41)
                             (
                                    SELECT a.counter,
                                           a.dtime
                                    FROM   MinutesVisible a,
                                           calendar b
                                    WHERE  a.dtime = b.dtime
                                    AND    b.dtime >= '2015:3:1 00:00:00'
                                    AND    b.dtime <= '2015:3:16  23:59:59'
                                    AND    b.dtime < NOW()
                                    AND    id='984121')
                      UNION
                               (
                                      SELECT '0' AS counter,
                                             b.dtime
                                      FROM   calendar b
                                      WHERE  dtime >= '2015:3:1  00:00:00'
                                      AND    dtime <= '2015:3:16  23:59:59'
                                      AND    dtime < NOW()
                                      AND    dtime NOT IN
                                             (
                                                    SELECT dtime
                                                    FROM   MinutesVisible where dtime >= '2015:3:1 00:00:00'
                                                    AND    dtime <= '2015:3:16 23:59:59'
                                                    AND    dtime < NOW()
                                                    AND    id='984121' ))
                      ORDER BY dtime
                      AND      counter != 0) AS MainTable
GROUP BY WEEKOFYEAR(dtime)
HAVING   SUM(counter) > 0

如果我将 'id=' 更改为 id in 并在其中包含其他 id 编号并删除第二个联合，则所有操作都有效。我很确定这与第二个联盟有关。该语句是动态构建的，因此我无法轻松更改为使用 in 而不是多个 union all。

如果我没有清楚地解释这一点，请告诉我，我将提供更多详细信息。

Answer 1

检查您所写行上方的语句(这是第 41 行)尝试删除/更新此语句并执行您的查询。

ORDER BY
            dtime
        AND counter != 0

我们不能在 UNION 和 UNION ALL 之间使用顺序。 order by 应该放在最后，在所有 UNION/UNION ALL 语句之后