一条SQL语句通过很多的UNION ALL拼接的 能不能再进行优化了
SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='江苏卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='优漫卡通卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='北京卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALLSELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='东方卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALLSELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='天津卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='重庆卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='安徽卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALLSELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='浙江卫视' and createtime betweento_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='广西卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL (SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='湖南卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='湖北卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='辽宁卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='东南卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='山东卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='江西卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='旅游卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='四川卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='黑龙江卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='贵州卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='河北卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='云南卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='广东卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='吉林卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='河南卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='西藏卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='甘肃卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='新疆卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='青海卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='宁夏卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='山西卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') UNION ALL SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE CHANNELNAME ='兵团卫视' and createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss')
SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT WHERE createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss') GROUP BY(频道字段);
[解决办法]
把这些频道做成一个临时表，然后两表关联查询
[解决办法]
SELECT CHANNELNAME,SUM(NVL(TIMELENGTH,0)),COUNT(XLH),COUNT(DISTINCT XLH)
FROM CHANNELRESULT
WHERE createtime >= to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss')
AND createtime <= to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss')
GROUP BY CHANNELNAME
[解决办法]
-- 通过子查询先将所有的频道列表虚拟成一个视图，然后再和原表进行join查询。SELECT NVL(SUM(TIMELENGTH),0),COUNT(XLH),COUNT(DISTINCT XLH) FROM CHANNELRESULT A, (SELECT '江苏卫视' CHANNELRESULTNAME FROM DUAL UNION ALL SELECT '优漫卡通卫视' FROM DUAL UNION ALL ...... SELECT '兵团卫视' FROM DUAL) B, WHERE A.CHANNELNAME = B.CHANNELNAME and A.createtime between to_date('2011-8-01 00:00:00','yyyy-MM-dd hh24:MI:ss') and to_date('2011-8-31 23:59:59','yyyy-MM-dd hh24:MI:ss')