数据量
SQL> select count(*) from zinhosmedikind; COUNT(*)
----------
9278SQL> select count(*) from checkuplistdetail; COUNT(*)
----------
173169
SQL> select count(*) from zinhosmedikind; COUNT(*)
----------
9278SQL> select count(*) from checkuplistdetail; COUNT(*)
----------
173169
我想可以分析一下需求,重新改写.
SELECT
CENTERCODE,
SERIALNO,
LISTNO,
MEDIKINDCODE,
SUM(AMOUNT) AS AMOUNT
FROM CHECKUPLISTDETAIL
WHERE MEDIKINDCODE='00'
GROUP BY CENTERCODE,SERIALNO,LISTNO,MEDIKINDCODE
UNION ALL
SELECT
HOSCODE AS CENTERCODE,
INHOSBALANCENO AS SERIALNO,
0 AS LISTNO,
MK_ID AS MEDIKINDCODE,
sum(AMOUNT)/count(hoscode) as amount
FROM ZINHOSMEDIKIND
group by hoscode,inhosbalanceno,mk_id )
得到所有的行数据,然后在这上面处理数据。
可以重新分析你的需求,简化代码.
宁愿建多几个view ,而不要把同样的查询写在一堆.
我有这么两个建议:
1.如果界面可以控制,那么直接把数据提取出来,横排数据的事情交给界面
2.如果一定要用sql实现横排,建议在存储过程中用游标处理一下就是
, sum (decode (medikindcode, '00', amount, 0)) amount00
, sum (decode (medikindcode, '01', amount, 0)) amount01
, sum (decode (medikindcode, '02', amount, 0)) amount02
, sum (decode (medikindcode, '03', amount, 0)) amount03
, sum (decode (medikindcode, '04', amount, 0)) amount04
, sum (decode (medikindcode, '05', amount, 0)) amount05
, sum (decode (medikindcode, '06', amount, 0)) amount06
, sum (decode (medikindcode, '07', amount, 0)) amount07
, sum (decode (medikindcode, '08', amount, 0)) amount08
, sum (decode (medikindcode, '09', amount, 0)) amount09
from (select centercode, serialno, listno, medikindcode, sum (amount) amount
from checkuplistdetail
group by centercode, serialno, listno, medikindcode
union all
select hoscode, inhosbalanceno, 0, mk_id, sum (amount) / count (hoscode) amount
from zinhosmedikind
group by hoscode, inhosbalanceno, mk_id)
group by m.centercode, m.serialno, m.listno
好久都没看见你了