难道非得采用select * from (SELECT NHBH, (SELECT COUNT(*) FROM table_b WHERE columnA= table_a.columnA) AS AA FROM table_a) aa>0吗?
marx (2011-1-23 23:39:10)
你的问题太模糊了，你是不是要计算table_b.A1中包含table_a.A1的值的列数？
select count (*) from table_b where table_b.a1 in (select table_a.a1 from table_a );
ITDonald (2011-1-27 04:07:01)
这个不能用join吗？
mazarine (2011-2-14 21:57:30)
Oracle现在还不支持这样的写法。
其实换种其他的写法也能实现你的要求，例如：
1.用exist 关键字：
SELECT NHBH FROM table_a
WHERE exist (SELECT 1 FROM table_b WHERE columnA= table_a.columnA)
2.用group：
SELECT NHBH, count(*) AS AA
FROM table_a a, table_b b
WHERE a.columnA = b.columnA(+)
group by a.NHBH
having count(*) > 1
3.用in关键字：
sql语句见3楼。
等等
gavinhuang (2011-3-07 21:59:22)
分组having就可以了
shaier (2011-3-16 15:21:41)
可能是我没表达清楚我的问题,现在我们系统中到处都是这样的查询
SELECT NHBH, count(*) AS AA
FROM table_a a,table_b b
WHERE a. columnA= b.columnA
group by a.nhbh
having count(*) > 0
最新回复
shaier (2011-1-13 11:40:31)
marx (2011-1-23 23:39:10)
select count (*) from table_b where table_b.a1 in (select table_a.a1 from table_a );
ITDonald (2011-1-27 04:07:01)
mazarine (2011-2-14 21:57:30)
其实换种其他的写法也能实现你的要求，例如：
1.用exist 关键字：
SELECT NHBH FROM table_a
WHERE exist (SELECT 1 FROM table_b WHERE columnA= table_a.columnA)
2.用group：
SELECT NHBH, count(*) AS AA
FROM table_a a, table_b b
WHERE a.columnA = b.columnA(+)
group by a.NHBH
having count(*) > 1
3.用in关键字：
sql语句见3楼。
等等
gavinhuang (2011-3-07 21:59:22)
shaier (2011-3-16 15:21:41)
SELECT NHBH, count(*) AS AA
FROM table_a a,table_b b
WHERE a. columnA= b.columnA
group by a.nhbh
having count(*) > 0
having这种是可以,但是如果group by 列比较多,性能应该不如我上面的子查询吧?