现在我有两张表.
表A
字段A1 字段A2
AAA 1001
BBB 1002
CCC 1003
DDD 1004
表B
字段B1 字段B2
KKKK1 1002
KKKK2 1003
KKKK3 1002
KKKK4 1001
KKKK5 1002
最后的列表需要打印出
字段A1在表B中的个数AAA 1
BBB 3
CCC 1
DDD 0
GROUP BY 在两张表的时候没法用了:(
解决方案 »
- 用plsql的工具条可以建dblink,用命令行建不了dblink,这是怎么回事?
- 执行netca,爆出错误。请高手指点
- 急!!!SQL里的一个莫名其妙的问题_______在线等
- oracle时间触发器
- pl/sql中提示"ORA-12541:TNS 没有监听器" 但tns listener 可以正常启动! (急,在线等待)
- 请教在linux中oracle的启动文件的问题
- Oracle中如何分解字符串
- oracle的自带help
- insert select 的问题 最高可给到400分
- Oracle 9i服务器端可以在win2000专业版装吗
- 请高手,求一条sql语句!顶即给分!
- 20分求一條簡單語句,虛心請教各位,在線等。。。謝謝了
from 表A a left join 表B b on a.字段A2 = b.字段B2
group by a.字段A1
AAA 0
BBB 0
CCC 0
DDD 0
table1 D,
(
select a1,count(*) cnt from
table1 A,table2 B
where a.a2=b.b2
group by a1
) C
where c.a1(+)=d.a1
demo@XIAOXIAO>select *from ta;A1 A2
---------- ----------
A 1001
B 1002
C 1003
D 1004demo@XIAOXIAO>select *from tb;B1 B2
---------- ----------
KKKK1 1002
KKKK2 1003
KKKK3 1002
KKKK4 1001
KKKK5 1002demo@XIAOXIAO>select a.A1, count(b.b1)
2 from ta a left join tb b on a.A2 = b.B2
3 group by a.A1
4 /A1 COUNT(B.B1)
---------- -----------
A 1
B 3
C 1
D 0版本信息如下:
demo@XIAOXIAO>select *from v$version;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production很困惑的说
SQL> select * from tmp
2 /A1 A2
---------- ---------
A 1001
B 1002
C 1003
D 1004SQL> select * from tmp1
2 /B1 B2
---------- ---------
KKKK1 1002
KKKK2 1003
KKKK3 1002
KKKK4 1001
KKKK5 1002SQL> select a.A1, count(b.b1)
2 from tmp a, tmp1 b
3 where a.A2 = b.B2(+)
4 group by a.A1
5 /A1 COUNT(B.B1)
---------- -----------
A 1
B 3
C 1
D 0SQL> select * from v$version
2 /BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
from t_a t,
(select a.b2 as b2,count(a.b2) as abc from t_b a group by a.b2) t2
where t.a2 = t2.b2这个就OK了
2 from test_a a;A1 COUNT
-------------------- ----------
AAA 1
BBB 3
CCC 1
DDD 0