aTable
person ¦ key
----------------
a 3
a 4
b 5
b 3
b 4
c 3
c 4
要做的是,在表中查询拿有相同钥匙的人
我这样做怎么老是运行不了
select p1.person,p2.person from aTable p1, aTable p2
having
(
select ptemp1.key from aTable ptemp1
where ptemp1.person = p1.person
and exists
(
select key from aTable
where person = p2.person
)
)
and
(
select ptemp2.key from aTable ptemp2
where ptemp2.person = p1.person
and exists
(
select key from aTable
where person = p2.person
)
)
person ¦ key
----------------
a 3
a 4
b 5
b 3
b 4
c 3
c 4
要做的是,在表中查询拿有相同钥匙的人
我这样做怎么老是运行不了
select p1.person,p2.person from aTable p1, aTable p2
having
(
select ptemp1.key from aTable ptemp1
where ptemp1.person = p1.person
and exists
(
select key from aTable
where person = p2.person
)
)
and
(
select ptemp2.key from aTable ptemp2
where ptemp2.person = p1.person
and exists
(
select key from aTable
where person = p2.person
)
)
解决方案 »
- 急需oracle oajinit.exe文件
- 导出文件中出现无法识别的语句
- 求救 小弟眼拙
- 一台机子两个实例问题,另外一个实例启动不了报ORA-12505错误--急!
- 你能解释么?关于聚合
- oracle数据乱码怎么办
- powerdesigner9.5 支持oracle10g吗?
- 如果在视图中使用了自定义函数和自定义数据类型,那么在导入数据库时会报警告
- 装完WIN2000操作系统最新漏洞补丁(Windows2000-KB835732-x86-CHS.EXE后 ,oracleservice就再也启动不了了?
- ##### 简单问题,高手指点,在线等待,马上给分! #####
- linux9安装oracle出现bash:sqlplus:command not found的问题
- ORACLE配置问题 急啊!!!!
person1 | person2
-------- --------------
a c因为这两个用有相同的key
where p1.key = p2.key
不就好了?
from (select person,count(distinct key) key from a group by person) t1 inner join (select a1.person p1,count(distinct a1.key) key1,a2.person p2,count(distinct a2.key) key2
from a a1 left join a a2 on a1.key = a2.key
where a1.person <> a2.person
group by a1.person,a2.person) t2 on t1.person = t2.p1 and t1.key = t2.key1
order by 1,2SQL> select * from a;PERSON KEY
------ ---------------------------------------
a 3
a 4
b 3
b 5
c 3
c 4
d 3
d 4结果:
PERSON PERSON
------ ------
a c
a d
c a
c d
d a
d c6 rows selected只能做到这程度了!