就是这样 打个比喻 假设有一个地区表,有5条数据:
---------------------
id name value
100 中国 800
101 北京 200
102 上海 400
103 广州 600
104 其他 0
----------------------
现在要把 中国的value转换为其他的value,然后中国的value记录101-104value的总和
结果就是这样:
---------------------
id name value
100 中国 2000
101 北京 200
102 上海 400
103 广州 600
104 其他 800
----------------------
(
select 100 id ,800 value from dual
union all
select 101 id ,200 value from dual
union all
select 102 id ,400 value from dual
union all
select 103 id ,600 value from dual
union all
select 104 id ,0 value from dual
)
select 100 id,sum(value) from t
union all
select decode(id,100,104,id),value from t where id<>104 order by id
SELECT T.ID,
DECODE(T.ID,
104,
(SELECT VALUE FROM TABLE WHERE ID = 100) VALUE,
100,
(SELECT SUM(VALUE) FROM TABLE WHERE ID <> 104),
T.VALUE)
FROM TABLE T
with t as ( select 100 id ,800 value from dual
union ALL select 101 id ,200 value from dual
union ALL select 102 id ,400 value from dual
union ALL select 103 id ,600 value from dual
union ALL select 104 id ,0 value from dual )
SELECT T.ID,
DECODE(T.ID,
104,
(SELECT VALUE FROM T WHERE ID = 100),
100,
(SELECT SUM(VALUE) FROM T WHERE ID <> 104),
T.VALUE)
FROM T T
with t as ( select 100 id ,800 value from dual
union ALL select 101 id ,200 value from dual
union ALL select 102 id ,400 value from dual
union ALL select 103 id ,600 value from dual
union ALL select 104 id ,0 value from dual )
SELECT T.ID,
DECODE(T.ID,
104,
(SELECT VALUE FROM T WHERE ID = 100),
100,
(SELECT SUM(VALUE) FROM T WHERE ID <> 104),
T.VALUE)
FROM T T对 就是这个意思