use NVL()
SELECT agent.agent_id, agent.agent_name, SUM(nvl(consignment.value, 0))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;
SELECT agent.agent_id, agent.agent_name, SUM(nvl(consignment.value, 0))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;
SELECT agent.agent_id, agent.agent_name, SUM(nvl(consignment.value, 0))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;2.use decode
use NVL()
SELECT agent.agent_id, agent.agent_name, SUM(decode(consignment.value, null,0,consignment.value))*0.02 "Commission (2%)"
FROM agent, consignment
WHERE agent.agent_id = consignment.agent_id (+)
GROUP BY agent.agent_id, agent.agent_name;
或decode(ddd,null,0,ddd)
判断的话用 is (not) null
SQL> SELECT agent.agent_id, agent.agent_name, TO_CHAR(SUM(decode(consignment.value, null,0,consignme
nt.value))*0.02, '$999,999,999.99') "Commission (2%)"
2 FROM agent, consignment
3 WHERE agent.agent_id = consignment.agent_id (+)
4 GROUP BY agent.agent_id, agent.agent_name; AGENT_ID AGENT_NAME Commission (2%)
--------- -------------------- ----------------
1 Bob Smith $7,020.00
2 Jane Doe $3,030.00
3 Sally Atkinson $2,010.00
4 Dave Kennedy $.00我如何可以把最后一行格式化成$0.00?