你先把你的子查询写成: select ..... from .... where ....
这样的形式, 并且验证没啥问题后.再写
with (select ... from ... where... ) MySubQuery --这个别名随便取
select ... from ... MySubQuery, 其他表 where .... -- 这个是真正你要的结果.
这样的形式, 并且验证没啥问题后.再写
with (select ... from ... where... ) MySubQuery --这个别名随便取
select ... from ... MySubQuery, 其他表 where .... -- 这个是真正你要的结果.
能不能写个例子我,谢谢了
是不是这样的:
select sum (decodeif_feeinfo.fee_code,'001',if_feeinfo.tot_cost,'003',if_feeinfo.tot_cost,'038',if_feeinfo.tot_cost,'005',if_feeinfo.tot_cost,'037',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'009',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'031',if_feeinfo.tot_cost,'032',if_feeinfo.tot_cost,0)) as tot,
sum(decode (if_feeinfo.fee_code , '032', if_feeinfo.tot_cost, 0) )as b,
sum(decode (if_feeinfo.fee_code , '038', if_feeinfo.tot_cost, 0 ))as h,
sum(decode (if_feeinfo.fee_code , '001', if_feeinfo.tot_cost, 0 ))as x,
sum(decode (if_feeinfo.fee_code , '003', if_feeinfo.tot_cost, 0 ))as z,
sum(decode (if_feeinfo.fee_code , '017', if_feeinfo.tot_cost, 0 ))as f,
sum(decode (if_feeinfo.fee_code , '005', if_feeinfo.tot_cost , 0 ))as hy,
sum(decode (if_feeinfo.fee_code , '024', if_feeinfo.tot_cost, 0 ))as sy,
sum(decode (if_feeinfo.fee_code , '029', if_feeinfo.tot_cost, 0 ))as sx,
sum(decode (if_feeinfo.fee_code , '037', if_feeinfo.tot_cost , 0 ))as zl,
sum(decode (if_feeinfo.fee_code , '028', if_feeinfo.tot_cost , 0 ))as ss,
sum(decode (if_feeinfo.fee_code , '031', if_feeinfo.tot_cost , 0 ))as other
from if_feeinfo
where "II_INMAININFO"."INPATIENT_NO" = "IF_FEEINFO"."INPATIENT_NO"
(select sum(),sum(),sum(),... from if_feeinfo where ...) A, -- 查询结果
II_INMAININFO B
where A.INPATIENT_NO = B.INPATIENT_NO
tab_a as (select INPATIENT_NO,sum (decodeif_feeinfo.fee_code,'001',if_feeinfo.tot_cost,'003',if_feeinfo.tot_cost,'038',if_feeinfo.tot_cost,'005',if_feeinfo.tot_cost,'037',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'009',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'031',if_feeinfo.tot_cost,'032',if_feeinfo.tot_cost,0)) as tot,
sum(decode (if_feeinfo.fee_code , '032', if_feeinfo.tot_cost, 0) )as b,
sum(decode (if_feeinfo.fee_code , '038', if_feeinfo.tot_cost, 0 ))as h,
sum(decode (if_feeinfo.fee_code , '001', if_feeinfo.tot_cost, 0 ))as x,
sum(decode (if_feeinfo.fee_code , '003', if_feeinfo.tot_cost, 0 ))as z,
sum(decode (if_feeinfo.fee_code , '017', if_feeinfo.tot_cost, 0 ))as f,
sum(decode (if_feeinfo.fee_code , '005', if_feeinfo.tot_cost , 0 ))as hy,
sum(decode (if_feeinfo.fee_code , '024', if_feeinfo.tot_cost, 0 ))as sy,
sum(decode (if_feeinfo.fee_code , '029', if_feeinfo.tot_cost, 0 ))as sx,
sum(decode (if_feeinfo.fee_code , '037', if_feeinfo.tot_cost , 0 ))as zl,
sum(decode (if_feeinfo.fee_code , '028', if_feeinfo.tot_cost , 0 ))as ss,
sum(decode (if_feeinfo.fee_code , '031', if_feeinfo.tot_cost , 0 ))as other
from if_feeinfo
group by INPATIENT_NO
)
select *
from II_INMAININFO ,tab_a
where II_INMAININFO.INPATIENT_NO=tab_a.INPATIENT_NO
(select sum(),sum(),sum(),... from if_feeinfo ) A, -- 查询结果
II_INMAININFO B
where A.INPATIENT_NO = B.INPATIENT_NO"
A里面没有INPATIENT_NO,执行就报错。
(select INPATIENT_NO,sum(),sum(),sum(),... from if_feeinfo where ...) A, -- 查询结果
II_INMAININFO B
where A.INPATIENT_NO = B.INPATIENT_NO
select *
from (select "INPATIENT_NO",
sum(decode (if_feeinfo.fee_code,'001',if_feeinfo.tot_cost,'003',if_feeinfo.tot_cost,'038',if_feeinfo.tot_cost,'005',if_feeinfo.tot_cost,'037',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'009',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'031',if_feeinfo.tot_cost,'032',if_feeinfo.tot_cost,0)) as tot,
sum(decode (if_feeinfo.fee_code , '032', if_feeinfo.tot_cost, 0) )as b,
sum(decode (if_feeinfo.fee_code , '038', if_feeinfo.tot_cost, 0 ))as h,
sum(decode (if_feeinfo.fee_code , '001', if_feeinfo.tot_cost, 0 ))as x,
sum(decode (if_feeinfo.fee_code , '003', if_feeinfo.tot_cost, 0 ))as z,
sum(decode (if_feeinfo.fee_code , '017', if_feeinfo.tot_cost, 0 ))as f,
sum(decode (if_feeinfo.fee_code , '005', if_feeinfo.tot_cost , 0 ))as hy,
sum(decode (if_feeinfo.fee_code , '024', if_feeinfo.tot_cost, 0 ))as sy,
sum(decode (if_feeinfo.fee_code , '029', if_feeinfo.tot_cost, 0 ))as sx,
sum(decode (if_feeinfo.fee_code , '037', if_feeinfo.tot_cost , 0 ))as zl,
sum(decode (if_feeinfo.fee_code , '028', if_feeinfo.tot_cost , 0 ))as ss,
sum(decode (if_feeinfo.fee_code , '031', if_feeinfo.tot_cost , 0 ))as other
FROM IF_FEEINFO
GROUP BY "IF_FEEINFO"."INPATIENT_NO") A,
II_INMAININFO B
where A.INPATIENT_NO = B.INPATIENT_NO AND
B.in_state = '4' and
( B.OUT_DATE) >= to_date('2002-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and
( B.OUT_DATE) < to_date('2006-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
除了取出INPATIENT_NO之外把II_INMAININFO的值都给取出来,限制条件不会写。
估计偶是最菜的那种菜鸟了。
from (select "INPATIENT_NO",
sum(decode (if_feeinfo.fee_code,'001',if_feeinfo.tot_cost,'003',if_feeinfo.tot_cost,'038',if_feeinfo.tot_cost,'005',if_feeinfo.tot_cost,'037',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'009',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'031',if_feeinfo.tot_cost,'032',if_feeinfo.tot_cost,0)) as tot,
sum(decode (if_feeinfo.fee_code , '032', if_feeinfo.tot_cost, 0) )as b,
sum(decode (if_feeinfo.fee_code , '038', if_feeinfo.tot_cost, 0 ))as h,
sum(decode (if_feeinfo.fee_code , '001', if_feeinfo.tot_cost, 0 ))as x,
sum(decode (if_feeinfo.fee_code , '003', if_feeinfo.tot_cost, 0 ))as z,
sum(decode (if_feeinfo.fee_code , '017', if_feeinfo.tot_cost, 0 ))as f,
sum(decode (if_feeinfo.fee_code , '005', if_feeinfo.tot_cost , 0 ))as hy,
sum(decode (if_feeinfo.fee_code , '024', if_feeinfo.tot_cost, 0 ))as sy,
sum(decode (if_feeinfo.fee_code , '029', if_feeinfo.tot_cost, 0 ))as sx,
sum(decode (if_feeinfo.fee_code , '037', if_feeinfo.tot_cost , 0 ))as zl,
sum(decode (if_feeinfo.fee_code , '028', if_feeinfo.tot_cost , 0 ))as ss,
sum(decode (if_feeinfo.fee_code , '031', if_feeinfo.tot_cost , 0 ))as other
FROM IF_FEEINFO
GROUP BY "IF_FEEINFO"."INPATIENT_NO") A,
II_INMAININFO B
where A.INPATIENT_NO = B.INPATIENT_NO AND
B.in_state = '4' and
( B.OUT_DATE) >= to_date('2002-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and
( B.OUT_DATE) < to_date('2006-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
select *
from (select "INPATIENT_NO",
sum(decode (if_feeinfo.fee_code,'001',if_feeinfo.tot_cost,'003',if_feeinfo.tot_cost,'038',if_feeinfo.tot_cost,'005',if_feeinfo.tot_cost,'037',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'009',if_feeinfo.tot_cost,'028',if_feeinfo.tot_cost,'031',if_feeinfo.tot_cost,'032',if_feeinfo.tot_cost,0)) as tot,
sum(decode (if_feeinfo.fee_code , '032', if_feeinfo.tot_cost, 0) )as b,
sum(decode (if_feeinfo.fee_code , '038', if_feeinfo.tot_cost, 0 ))as h,
sum(decode (if_feeinfo.fee_code , '001', if_feeinfo.tot_cost, 0 ))as x,
sum(decode (if_feeinfo.fee_code , '003', if_feeinfo.tot_cost, 0 ))as z,
sum(decode (if_feeinfo.fee_code , '017', if_feeinfo.tot_cost, 0 ))as f,
sum(decode (if_feeinfo.fee_code , '005', if_feeinfo.tot_cost , 0 ))as hy,
sum(decode (if_feeinfo.fee_code , '024', if_feeinfo.tot_cost, 0 ))as sy,
sum(decode (if_feeinfo.fee_code , '029', if_feeinfo.tot_cost, 0 ))as sx,
sum(decode (if_feeinfo.fee_code , '037', if_feeinfo.tot_cost , 0 ))as zl,
sum(decode (if_feeinfo.fee_code , '028', if_feeinfo.tot_cost , 0 ))as ss,
sum(decode (if_feeinfo.fee_code , '031', if_feeinfo.tot_cost , 0 ))as other
FROM IF_FEEINFO
GROUP BY "IF_FEEINFO"."INPATIENT_NO") A,
(select "II_INMAININFO"."DEPT_NAME",
"II_INMAININFO"."INPATIENT_NO",
"II_INMAININFO"."NAME",
"II_INMAININFO"."OUT_DATE",
"II_INMAININFO"."IN_STATE"
from II_INMAININFO) B
where A.INPATIENT_NO = B.INPATIENT_NO AND
B.in_state = '4' and
( B.OUT_DATE) >= to_date('2002-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and
( B.OUT_DATE) < to_date('2006-08-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
不知道有没有优化的SQL语句。
谢谢