我被要求发出一个查询来回答一个问题,我发出的查询是:
SELECT SNAME, TO_CHAR(SAL,' l9999 ')为MONTHLY, TO_CHAR(SAL/22, 'L999.99')为DAILY, TO_CHAR(SAL/22/8, 'L99.99')为hour FROM STAFF
但这一次我需要截断到最接近的便士,而不是整数,任何帮助将不胜感激。
乘以100(因此便士是单位而不是小数),截断到最接近的便士,然后除以100:
SELECT SNAME,
TO_CHAR(SAL,'L9,999') AS MONTHLY,
TO_CHAR(TRUNC(SAL/22*100)/100, 'L999.99') AS DAILY,
TO_CHAR(TRUNC(SAL/22/8*100)/100, 'L99.99') AS HOURLY
FROM STAFF
或者,使用TRUNC
的第二个参数指定要截断的小数位数:
SELECT SNAME,
TO_CHAR(SAL,'L9,999') AS MONTHLY,
TO_CHAR(TRUNC(SAL/22, 2), 'L999.99') AS DAILY,
TO_CHAR(TRUNC(SAL/22/8, 2), 'L99.99') AS HOURLY
FROM STAFF