MySql当使用函数WEEK（）与模式时返回错误

问题描述：

我有一个问题，我不能使功能WEEK（日期，模式）工作。MySql当使用函数WEEK（）与模式时返回错误

我有一个SQL我在哪里分组通过一年的款项，然后以周：

SELECT SUM(o.total) as total_org, SUM(o.total_eur) as total_eur, 
WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as period, YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as smallerperiod, `s`.`domain` 
FROM `orders` `o` 
JOIN `shops` `s` ON `o`.`domain_id` = `s`.`id` 
WHERE `o`.`domain_id` = '5' 
GROUP BY YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)), WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) 
ORDER BY `smallerperiod` ASC, `period` ASC

正是作品我多么希望除了周数从0开始，我发现了该模式，将工作最适合我的是3号，所以我试图改变我的SQL语句：

SELECT SUM(o.total) as total_org, SUM(o.total_eur) as total_eur, 
WEEK('DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)', 3) as period, YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as smallerperiod, `s`.`domain` 
FROM `orders` `o` 
JOIN `shops` `s` ON `o`.`domain_id` = `s`.`id` 
WHERE `o`.`domain_id` = '5' 
GROUP BY YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)), WEEK('DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)', 3)) 
ORDER BY `smallerperiod` ASC, `period` ASC

然而，它只是给了我的错误

你的SQL语法有错误;检查对应于您MariaDB的服务器版本正确的语法使用接近“）ORDER BY smallerperiod ASC手册，period ASC

编辑：我试图使它没有单引号工作过：

SELECT SUM(o.total) as total_org, SUM(o.total_eur) as total_eur, 
WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3) as period, YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as smallerperiod, `s`.`domain` 
FROM `orders` `o` 
JOIN `shops` `s` ON `o`.`domain_id` = `s`.`id` 
WHERE `o`.`domain_id` = '5' 
GROUP BY YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)), WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3)) 
ORDER BY `smallerperiod` ASC, `period` ASC

但是，它返回我同样的错误

这里删除引号：'“DATE_ADD（o.date_created_utc，间隔0 HOUR）”' – Jens

事实上，我有，但它改变不了什么，我得到了同样的错误使用引号或没有。 – NeuTronas

你在这里有一个额外的括号：'HOUR）'，3））' – Jens

答

，因为你要调用一个funtion，而不是把它作为一个字符串，你必须删除单引号角落找寻'DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)'。

你得到theeroor，监守你有一个额外的支架位置：HOUR)', 3))

所以查询必须是：

SELECT SUM(o.total) as total_org, SUM(o.total_eur) as total_eur, 
WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3) as period, YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as smallerperiod, `s`.`domain` 
FROM `orders` `o` 
JOIN `shops` `s` ON `o`.`domain_id` = `s`.`id` 
WHERE `o`.`domain_id` = '5' 
GROUP BY YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)), WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3) 
ORDER BY `smallerperiod` ASC, `period` ASC

答

你只需要后。否则寄托都完美我删除，去年支架曾尝试过。你也可以尝试下面的代码。

SELECT SUM(o.total) as total_org, SUM(o.total_eur) as total_eur, 
    WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3) as period, YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)) as smallerperiod, `s`.`domain` 
    FROM `orders` `o` 
    JOIN `shops` `s` ON `o`.`domain_id` = `s`.`id` 
    WHERE `o`.`domain_id` = '5' 
    GROUP BY 
    YEAR(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR)), 
    WEEK(DATE_ADD(o.date_created_utc, INTERVAL 0 HOUR), 3) 
    ORDER BY `smallerperiod` ASC, `period` ASC

MySql当使用函数WEEK（）与模式时返回错误

相关推荐