无法找到SQL语法错误

Question

我正在尝试运行这个SQL查询：

SELECT avg(response_seconds) as s FROM 
    ( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) ) as response_seconds 
    FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber) 
    AS r 

但是我看到了这个错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber WHE' at line 3

我无法确定查询中的错误位置

- charlie

---

什麼是SQL？SQL Server？請更新您的標籤。 - rory.ap

看起来你在 as response_seconds 之前把 ) 关闭得太早了。它应该在 (select...) 内部，但你却把它放在外面了。correct syntax to use near FROM 意味着你必须在那之前立即查找问题所在。 - Michael Berkowski

你有两个 FROM - 一个来自嵌套查询，另一个来自 tickets，你需要将 tickets 加入到查询中。 - Alon Eitan

您有6个开放的(，应该在GROUP BY之后最终关闭，但第6个闭合的)出现在as response_seconds之前。 - Michael Berkowski

你应该格式化你的查询并使用一个好的编辑器，它会显示括号对。 - dnoeth

Answer 1

在as response_seconds之前删除括号)

SELECT avg(response_seconds) as s FROM 
    ( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) as response_seconds 
      FROM tickets t 
         JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
      WHERE u.type = 'update' 
        and t.customer = 'Y' 
        and DATE(u.datetime) = '2016-04-18' 
      GROUP BY t.ticketnumber
    ) AS r 

你这个计算式的右括号太多了，导致子查询提前结束。

- RiggsFolly

---

但是你有FROM response_seconds和FROM tickets吗？ - Alon Eitan

这是一个子查询，它需要一个FROM。 - RiggsFolly

哦，现在对齐后，我看到你是正确的。+1 - Alon Eitan

Answer 2

）在 ) ) as response_seconds 中多了一个多余的括号，删除它即可解决问题。为了更好的可读性，我对代码进行了对齐：

SELECT avg(response_seconds) AS s 
FROM 
( 
    SELECT 
        time_to_sec( 
            timediff( 
                from_unixtime( 
                    floor( 
                            UNIX_TIMESTAMP(u.datetime)/60 
                        )*60 
                ), u.datetime
            )   -- ) the one more extra parenthesis causing the problem
        ) as response_seconds 
    FROM tickets t 
    JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber
) AS r 

- Arulkumar

Answer 3

看起来你这里多了一个')' "tetime) ) ) <-- as response_seco"

SELECT avg(response_seconds) as s
FROM (
      select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) as response_seconds 
      FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
      WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
      GROUP BY t.ticketnumber
    ) AS r 

- Frederick Álvarez

Answer 4

数一下你的括号。实际上你正在做这个：

SELECT some_column as s
FROM some_sub_select as response_seconds 
FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
....

在同一查询中不能有两个FROM子句。

也许您的括号对齐不正确？也许您想做一个JOIN而不是第二个FROM？从看起来像是一个不完整的总体查询中很难确定。

- David