使用Oracle优化一下这段SQL，让结果保留两位小数，to_char(round(count(case when k.customerid is not null then 1 end) -- / NULLIF(count(case when k.service_status in ('2', '6', '8') then 1 end), 0), 4) * 100, 'fm999.99')||'%' as rate

您可以使用 Oracle 的 ROUND 函数来保留结果的两位小数，使用 TO_CHAR 函数来格式化结果并加上 % 符号，并使用 NULLIF 函数来避免在除数为 0 时发生错误。您的 SQL 语句可以改写为如下形式：

SELECT TO_CHAR(ROUND(COUNT(CASE WHEN k.customerid IS NOT NULL THEN 1 END) / 
                    NULLIF(COUNT(CASE WHEN k.service_status IN (2, 6, 8) THEN 1 END), 0) * 100, 2), 'fm999.99') || '%' as rate
FROM ...

在这里，ROUND 函数用来保留两位小数，TO_CHAR 函数用来格式化数字并加上 % 符号，NULLIF 函数用来避免在除数为 0 时发生错误。

相关问题

SELECT cs.taskname, rt.taskcont rtname, rp.id rid, rp.YEAR, rp.taskcont rpname, ROUND( SUM( IFNULL( rp.centralfinance, 0 ) + IFNULL( rp.localfinance, 0 ) + IFNULL( rp.investedfinance, 0 ) + IFNULL( rp.supportfinance, 0 ) + IFNULL( rp.selffinance, 0 )), 6 ) total_fund, rp.iszb, ni.NAME, nui.realname, IFNULL( rp.progress, 0 ) progress, IFNULL( rp.fundprogress, 0 ) fundprogress FROM nk_xjsg_refinetask_process rp JOIN nk_xjsg_refinetask rt ON rt.id = rp.rtaskid AND rt.customerid = rp.customerid JOIN nk_xjsg_constask cs ON cs.id = rt.taskid AND cs.customerid = rt.customerid LEFT JOIN nk_xjsg_itemteam ni ON ni.id = cs.itemteamid AND ni.customerid = cs.customerid JOIN nk_xjsg_process_leader nl ON nl.itemid = rp.id AND nl.customerid = rp.customerid JOIN nk_user_info nui ON nui.id = nl.tid AND nui.customerid = nl.customerid GROUP BY cs.taskname, rt.taskcont, rp.YEAR, rp.taskcont, rp.iszb, ni.NAME, nui.realname, rp.progress, rp.fundprogress, rp.id CONVERT ( cs.orders, SIGNED ) ASC, rt.serno ASC, rp.YEAR ASC, rp.serno ASC > 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 'ASC, rt.serno ASC, rp.YEAR ASC, rp.serno ASC' at line 40 > 时间: 0.004s 解决这个错误

根据错误提示，可以看出是在 SQL 语句的最后一行出现了语法错误，具体是在 `ORDER BY` 子句中。在这个子句中，需要使用 `ASC` 或者 `DESC` 来指定排序的方式，但是在这个 SQL 语句中，似乎缺少了排序方式的指定。

因此，你可以尝试在 `ORDER BY` 子句中添加一个排序方式，例如：

ORDER BY
    CONVERT ( cs.orders, SIGNED ) ASC,
    rt.serno ASC,
    rp.YEAR ASC,
    rp.serno ASC ASC

这里我假设 `rp.serno ASC` 是你想要的排序方式，如果不是，可以根据实际情况进行修改。

SELECT cs.taskname, rt.taskcont, rp.YEAR, rp.taskcont rpname, rp.iszb, ni.id, ifnull( ni.totalfinance, '0' ) totalfinance, ifnull( ni.centralfinance, '0' ) centralfinance, ifnull( ni.localfinance, '0' ) localfinance, ifnull( ni.investedfinance, '0' ) investedfinance, ifnull( ni.supportfinance, '0' ) supportfinance, ifnull( ni.selffinance, '0' ) selffinance, ni.statusnum FROM nk_xjsg_constask cs JOIN nk_xjsg_refinetask rt ON cs.id = rt.taskid AND cs.customerid = rt.customerid JOIN nk_xjsg_refinetask_process rp ON rt.id = rp.rtaskid AND rt.customerid = rp.customerid LEFT JOIN nk_xjsg_itembudget ni ON ni.rpid = rp.id AND ni.customerid = rp.customerid join nk_xjsg_process_leader nl on nl.itemid = rp.id and nl.customerid = rp.customerid join nk_user_info nui on nui.id = nl.tid and nui.customerid = nl.customerid where cs.pid = '40289e8c87fff7f80188042c15d50010' and ni.flowtaskid in ('40289e8b87a7c0ef0188527f09700206','40289e8b87a7c0ef018852359f600204') where cs.pid = '40289e8c87fff7f80188042c15d50010' > 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 'where cs.pid = '40289e8c87fff7f80188042c15d50010'' at line 33 哪里错了

根据错误提示，可以看出问题出在第二个 `where` 子句上。在 SQL 语句中，每个 `where` 子句应该只出现一次。因此，您需要删除第二个 `where` 子句，并将其与第一个 `where` 子句合并。合并后的 SQL 语句如下所示：

SELECT
    cs.taskname,
    rt.taskcont,
    rp.YEAR,
    rp.taskcont rpname,
    rp.iszb,
    ni.id,
    ifnull(ni.totalfinance, '0') totalfinance,
    ifnull(ni.centralfinance, '0') centralfinance,
    ifnull(ni.localfinance, '0') localfinance,
    ifnull(ni.investedfinance, '0') investedfinance,
    ifnull(ni.supportfinance, '0') supportfinance,
    ifnull(ni.selffinance, '0') selffinance,
    ni.statusnum
FROM
    nk_xjsg_constask cs
    JOIN nk_xjsg_refinetask rt ON cs.id = rt.taskid AND cs.customerid = rt.customerid
    JOIN nk_xjsg_refinetask_process rp ON rt.id = rp.rtaskid AND rt.customerid = rp.customerid
    LEFT JOIN nk_xjsg_itembudget ni ON ni.rpid = rp.id AND ni.customerid = rp.customerid
    JOIN nk_xjsg_process_leader nl ON nl.itemid = rp.id AND nl.customerid = rp.customerid
    JOIN nk_user_info nui ON nui.id = nl.tid AND nui.customerid = nl.customerid
WHERE
    cs.pid = '40289e8c87fff7f80188042c15d50010'
    AND ni.flowtaskid IN ('40289e8b87a7c0ef0188527f09700206', '40289e8b87a7c0ef018852359f600204')