给个建议你试试,先把union的结果作为一个结果集,再对结果集做order by ,最后创建表。我没有想明白的是,你这是建表,为什么一定要用order by呢?即使表中的数据无序,在提取表中的数据时,再用order by 也不迟呀
再加一层查询 CREATE TABLE T_426D05262562785( TMPJLBH,JYCS,KDJ,XSSL,XSJE) AS ( select * from ( select T.TMPJLBH,Count(distinct B.SKTNO||to_char(B.JLBH)) JYCS,round( sum(C.XSJE)/count(distinct B.SKTNO||to_char(B.JLBH)),2) KDJ ,sum(C.XSSL) XSSL,sum(C.XSJE) XSJE from SKTXSJL B , T_426C05262562785 T,SKTXSJLC C where T.BEGIN_SJ<B.JYSJ and B.JYSJ <=T.END_SJ and B.JLBH=C.JLBH and B.SKTNO=C.SKTNO and to_number(substr(B.SKTNO,1,2))=1 group by T.TMPJLBH union ( select T.TMPJLBH,Count(distinct B.SKTNO||to_char(B.JLBH)) JYCS,round( sum(C.XSJE)/count(distinct B.SKTNO||to_char(B.JLBH)),2) KDJ ,sum(C.XSSL) XSSL,sum(C.XSJE) XSJE from XSJL B , T_426C05262562785 T,XSJLC C where T.BEGIN_SJ<B.JYSJ and B.JYSJ <=T.END_SJ and B.JLBH=C.JLBH and B.SKTNO=C.SKTNO and to_number(substr(B.SKTNO,1,2))=1 group by T.TMPJLBH ) ) order by tmpjbh )
一般用union all, 因为union会显示重复记录。
在加一层查询才行。要么不知道你这个order by 排的哪。
可以将union 后的结果作为一个结果集,然后在对这个结果集进行order by,不然无法判断是对那些数据的哪些字段进行order by。 而且使用union 时应注意: Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序; Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
AS
(
select * from
(
select T.TMPJLBH,Count(distinct B.SKTNO||to_char(B.JLBH)) JYCS,round( sum(C.XSJE)/count(distinct B.SKTNO||to_char(B.JLBH)),2) KDJ ,sum(C.XSSL) XSSL,sum(C.XSJE) XSJE
from SKTXSJL B , T_426C05262562785 T,SKTXSJLC C
where T.BEGIN_SJ<B.JYSJ
and B.JYSJ <=T.END_SJ
and B.JLBH=C.JLBH and B.SKTNO=C.SKTNO
and to_number(substr(B.SKTNO,1,2))=1
group by T.TMPJLBH
union
(
select T.TMPJLBH,Count(distinct B.SKTNO||to_char(B.JLBH)) JYCS,round( sum(C.XSJE)/count(distinct B.SKTNO||to_char(B.JLBH)),2) KDJ ,sum(C.XSSL) XSSL,sum(C.XSJE) XSJE
from XSJL B , T_426C05262562785 T,XSJLC C
where T.BEGIN_SJ<B.JYSJ
and B.JYSJ <=T.END_SJ
and B.JLBH=C.JLBH and B.SKTNO=C.SKTNO
and to_number(substr(B.SKTNO,1,2))=1
group by T.TMPJLBH
)
) order by tmpjbh
)
而且使用union 时应注意:
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;