t1表
a b
a1 1
a1 2
a1 3
a1 3
a2 1
a2 1
a2 3t2表
b c
1 b1
2 b2
3 b3需要这样的结果
a b1 b2 b3
a1 1 1 2
a2 2 0 1即按照t1表里面a字段来统计b值出现的次数
t1表里面b字段和t2表的b字段相关联
a b
a1 1
a1 2
a1 3
a1 3
a2 1
a2 1
a2 3t2表
b c
1 b1
2 b2
3 b3需要这样的结果
a b1 b2 b3
a1 1 1 2
a2 2 0 1即按照t1表里面a字段来统计b值出现的次数
t1表里面b字段和t2表的b字段相关联
解决方案 »
- oracle导入出错,求帮助。百度的方法不顶用 IMP-00017: 由于 ORACLE 错误 20000;IMP-00003: 遇到 ORACLE 错误
- oracle命令如何用一个字符替换指定位置的字符,帖子中详细描述
- 很奇怪的TO_DATE
- ORA-04091: 表 发生了变化, 触发器/函数不能读它
- Oracle 10.1.0 regexp_substr问题
- 有没有类似VFP中的表单的内容,纯粹使用ORACLE创建一个管理软件?
- TNS 无法处理服务名 怎么解决?
- oracle中oci编程的问题
- sql plus中用@命令时如何让其不执行缓冲区内容?
- 关于一个排序求第一条SQL语句的写法..
- 超级怪异的问题,真正高手请进,ORA-00600 问题
- oracle 如何判断系统时间和数据表中的时间相差多少小时???
SQL:
select max(decode(t2.c,'b1',tt.cou,0)) as b1,
max(decode(t2.c,'b2',tt.cou,0)) as b2,
max(decode(t2.c,'b3',tt.cou,0)) as b3
from (
select a,
b,
count(1) as cou
from t1
group by a,b
)tt,
t2
where tt.b = t2.b
group by tt.a;
Result: B1 B2 B3
---------- ---------- ----------
1 1 2
2 0 1
-----------存储过程代码-----------------------------create or replace procedure pro_test is -- Declare the variable
sql_text varchar2(3000);
sql_b varchar2(3000);
count_b number(5);
count_max number(5);
r_b t2%ROWTYPE;
cursor c_b is select * from t2;
begin
count_b := 1;
sql_b := '';
sql_text := 'select ';
select count(1) into count_max from t2;
-- open cursor c_b
OPEN c_b;
LOOP
FETCH c_b INTO r_b;
EXIT WHEN c_b %NOTFOUND;
-- when the last count of table 't2'
IF count_b = count_max THEN
sql_b := sql_b || 'max(decode(c,'''||r_b.c||''',cou,0)) as '||r_b.c||'';
ELSE
sql_b := sql_b || 'max(decode(c,'''||r_b.c||''',cou,0)) as '||r_b.c||'' || ',';
END IF;
count_b := count_b + 1;
END LOOP;
sql_text := sql_text || sql_b || ' from ( select a,b,count(1) as cou from t1 group by a,b)tt,t2 where tt.b = t2.b group by tt.a';
dbms_output.put_line(sql_text);
CLOSE c_b;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-200020,'Error in table t2 b'||SQLCODE);
end pro_test;
--------------------执行结果-------------------------------SQL> set serveroutput on;
SQL> exec pro_test;select max(decode(c,'b1',cou,0)) as b1,max(decode(c,'b2',cou,0)) as b2,max(decode(c,'b3',cou,0)) as b3 from ( select a,b,count(1) as cou from t1 group by a,b)tt,t2 where tt.b = t2.b group by tt.aPL/SQL procedure successfully completedSQL> select max(decode(c,'b1',cou,0)) as b1,max(decode(c,'b2',cou,0)) as b2,max(decode(c,'b3',cou,0)) as b3 from ( select a,b,count(1) as cou from t1 group by a,b)tt,t2 where tt.b = t2.b group by tt.a; B1 B2 B3
---------- ---------- ----------
1 1 2
2 0 1