oracle
insert table1
select name ,count(*)
from ...
group by type
select ,case type1 == value1 , case type2 == value2 , case type3 = value3 .........這里也要寫死的
from table2 , table1
導入到臨時表 1:N 然后拼接出一張表 寫1次固定代碼
還沒想到更好的...................????????
其他方式
select ......
from a
( select * ,count from ... where type1 ) t1
( select * ,count from ... where type2 ) t2
.
.
.
.
有幾種就要寫幾種.... 但是速度如何呢? 要發動N次的 index count(*) 寫2次固定代碼
1-1-1-N
可能因為 不是3,6 整天記錄都不輸出 問題是怎么解決 .
select
a.seatid_ "xwh" , --席位
b.exchgcode_ "jys" , --交易所
b.holderacc_ "khh", --客戶號
c.password_ "khmm" , -- 密碼
count(*) "num" ,
case
when count(*) = 0 then 'N' --不讀回報 沒有未完成的委托
else 'Y' -- 有未完成的委托
end "qbh" -- 取回報
from acc_seat a, acc_holder2seat b , acc_holder c ,dl_entrust d
where a.exchgcode_ = v_exchgcode
and a.seatid_ = v_seatid
and a.exchgcode_ = b.exchgcode_
and a.seatid_ = b.seatid_
and b.exchgcode_ =c.exchgcode_
and b.holderacc_ =c.holderacc_
and d.exchgcode_ = a.exchgcode_
and d.seatid_ = a.seatid_
and d.holderacc_ = b.holderacc_
and d.entstatus_ not in (3,6)
group by a.seatid_ , --席位
b.exchgcode_ , --交易所
b.holderacc_ , --客戶號
c.password_ ; -- 密碼
select
a.seatid_ "xwh" , --席位
b.exchgcode_ "jys" , --交易所
b.holderacc_ "khh", --客戶號
c.password_ "khmm" , -- 密碼
nvl( (
select count(*)
from dl_entrust d
where 1=1 and d.exchgcode_ = a.exchgcode_
and d.seatid_ = a.seatid_
and d.holderacc_ = b.holderacc_
and d.entstatus_ not in (3,6)
),0 ) "num"
from acc_seat a, acc_holder2seat b , acc_holder c
where a.exchgcode_ = &v_exchgcode
and a.seatid_ = &v_seatid
and a.exchgcode_ = b.exchgcode_
and a.seatid_ = b.seatid_
and b.exchgcode_ =c.exchgcode_
and b.holderacc_ =c.holderacc_