问题描述

把三张表A,B,C对应列的值同时显示出来

select *
from (select sum(OUTGARBAGE) as OUTGARBAGE from sw_classroom_health a where to_char(eventdate, 'yyyy-mm-dd') between '2015-11-01' and
'2015-11-31' and classid='0021') ,
(select sum(EXERCISE) as EXERCISE from sw_class_discipline b where to_char(eventdate, 'yyyy-mm-dd') between '2015-11-01' and
'2015-11-31' and classid='0021' ),
(select sum(EQUIPMENTSOURCE) as EQUIPMENTSOURCE from sw_class_tourhall c where to_char(eventdate, 'yyyy-mm-dd') between '2015-11-01' and
'2015-11-31' and classid='0021' )

                        A,B,C表有两列列名相同，该怎么简写？

解决方案

不知道你要干什么，
SELECT * FROM
(SELECT SUM(OUTGARBAGE) AS OUTGARBAGE FROM sw_classroom_health a WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') a,
(SELECT SUM(EXERCISE) AS EXERCISE FROM sw_class_discipline b WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') b,
(SELECT SUM(EQUIPMENTSOURCE) AS EQUIPMENTSOURCE FROM sw_class_tourhall c WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') c
这样写可以查出来

解决方案二：

不知道你要干什么，
SELECT * FROM
(SELECT SUM(OUTGARBAGE) AS OUTGARBAGE FROM sw_classroom_health a WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') a,
(SELECT SUM(EXERCISE) AS EXERCISE FROM sw_class_discipline b WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') b,
(SELECT SUM(EQUIPMENTSOURCE) AS EQUIPMENTSOURCE FROM sw_class_tourhall c WHERE to_char (eventdate, 'yyyy-mm-dd') BETWEEN '2015-11-01' AND '2015-11-31' AND classid = '0021') c
这样写可以查出来

解决方案三：

没必要简写了吧。3张表又不一定能用Join方式，而且不一定比现在这样写的效果好。
真要优化的话，to_char 再进行字符串类型比较的这种改成用date来比较，将日期字符串用to_data或to_timestamp的方式转成日期来比较。
理论上日期的比较比字符串比较要快，而且不用每行数据都转换后再比较。