將 MySQL 轉換為 PostgreSQL 的問題

我們使用 Trac 進行問題跟踪。我將它從 MySQL 遷移到 PostgreSQL，但某些票證查詢不再起作用。

在下面，我到目前為止所做的只是IFNULL()用COALESCE()和UNIX_TIMESTAMP()替換TO_TIMESTAMP()::TIMESTAMP

我不知道錯誤是什麼意思。該查詢在 MySQL 中執行良好，我不確定，在查看討論它和 PostgreSQL 之間差異的頁面後，問題是什麼（我也不知道用什麼替換 SEC_TO_TIME 行，但刪除該行不會’不修復錯誤）。

SELECT IFNULL(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
   (CASE
     WHEN t.id > 0 THEN (CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END)
     ELSE 'font-weight: bold'
   END) AS __style__,
   t.id AS ticket,
   IF(t.id > 0, t.summary, 'Total') AS summary,
   SEC_TO_TIME(SUM(IF(w.endtime, w.endtime, TO_TIMESTAMP(NOW())::TIMESTAMP) - w.starttime)) AS total
 FROM ticket t
 INNER JOIN work_log w
 WHERE t.id = w.ticket
 GROUP BY t.component, t.id, t.summary, t.status
 WITH ROLLUP HAVING IFNULL(id, -1) = -1 OR (t.summary IS NOT NULL AND t.status IS NOT NULL);

ERROR:  syntax error at or near "WHERE"
LINE 11:  WHERE t.id = w.ticket

**編輯：**我嘗試將 更改WHERE為ON，這似乎解決了該錯誤；但是，我不知道這是否是正確的解決方案。另外，現在我得到了這個"WITH ROLLUP HAVING"部分的錯誤。在 PostgreSQL 中這樣做的方法是什麼？

另外，我不知道SEC_TO_TIME用上面的替換什麼，或者SUM/IF組合（IF()只有 MySQL ——如果我換成CASE，我需要分別測試 0 和 null 嗎？）。根據http://www.verious.com/qa/sec-to-time-function-in-postgre-sql/使用 TO_CHAR不起作用，因為在此處給出的程式碼中分鐘始終為零（即 90 秒結束最高為 00:00:30…)

**編輯 2：**我想要得到的結果是 MySQL 的情況：

**編輯 3：**感謝 a_horse_with_no_name 和 efesar，我最終得到了以下工作查詢：

WITH base AS (
 SELECT t.component AS component, t.status AS status, t.id AS ticket, t.summary AS summary,
     SUM((CASE WHEN w.endtime > 0 THEN w.endtime ELSE CAST(EXTRACT(EPOCH FROM current_timestamp) AS bigint) END) - w.starttime) * INTERVAL '1 second' AS total
   FROM ticket t
   INNER JOIN work_log w
   ON t.id = w.ticket
   GROUP BY t.component, t.id, t.summary, t.status
)
SELECT CONCAT('Component ', component) AS __group__,
   (CASE status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END) AS __style__,
   ticket, summary, total
 FROM base
UNION ALL
SELECT CONCAT('Component ', component) AS __group__,
 'font-weight: bold' as __style__, null, 'Total', SUM(total)
 FROM base
 GROUP BY __group__
UNION ALL
SELECT 'All Components' AS __group__, 'font-weight: bold' as __style__, null, 'Total', SUM(total)
 FROM base
ORDER BY __group__, ticket

像這樣的東西：

SELECT coalesce(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
      CASE
         WHEN t.id > 0 THEN CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END
         ELSE 'font-weight: bold'
       END AS __style__,
       t.id AS ticket,
       case when t.id > 0 then t.summary else 'Total' end AS summary,
       SUM( coalesce(w.endtime, current_timestamp) - w.starttime) AS total
FROM ticket t
 INNER JOIN work_log w ON t.id = w.ticket
GROUP BY t.component, t.id, t.summary, t.status

sum()將以毫秒為單位總結兩者之間的差異，endtime並不starttime確定其背後的意圖是什麼。

我不完全確定匯總的作用，但大致如下：

with base_data as (
  ... the above query goes here ...
)
select __group__, ticket, __style__, summary, total
from base_data
union all
select null, null, null, null, sum(total)
from base_data
group by __group__, ticket, __style__, summary 

可能會讓你開始。

引用自：https://dba.stackexchange.com/questions/62496