网友问我:
update Ta set rmb=
(
select rmb from subA where id=Ta.id
与
select rmb from subB where id=Ta.id
这两个子查询中rmb较小的一个
)
这个SQL该怎么写?
嗯,我们来动手做做
先创建3张表
Ta、subA、subB 他们结构一样
SQL> desc Ta;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
RMB NUMBER
SQL> select * from Ta;
ID RMB
---------- ----------
1 8
SQL> select * from subA;
ID RMB
---------- ----------
1 10
SQL> select * from subB;
ID RMB
---------- ----------
1 20
按要求,更新后 Ta中的RMB应该是 10!
SQL> update Ta set rmb=(
select least ((select a.rmb from subA a,Ta where a.id=Ta.id),(select b.rmb from subB b,Ta where b.id=Ta.id)) from dual
);
1 row updated.
SQL> select * from Ta;
ID RMB
---------- ----------
1 10 //呵呵,说明成功了!
顺便想想,如果要把rmb最大的那个值赋给Ta的rmb怎么做呢?对,用greatest函数!
SQL> update Ta set rmb=
(
select greatest((select a.rmb from subA a,Ta where a.id=Ta.id),(select b.rmb from subB b,Ta where b.id=Ta.id)) from dual
);
1 row updated.
SQL> select * from Ta;
ID RMB
---------- ----------
1 20
关于greatest函数的说明:
greatest( expr1, expr2, ... expr_n )
expr1, expr2, . expr_n 可以值也可以是函数.
函数功能: 取得值最大值。
影响版本: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
例子:
greatest(2, 5, 12, 3) would return 12
greatest('2', '5', '12', '3') would return '5'
greatest('apples', 'oranges', 'bananas') would return 'oranges'
greatest('apples', 'applis', 'applas') would return 'applis'