主表 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可以实现吗
解决方案 »
- 请各位帮看看这个问题怎样解决
- pl/sql调试时遇到的一个问题
- create index..(bsm asc) 与create index ..(bsm)的区别
- 关于oracle索引问题
- 关于数据库性能的问题?
- 为何在PRO*C/C++里调用函数出错啊?
- Oracle数据库最近频繁的会象死机一样,查询某些表无任何反应,该如何处理啊?
- 在Win2K下怎样安装Oracle734??急!!在线等待
- 关于存储过程动态执行语句返回结果集!
- 声明变量太多了?奇怪~~
- 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)