主表 A 中,对应B表有多条数据 通过B表的mid 与A表关联。B表有个字段state,只有state全为1时才将A表中的state 设为1.
结构基本如下:
A
ID state
1 0
B
ID MID state
1 1 1
2 1 1
3 1 1
最后的结果需要。
AID Astate
1 1
一条sql可以实现吗
结构基本如下:
A
ID state
1 0
B
ID MID state
1 1 1
2 1 1
3 1 1
最后的结果需要。
AID Astate
1 1
一条sql可以实现吗
解决方案 »
- 关于update效率的一个很简单的问题 高手进来看看
- 安装Oracle参数NLS_NCHAR_CHARACTERSET在哪设置
- 根据sysdate查数据?
- 求Oracle资料,提供者人人有分拿
- oracle服务的dos启动方式
- Oracle表中的空格怎么处理?
- 如何获取ORACLE的树中的 章节 的信息
- 在PL/SQL中,怎样判断一次查询的结果是否为空?
- char 到 number 的数据类型转换问题,急~在线等!谢谢各位大虾了*_*
- oracle的奇怪的问题:ORA-01033: ORACLE initialization or shutdown in progress
- windows PLSQL连接linux版Oracle10g 产生的问题 insufficient privileges
- 请教 在oracle10g中的审计问题
insert into a values(1,0)create table b (id int,mid int,state int)
insert into b values(1,1,1)
insert into b values(2,1,1)
insert into b values(3,1,1)
update a set state=1
from a
where exists(select 1 from b where a.id=b.mid and state=1)select * from a
id state
1 1
SET a.state =
(SELECT MIN(nvl(b.state, 0)) FROM b WHERE b.mid = a.id)
WHERE EXISTS (SELECT 1 FROM b WHERE b.mid = a.id);
UPDATE a
SET a.state =
(SELECT case when COUNT(1)>0 then 0 else 1 end
FROM b
WHERE b.mid = a.id AND
a.id != 1)
WHERE EXISTS (SELECT 1 FROM b WHERE b.mid = a.id);
--没看清楚
SQL> edi
已写入 file afiedt.buf 1 select a.id,nullif(b.state,a.state) Astate
2 from a,b
3 where a.id=b.mid
4* group by a.id,nullif(b.state,a.state)
SQL> / ID ASTATE
---------- ----------
1 1
SQL> edi
已写入 file afiedt.buf 1 select a.id,nullif(state1,a.state) Astate
2 from a,(select mid,min(b.state) state1 from b group by mid having min(b.state)=1) b
3* where a.id=b.mid
SQL> / ID ASTATE
---------- ----------
1 1
insert into t1 select 1,0 from dual;
insert into t1 select 2,0 from dual;
create table t2(id int,mid int,state int);
insert into t2 select 1,1,1 from dual;
insert into t2 select 2,1,1 from dual;
insert into t2 select 3,1,1 from dual;
insert into t2 select 1,2,1 from dual;
insert into t2 select 2,2,1 from dual;
insert into t2 select 3,2,0 from dual;update t1 a set state=1
where not exists(select 1 from t2 b where a.id=b.mid and b.state<>1)
with a as(
select 1 id, 0 state from dual
union all
select 2 id, 0 state from dual
),
b as(
select 1 id, 1 MID,1 state from dual
union all
select 2 id, 1 MID,1 state from dual
union all
select 3 id, 1 MID,1 state from dual
union all
select 4 id, 2 MID,0 state from dual
union all
select 5 id, 2 MID,1 state from dual
)
select a.id,nullif(state1,a.state) Astate
from a,(select mid,min(b.state) state1 from b group by mid having min(b.state)=1) b
where a.id=b.mid
create table t1(id int,state int);
insert into t1 select 1,0 from dual;
insert into t1 select 2,0 from dual;
create table t2(id int,mid int,state int);
insert into t2 select 1,1,1 from dual;
insert into t2 select 2,1,1 from dual;
insert into t2 select 3,1,1 from dual;
insert into t2 select 1,2,1 from dual;
insert into t2 select 2,2,1 from dual;
insert into t2 select 3,2,0 from dual;select * from t1 a
where not exists(select 1 from t2 b where a.id=b.mid and b.state<>1)