如下数据
ID CI_1 LA_1 CI_2 LA_2 times
1 30573 30625 25243 30625 2
2 30573 30625 32043 30625 2
3 22780 30625 30573 30625 2
4 30573 30625 25243 30625 2
5 25246 30625 30573 30625 2
我先现在想要只看30573这个,不区分是CI_1、CI_2只要包含这个就可以了(同一个CI对应的LA一致),
需要的结果如下:
30573 30625 25243 30625 30
30573 30625 32043 30625 14
但我现在group by 之后是如下:
30573 30625 32043 30625 4
32043 30625 30573 30625 10
25243 30625 30573 30625 18
30573 30625 25243 30625 12
请问我如何汇总?谢谢,在线等附:表结构
Create table TEST
(
ID number(15,0),
CI_1 number(15,0),
LA_1 number(15,0),
CI_2 number(15,0),
LA_2 number(15,0),
Times number(15,0)
);
ID CI_1 LA_1 CI_2 LA_2 times
1 30573 30625 25243 30625 2
2 30573 30625 32043 30625 2
3 22780 30625 30573 30625 2
4 30573 30625 25243 30625 2
5 25246 30625 30573 30625 2
我先现在想要只看30573这个,不区分是CI_1、CI_2只要包含这个就可以了(同一个CI对应的LA一致),
需要的结果如下:
30573 30625 25243 30625 30
30573 30625 32043 30625 14
但我现在group by 之后是如下:
30573 30625 32043 30625 4
32043 30625 30573 30625 10
25243 30625 30573 30625 18
30573 30625 25243 30625 12
请问我如何汇总?谢谢,在线等附:表结构
Create table TEST
(
ID number(15,0),
CI_1 number(15,0),
LA_1 number(15,0),
CI_2 number(15,0),
LA_2 number(15,0),
Times number(15,0)
);
解决方案 »
- 求sql
- 参数log_archive_start与语句alter system archive log start的区别?
- 关于查询中的时间函数TO_date OR TO_char
- oracle10g
- 将查询出的数据添加到另一张表的主键问题
- 用Select生成新表不行,郁闷ing
- 非常复杂的SQL语句,邀高手优化.我在线测试优化结果.并公布之,分数不是问题.
- initialization error:Could not locate OCI dll
- 请教!!!
- [讨论]oracle的安装方法与cpu有关系吗?(回复都有分)
- 在plsql中,使用绑定变量的方法除了动态sql外,还有别的方式吗
- 在oracle中关于rownum和count一起使用时出现的问题
你先GROUP BY 然后再去判断条件select select CI_1 , LA_1 ,CI_2 , LA_2 , times
from (
select CI_1 , LA_1 ,CI_2 , LA_2 ,count(*) as times
from TEST
group by CI_1 , LA_1 ,CI_2 , LA_2
)
where CI_1 ='30573' or CI_2 ='30573'
select CI_1 , LA_1 ,CI_2 , LA_2 , sum(times)
from ( select CI_1 , LA_1 ,CI_2 , LA_2 , sum(times) from TEST
where CI_1 = '30573' group by CI_1 , LA_1 ,CI_2 , LA_2
union all
select CI_2 as CI_1 , LA_2 as LA_1 , CI_1 as CI_2 , LA_1 asLA_2 , sum(times) from TEST
where CI_2 = '30573' group by CI_1 , LA_1 ,CI_2 , LA_2)
是这意思么,没调试,试一下吧
外面再加上一个group by CI_1 , LA_1 ,CI_2 , LA_2
漏了。
from ( select CI_1 , LA_1 ,CI_2 , LA_2 , times from TEST
where CI_1 = '30573'
union all
select CI_2 as CI_1 , LA_2 as LA_1 , CI_1 as CI_2 , LA_1 asLA_2 , times from TEST
where CI_2 = '30573' )
group by CI_1 , LA_1 ,CI_2 , LA_2
Create table TEST
(
ID number(15,0),
CI_1 number(15,0),
LA_1 number(15,0),
CI_2 number(15,0),
LA_2 number(15,0),
Times number(15,0)
)insert into TEST values (1,30573,30625,25243,30625,2);
insert into TEST values (2,30573,30625,32043,30625,2);
insert into TEST values (3,22780,30625,30573,30625,2);
insert into TEST values (4,30573,30625,25243,30625,2);
insert into TEST values (5,25246,30625,30573,30625,2);
--查询select CI_1,max(LA_1) as LA_1 ,CI_2,max(LA_2) as LA_2,sum(TIMES) as TIMES from (
select * from TEST where CI_1=30573 )
group by CI_1,CI_2
30573 30625 32043 30625 2
30573 30625 25243 30625 4
需要的结果如下:
test
-------------------------------------------
CI_1 LA_1 CI_2 LA_2 times
30573 30625 25243 30625 30
30573 30625 32043 30625 14
也就是说与LA_1 LA_2无关啦。不妨降低一下复杂度。Create table TEST
(
ID varchar2(10),
CI_1 varchar2(15),
CI_2 varchar2(15),
Times number(15,0)
)insert into TEST values (1,11111,22222,3);
insert into TEST values (2,22222,11111,3);
insert into TEST values (3,'aaaaa','bbbbb',2);
insert into TEST values (4,'bbbbb','aaaaa',2);
insert into TEST values (5,'bbbbb','aaaaa',2);而对ci_1值为'aaaaa'的进行统计。
要求得到的结果:
aaaaa , bbbbb , 6 (应该是LZ的意思吧!)执行SQL:
select ci_1,ci_2,sum(times) from (select t1.ci_1,t1.ci_2,t1.times from test t1,test t2
where t1.CI_1= 'aaaaa'
and ( t1.CI_2 = t2.CI_2 and t1.id = t2.id
)union allselect t2.ci_2 ci_1, t2.ci_1 ci_2,t2.times from test t1, test t2
where t1.ci_1 = 'aaaaa'
and t1.ci_1 = t2.ci_2
and t1.ci_2 = t2.ci_1) xxxxx
group by ci_1,ci_2
得结果:
CI_1 CI_2 TIMES
aaaaa bbbbb 6把数据替换成你要的形式就OK乐。另外可以考虑下如何优化这个SQL。期待其他解法。。接分