iT邦幫忙

0

oracle sum

請問大大為什麼

select sum(a),sum(b),sum(c)
form (select a,b,c from table_a where kk > 12 )

select *
form (select sum(a),sum(b),sum(c) from table_a where kk > 12)

得到的值不一樣 ?

2 個回答

0
暐翰
iT邦大師 1 級 ‧ 2019-01-07 14:41:00

兩個要一樣才對,有資料佐證?


以下是測試demo

with table_a as (
    select 1 a ,1 b ,1 c, 11 kk from dual union all
    select 1 a ,1 b ,1 c, 12 kk from dual union all
    select 1 a ,1 b ,null c, 13 kk from dual union all
    select 1 a ,1 b ,1 c, 14 kk from dual
)
select sum(a),sum(b),sum(c)
from (select a,b,c from table_a where kk > 12 );
-- Result : 2,2,1


with table_a as (
    select 1 a ,1 b ,1 c, 11 kk from dual union all
    select 1 a ,1 b ,1 c, 12 kk from dual union all
    select 1 a ,1 b ,null c, 13 kk from dual union all
    select 1 a ,1 b ,1 c, 14 kk from dual
)
select *
from (select sum(a),sum(b),sum(c) from table_a where kk > 12)
-- Result : 2,2,1
joy036 iT邦研究生 4 級 ‧ 2019-01-09 15:25:24 檢舉

資料太多無法提供/images/emoticon/emoticon02.gif


0
fuzzylee1688
iT邦新手 2 級 ‧ 2019-01-09 08:48:52

select *
form (select sum(a),sum(b),sum(c) from table_a where kk > 12)
這個會錯吧?? subQuery 沒給別名, 感覺語法錯誤..
我會建議改成這樣

select DATA.*
form (
select sum(a) SUMa, sum(b) SUMb, sum(c) SUMc 
  from table_a 
where kk > 12) DATA
joy036 iT邦研究生 4 級 ‧ 2019-01-09 15:26:16 檢舉

語法? 可以執行耶 !

我要發表回答

立即登入回答