要求:
求A.NAME,满足条件
A.NEME=B.NAME
B.ID=C.ID
C.NEW='E' 并且COUNT(*)=2结果:NEME
--------------
AAA
DDD
==========================================================================================
表A:NAME
-------------------------
AAA
BBB
CCC
DDD==========================================================================================表B:ID NAME
-----------------------------------
111 AAA
222 BBB
333 CCC
444 DDD
==========================================================================================
表CID | CLASS | NEW
-----------------------------------
111 | I | E
111 | T | T
111 | O | E
222 | I | E
222 | O | E
333 | T | T
333 | O | E
444 | T | T
| |
求A.NAME,满足条件
A.NEME=B.NAME
B.ID=C.ID
C.NEW='E' 并且COUNT(*)=2结果:NEME
--------------
AAA
DDD
==========================================================================================
表A:NAME
-------------------------
AAA
BBB
CCC
DDD==========================================================================================表B:ID NAME
-----------------------------------
111 AAA
222 BBB
333 CCC
444 DDD
==========================================================================================
表CID | CLASS | NEW
-----------------------------------
111 | I | E
111 | T | T
111 | O | E
222 | I | E
222 | O | E
333 | T | T
333 | O | E
444 | T | T
| |
解决方案 »
- 重复记录保留一条的初级问题
- 未找到游标上下文
- 为什么监听器在服务里面能启动,在命令行里就不能启动了????
- 一个汇总的问题,sql如何来写
- 获得表里名称、类别相同,价格不同的价格平均值的前后10%的数据,SQL语句怎么写啊
- 请问oracle高手:游标 和 delete 语句后跟条件 那个快?
- 一个sql语言,请大家帮忙!
- 请问怎样能很完全的删除ORACLE
- 见数据库时有个初始的连接,,extproc_conection_data ,使用来做什么的,,?
- 请问怎么讲sql存储过程改成oracle的啊?我是一点不会oracle
- windows下如何使用rman?
- oracle数据库连不上,报错输入口令: ERROR: ORA-01033: ORACLE initialization or shutdown in prog
from a , b , c
where
A.NEME=B.NAME and
B.ID=C.ID and
C.NEW='E'
group by a.name having count(1) = 2
with a as(
select 'AAA' NAME from dual
union all
select 'BBB' from dual
union all
select 'CCC' from dual
union all
select 'DDD' from dual),
b as(
select 111 ID,'AAA' NAME from dual
union all
select 222,'BBB' from dual
union all
select 333,'CCC' from dual
union all
select 444,'DDD' from dual),
c as(
select 111 ID ,'I' CLASS,'E' NEW from dual
union all
select 111,'T','T' from dual
union all
select 111,'O','E' from dual
union all
select 222,'I','E' from dual
union all
select 222,'O','E' from dual
union all
select 333,'T','T' from dual
union all
select 333,'O','E' from dual
union all
select 444,'T','T' from dual)
select a.name from a,b,c
where a.name=b.name and b.id=c.id
and c.new='E'
group by a.name
having count(*)=2NAM
---
BBB
AAA
select A.NAME from a,b
where a.name=b.name and
exists
(select 1 from c
where b.id=c.id and c.new='E'
group by c.id
having count(id)=2)
--------------
AAA
BBB