Question

oracle 给统计多个字段的数据结果 求之间的百分比

SELECT AIR_DATE,COUNT(CASE WHEN AIR_DATE = '2013-10-21' THEN 1 ELSE 0 END) CountDayAir,COUNT(CASE WHEN TO_DATE(SEND_PLAN_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')-TO_DATE(ARR_ACT_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')<0 and AIR_DATE = '2013-10-21' THEN 1 ELSE 0 END) CountOntimeAir,SUM(CASE WHEN TO_DATE(SEND_PLAN_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')-TO_DATE(ARR_ACT_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')<0 and AIR_DATE = '2013-10-21' THEN 1 ELS
E 0 END) CountDelayed, round((CountDelayed/CountOntimeAir)*100,2)||'%' percent FROM AIR_INFO WHERE AIR_DATE = '2013-10-21' group by AIR_DATE；
为啥给COUNT 求出数据的 起的别名不能用在求百分比里呢？ ORACLE 老是提示ORA-00904: "COUNTONTIMEAIR": 标识符无效。
我是想根据count 出来的3个结果来求它们之间的百分比，求解答

Answer 1

round((CountDelayed/CountOntimeAir)*100,2) 这个时候是还不能用CountDelayed,CountOntimeAir字段的,要在外面加一层;

COUNT(CASE WHEN AIR_DATE = '2013-10-21' THEN 1 ELSE 0 END) 这里你的本意是用sum的吧?count可能不是你想要的结果.

Answer 2

with air_xxx as
(SELECT AIR_DATE,COUNT(CASE WHEN AIR_DATE = '2013-10-21' THEN 1 ELSE 0 END) CountDayAir,COUNT(CASE WHEN TO_DATE(SEND_PLAN_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')-TO_DATE(ARR_ACT_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')<0 and AIR_DATE = '2013-10-21' THEN 1 ELSE 0 END) CountOntimeAir,SUM(CASE WHEN TO_DATE(SEND_PLAN_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')-TO_DATE(ARR_ACT_ARR_TIME,'YYYY-MM-DD HH24:MI:SS')<0 and AIR_DATE = '2013-10-21' THEN 1 ELS
E 0 END) CountDelayed, round((CountDelayed/CountOntimeAir)*100,2)||'%' percent FROM AIR_INFO WHERE AIR_DATE = '2013-10-21' group by AIR_DATE)
select round((CountDelayed/CountOntimeAir)*100,2)||'%' percent from air_xxx
;

追问

直接贴你的语句么？怎么看起来感觉怪怪的呢， 贴进去后报这样的错误： ORA-00905: 缺失关键字