使用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 IFNULL(COUNT(DISTINCT nt.id), 0) total, IFNULL(COUNT( DISTINCT ta.id), 0) endtotal, cs.taskname, nt.id, nt.name FROM nk_xjsg_taskresult nt JOIN nk_xjsg_constask cs ON cs.id = nt.taskid AND cs.customerid = nt.customerid AND cs.pid = '40289e8c87fff7f80188042c15d50010' JOIN nk_xjsg_consplan nc ON nc.id = cs.pid AND nc.customerid = cs.customerid LEFT JOIN ( SELECT id, resultid, statusnum, statusname, endnum FROM nk_xjsg_taskresult_date WHERE customerid = '40289e8c8803a391018803a6ef8f0000' ) ta ON ta.resultid = nt.id and ta.statusnum = '40' -- AND ta.number = 1 -- AND ta.endnum = nt.resultnum WHERE nt.customerid = '40289e8c8803a391018803a6ef8f0000' GROUP BY cs.taskname, nt.id, nt.name; 怎么让endtotal的值从5变成1

根据你提供的SQL查询语句，endtotal的值是通过COUNT函数统计的DISTINCT ta.id的数量，因此结果为5。如果你想让endtotal的值从5变成1，需要在COUNT函数中加入条件，只统计符合要求的数据。根据你的需求，可以在COUNT函数的参数中加入条件“ta.endnum = nt.resultnum”，确保只统计符合要求的数据。修改后的SQL查询语句如下：

SELECT IFNULL(COUNT(DISTINCT nt.id), 0) total, IFNULL(COUNT(DISTINCT CASE WHEN ta.endnum = nt.resultnum THEN ta.id ELSE NULL END), 0) endtotal, cs.taskname, nt.id, nt.name 
FROM nk_xjsg_taskresult nt 
JOIN nk_xjsg_constask cs ON cs.id = nt.taskid AND cs.customerid = nt.customerid AND cs.pid = '40289e8c87fff7f80188042c15d50010' 
JOIN nk_xjsg_consplan nc ON nc.id = cs.pid AND nc.customerid = cs.customerid 
LEFT JOIN (
  SELECT id, resultid, statusnum, statusname, endnum 
  FROM nk_xjsg_taskresult_date 
  WHERE customerid = '40289e8c8803a391018803a6ef8f0000'
) ta ON ta.resultid = nt.id and ta.statusnum = '40' -- AND ta.number = 1 
WHERE nt.customerid = '40289e8c8803a391018803a6ef8f0000' 
GROUP BY cs.taskname, nt.id, nt.name;

修改后的查询语句中，将第二个COUNT函数的参数改为“DISTINCT CASE WHEN ta.endnum = nt.resultnum THEN ta.id ELSE NULL END”，这样只有符合条件的数据才会被统计。

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')