如何合并对一个表的两个查询结果成一个表

[churchatp1]

有表结构如下：
a(a1,a2,a3,a4)
a1可以重复为字符串型,值为'1'..'9'，a2,a3为整数型
现做如下查询，1。select sum(a2)+sum(a3) from a where a1<'6' group by a1
2.select sum(a2)-sum(a3) from a where a1>'6' group by a1

谢谢！

[Andy__Huang]

select sum(cast(a2 as numeric))+sum(cast(a3 as numeric)) from a where a1<'6' group by a1
union all
select sum(cast(a2 as numeric))-sum(cast(a3 as numeric)) from a where a1>'6' group by a1

[chiwei]

select
A=(select sum(a2)+sum(a3) from a where a1<'6' group by a1),
B=(select sum(a2)-sum(a3) from a where a1>'6' group by a1)

[churchatp1]

to 新新人类：
不行啊，连接起来就不能用order by了，我还要用order by

[churchatp1]

我也是用union的，select * from （select sum(cast(a2 as numeric))+sum(cast(a3 as numeric)),a4 from a where a1<'6' group by a1
union all
select sum(cast(a2 as numeric))-sum(cast(a3 as numeric)),a4 from a where a1>'6' group by a1
）as m order by a4
提示出错

[churchatp1]

错了，我是这样写的：
我也是用union的，select * from （select sum(cast(a2 as numeric))+sum(cast(a3 as numeric)),a1 from a where a1<'6' group by a1
union all
select sum(cast(a2 as numeric))-sum(cast(a3 as numeric)),a1 from a where a1>'6' group by a1
）as m order by a1
提示出错
oeder by不可少该怎么写，谢谢

[paoluo]

试试

select sum(cast(a2 as numeric))+sum(cast(a3 as numeric)),a1 from a where a1<'6' group by a1
union all
select sum(cast(a2 as numeric))-sum(cast(a3 as numeric)),a1 from a where a1>'6' group by a1
order by a1

[churchatp1]

这样的话我要去取他的字段怎么取啊？谢谢

[paoluo]

取它的字段？？哪个？？

[churchatp1]

我要取这个合成表的sum(cast(a2 as numeric))-sum(cast(a3 as numeric))字段，改怎么办？

[paoluo]

那为你那一列取一个别名，如下面的SUMa。

select (sum(cast(a2 as numeric))+sum(cast(a3 as numeric))) As SUMa,a1 from a where a1<'6' group by a1
union all
select (sum(cast(a2 as numeric))-sum(cast(a3 as numeric))) As SUMa,a1 from a where a1>'6' group by a1
order by a1

现在应该知道怎么取了吧。

[churchatp1]

我刚开始就是这么做的，好象不行

[paoluo]

晕，怎么会不行？？不行指什么？？得不到值还是查询不出来？？

[churchatp1]

不好意思，是我自己弄错了，日期赋值错误，所以没有结果

[631799]

select * from(
select top 100 percent sum(cast(a2 as numeric))+sum(cast(a3 as numeric)) from a where a1<'6' group by a1 order by 字段1) a
union all
select * from(
select top 100 percent sum(cast(a2 as numeric))-sum(cast(a3 as numeric)) from a where a1>'6' group by a1 order by 字段2) b

[631799]

或者：

select 1,sum(cast(a2 as numeric))+sum(cast(a3 as numeric)),a1 from a where a1<'6' group by a1
union all
select 2,sum(cast(a2 as numeric))-sum(cast(a3 as numeric)),a1 from a where a1>'6' group by a1
order by 1