从时间中提取小时并添加转换为小时的分钟，以及从时间中抽取分钟并查找提醒(模)

Question

我刚开始学习Oracle SQL，就陷入了一个问题。情况如下。我有MySQL数据库，我想把一些查询从MySQL翻译成Oracle SQL我有一个表weekly_report，它包含像YEAR、WEEK这样的数据。我需要返回本周的TOTAL_SUM和本周的TOTAL_SUM。例如，如果我有10个weeklyReport，它是过去和本周的。我想知道本周和过去一周的现状。当我在Workbanch中运行这个MySQL查询时，我得到的结果类似于

TOTAL_SUM
459:30

459存在hour和30存在minutes这是MySQL查询

SELECT 
CONCAT(
-- extract hours from time and add minutes converted to hours
(SUM(SUBSTRING_INDEX(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTRING_INDEX(A.Quantity, ':', -1)) / 60),0)),
':', 
-- extract minutes from time and find reminder (modulo)
LPAD((SUM(SUBSTRING_INDEX(A.Quantity, ':', -1)) % 60), 2, 0)
) 
AS 
TOTAL_SUM FROM (
SELECT 
ata.ATAID AS AtaId, ata.ProjectID, ata.StartDate, ataAW.Quantity
FROM 
ata
INNER JOIN 
weekly_report
ON
weekly_report.ataId = ata.ATAID
INNER JOIN 
ata_articles AS ataAW 
ON 
ataAW.wrId = weekly_report.id 
WHERE 
ata.ATAID = $ataId
AND 
ataAW.type = 1
AND
(weekly_report.status != 3 AND weekly_report.status != 4)
AND
(
weekly_report.year < (SELECT year FROM weekly_report WHERE id = $weeklyReportId)
OR
(
weekly_report.year <= (SELECT year FROM weekly_report WHERE id = $weeklyReportId)
AND
weekly_report.week <= (SELECT week FROM weekly_report WHERE id = $weeklyReportId)
)
)
) AS A group by A.AtaId

当我修改这个查询并在Oracle SQL中写入时，我得到了类似于这个的东西

SELECT 
CONCAT(
-- extract hours from time and add minutes converted to hours
(SUM(SUBSTR(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTR(A.Quantity, ':', -1)) / 60),0)),
':', 
-- extract minutes from time and find reminder (modulo)
LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM 
FROM (
SELECT 
ata.ATAID AS AtaId, ata.ProjectID, ata.StartDate, ataAW.Quantity
FROM 
ata
INNER JOIN 
weekly_report
ON
weekly_report.ataId = ata.ATAID
INNER JOIN 
ata_articles ataAW 
ON 
ataAW.wrId = weekly_report.id 
WHERE 
ata.ATAID = 321
AND 
ataAW.type = 1
AND
(weekly_report.status != 3 AND weekly_report.status != 4)
AND
(
weekly_report.year < (SELECT year FROM weekly_report WHERE id = 200)
OR
(
weekly_report.year <= (SELECT year FROM weekly_report WHERE id = 200)
AND
weekly_report.week <= (SELECT week FROM weekly_report WHERE id = 200)
)
)
) A 
group by A.AtaId

目前weekly_report表格中的位置为：

YEAR      WEEK
2020      12
2020      15
2020      12 

这里的问题是，我收到错误消息，类似

ORA-00907: missing right parenthesis

在这行代码中

LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM 

所以，我认为用摘录的时间和时间写的部分是不正确的。

CONCAT(
-- extract hours from time and add minutes converted to hours
(SUM(SUBSTR(A.Quantity, ':', 1)) + TRUNCATE((SUM(SUBSTR(A.Quantity, ':', -1)) / 60),0)),
':', 
-- extract minutes from time and find reminder (modulo)
LPAD((SUM(SUBSTR(A.Quantity, ':', -1)) MOD 60), 2, 0)) TOTAL_SUM 

有人能告诉我这里出了什么问题吗？如何修改此项以运行查询成功？

Answer 1

您似乎滥用了MOD函数。

应为例如

SQL> select mod(5, 2) from dual;
MOD(5,2)
----------
1

而不是

SQL> select 5 mod 2 from dual;
select 5 mod 2 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL>