代码之家 › 专栏 › 技术社区 › Ivan P.

我的查询的SQL语法有什么问题?关闭

mariadb mysql sql

Ivan P. · 技术社区 · 1 年前

关闭这个问题是 not reproducible or was caused by typos 。它目前不接受答案。

这个问题是由打字错误或无法再复制的问题引起的。虽然类似的问题可能 on-topic 在这里,这个问题以一种不太可能帮助未来读者的方式得到了解决。

关闭 2小时前 .

社区正在审查是否从 1小时前 .

Improve this question

我的程序中有这样的查询:

SELECT customer.Id, customer.FirstName, customer.LastName, SUM(PeriodHours) AS SUMPeriodHours, PeriodDay 
FROM (
      (SELECT CustomerId, 
              (UNIX_TIMESTAMP(taskinterval.EndDateUtc) - UNIX_TIMESTAMP(taskinterval.StartDateUtc)) / 3600 AS PeriodHours, 
              date(taskinterval.StartDateUtc) as PeriodDay
       FROM taskinterval
       WHERE StartDateUtc > @STARTDATE and StartDateUtc<@ENDDATE
      ) AS tmptbl
 ) 
INNER JOIN customer on customer.Id = tmptbl.CustomerId
GROUP BY PeriodDay, customer.Id, customer.FirstName, customer.LastName

当我替换“@ENDDATE”和“@STARTDATE”时,它既不适用于我的程序代码,也不适用于SqlWorkbench

它在MariaDB 10.3.32上运行良好,但在MySQL 8.0.35上不起作用,错误如下:

错误代码:1064。您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,了解在客户上使用的正确语法。Id=tmptbl。CustomerId组'

这个错误对我来说似乎很模糊。我看过这篇文章: https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-10-4-and-mysql-8-/ 但没有找到任何可以帮助我解决这个问题的东西。如何修复此查询以使其与MySQL兼容?

2 回复 | 直到 1 年前

Dmitrii Bychenko 1 年前

直接原因是额外的 ( .. ) :

FROM (( SELECT ... FROM taskinterval))

应该是

FROM ( SELECT ... FROM taskinterval)

为了避免这种令人讨厌的错误,请保持您的查询简单, 分裂将其转换为易于阅读的片段,在您的案例中为CTE:

WITH tmptbl AS (
  SELECT CustomerId, 
         (UNIX_TIMESTAMP(taskinterval.EndDateUtc) - UNIX_TIMESTAMP(taskinterval.StartDateUtc)) / 3600 AS PeriodHours, 
         date(taskinterval.StartDateUtc) AS PeriodDay
    FROM taskinterval
   WHERE StartDateUtc > @STARTDATE AND StartDateUtc < @ENDDATE 
)

  SELECT customer.Id, 
         customer.FirstName, 
         customer.LastName, 
         SUM(PeriodHours) AS SUMPeriodHours, 
         PeriodDay 
    FROM tmptbl
    JOIN customer ON customer.Id = tmptbl.CustomerId
GROUP BY customer.Id, 
         customer.FirstName, 
         customer.LastName,
         PeriodDay

AurysVrV 1 年前

你好像多了一组括号。试试这个

    Select
    customer.Id,
    customer.FirstName,
    customer.LastName,
    SUM(PeriodHours) as SUMPeriodHours,
    PeriodDay
    from
    (
    SELECT
      CustomerId,
      (
        UNIX_TIMESTAMP(taskinterval.EndDateUtc) - UNIX_TIMESTAMP(taskinterval.StartDateUtc)
      ) / 3600 as PeriodHours,
      date(taskinterval.StartDateUtc) as PeriodDay
    FROM
      taskinterval
    where
      StartDateUtc > @STARTDATE
      and StartDateUtc < @ENDDATE
    ) as tmptbl
     Inner Join customer on customer.Id = tmptbl.CustomerId
    group by
  PeriodDay,
  customer.Id,
  customer.FirstName,
  customer.LastName