下午负责银行应用的同事跑过来，让帮忙实现一个SQL的写法。这个SQL是为了满足领导查看交易数据之用途，要求比较多。正好前两天在Tom的网站上看到过使用分析函数格式化查询结果，随手拈来。最终的SQL:
SELECT ta.organization_name, ta.new_amount, ta.new_count, tb.bank_list
FROM (SELECT zz.organization_name, zz.organization_code,
zz.organization_id, SUM (NVL (yy.amount, 0)) AS new_amount,
DECODE (SUM (NVL (yy.amount, 0)),
0, 0,
COUNT (*)
) AS new_count
FROM (SELECT organization_id
FROM companybankaccount
WHERE bank_id = '1006') xx,
(SELECT payer_company_org_code, amount
FROM p_transaction
WHERE payer_bank_code = 'FBOC---2001'
AND create_time >= TO_DATE ('20070601', 'yyyymmdd')
AND create_time < TO_DATE ('20070701', 'yyyymmdd')) yy,
organization zz
WHERE xx.organization_id(+) = zz.organization_id
AND yy.payer_company_org_code(+) = zz.organization_code
GROUP BY zz.organization_code,
zz.organization_name,
zz.organization_id) ta,
(SELECT organization_id,
MAX (SYS_CONNECT_BY_PATH (bank_name, ' ')) bank_list
FROM (SELECT organization_id, bank_name,
ROW_NUMBER () OVER (PARTITION BY organization_id ORDER BY bank_name)
rn
FROM (SELECT aa.organization_id, bb.bank_name
FROM companybankaccount aa,
basicbank bb
WHERE organization_id IN (
SELECT organization_id
FROM companybankaccount
WHERE bank_id = '1006')
AND aa.bank_id = bb.bank_id
GROUP BY aa.organization_id, bb.bank_name) TEST)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND PRIOR organization_id = organization_id
GROUP BY organization_id) tb
WHERE ta.organization_id = tb.organization_id
部分结果显示：
organization_id bank_list
--------------- ----------------------
00009 中行交行工行建行
000017 中行工行建行
![[This is a Roller site]](http://blog.oraclefans.cn/rocksky/resource/images/roller-logo.gif){kind=logo}