大家来写SQL语句(Oralce语法)，个人认为有点超高难

[猎人66]

有如下四句SQL：
select fa,sum(fb) from tablex where (条件1.......) group by fa
select fa,sum(fb) from tablex where (条件2.......) group by fa
select fa,sum(fb) from tablex where (条件3.......) group by fa
select fa,sum(fb) from tablex where (条件4.......) group by fa

上述四句SQL只有条件不同，其于部分相同
请教：哪们大虾能把们合并成一句SQL?即返回的数据集中要求四个字段fa,sum(fb)1,sum(fb)2,sum(fb)3,sum(fb)4

这是不是很简单，可我等菜鸟苦想了一个晚上也未想出~~~~

[H999]

select fa,sum(fb) from tablex where (条件1.......) group by fa
union
select fa,sum(fb) from tablex where (条件2.......) group by fa
union
select fa,sum(fb) from tablex where (条件3.......) group by fa
union
select fa,sum(fb) from tablex where (条件4.......) group by fa

[victorlee_lq]

select fa, (select sum(fb) from tablex where fa = 'Jan' group by fa),
(select sum(fb) from tablex where fa = 'Feb' group by fa),
(select sum(fb) from tablex where fa = 'Mar' group by fa) from tablex

[H999]

在一个记录集中

select t1.fa,sum(t1.fb),sum(t2.fb),sum(t3.fb),sum(t4.fb)
from tablex t1 , tablex t2 , tablex t3 , tablex t4
where (条件1.......)
and (条件2.......)
and (条件3.......)
and (条件4.......)
and (关联条件...具体看表结构)
group by t1.fa

[victorlee_lq]

select fa, (select sum(fb) from tablex where fa = 'Jan' group by fa),
(select sum(fb) from tablex where fa = 'Feb' group by fa),
(select sum(fb) from tablex where fa = 'Mar' group by fa) from tablex

[victorlee_lq]

试一下吧,是我请教的一位高人得来的

[victorlee_lq]

可以吗?

[猎人66]

谢谢victorlee_lq(victor)，我试一下，
成功了马上给你分

[JeasonZhao]

select
a.fa,a.data,b.fa,b.data...
from
(select fa,sum(fb) data from tablex where (条件1.......) group by fa) a,
(select fa,sum(fb) data from tablex where (条件2.......) group by fa) b,
(select fa,sum(fb) data from tablex where (条件3.......) group by fa) c,
(select fa,sum(fb) data from tablex where (条件4.......) group by fa) d,
group by
a.fa,a.data,b.fa,b.data...

[JeasonZhao]

呵呵,对不住!!!产生交叉集合啦

[猎人66]

to victorlee_lq(victor):好像不行，因为我的每条SQL都产生多条记录，
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

是不是这个原因?

[猎人66]

to JeasonZhao(Jeason):谢谢大虾，这样应该不行吧。
to victorlee_lq(victor):我也想过用存储过程，但也不知如何实现，
你能讲讲思路，或者帮小弟写个例子~~~~

[victorlee_lq]

OK, 是有很多记录的,你要进行过滤才是最后的呀.

OK,让我来试一下

[victorlee_lq]

select distinct fa, (select sum(fb) from tablex where fa = 'Jan' group by fa),
(select sum(fb) from tablex where fa = 'Feb' group by fa),
(select sum(fb) from tablex where fa = 'Mar' group by fa) from tablex
where fa = 'Jan'

我试过了上面的是可以的,只是 后面的where是你要任意取fa中的一行就可以了;注意前面加了distinct哟.......
Good Luck

[猎人66]

谢谢老大，期待中............

[猎人66]

谢谢，我试一试先~~~~~~~~

[victorlee_lq]

另外,如果是用存储过程的话问题变得就非常简单了;并且执行的效率也会高的;
每一个Select给一个CURSOR,然后,取出CURSOR中的Sum列的数据来;最后并到一个SELECT中输出就可以了

[猎人66]

还是同样的错误啊：
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

题目中的每条SQL都产生了不只一条记录，
而要求返回的数据集也不只一条记录

[victorlee_lq]

select distinct fa, (select sum(fb) from tablex where fa = 'Jan' group by fa),
(select sum(fb) from tablex where fa = 'Feb' group by fa),
(select sum(fb) from tablex where fa = 'Mar' group by fa) from tablex
where fa = 'Jan'

我明白了,是不是你的GROUP BY和WHERE控制的不对呀,你来看,如果说
(select sum(fb) from tablex where fa = 'Feb' group by fa),
的返回不是一条记录就会出错的......

[victorlee_lq]

你有MSN或QQ吗?

[猎人66]

我的QQ:81547289
请老大指点才是