|
说我有两个表
# P8 N% z7 i, x8 WTran(Tran_Id,Tran_Name)TFlag(Tran_Id,Flag)
+ K6 L2 i/ z2 ]6 [2 u我的查询结果如下:6 \( u6 \9 T5 H. _# j: F6 z
TRAN_ID TRAN_NAME FLAG -------------------------101 Lend A 101 Lend B 101 Lend C 101 Lend D 102 Borrow E 101 Lend F 101 Lend G我希望输出是这样的:( h1 m" z! l% u8 e& U# \" p6 |4 n3 L
TRAN_ID TRAN_NAME FLAG-------------------------101 Lend A (null) (null) B (null) (null) C (null) (null) D 102 Borrow E 101 Lend F (null) (null) G 1 ?( n5 ?/ V$ X) j4 }
解决方案:
8 w6 \" t, n1 U3 n- M) X8 m* ~ 在SQL Server和Oracle你们都可以用LAG分析功能。甲骨文:
% x$ l( w- g. C# h2 m8 lWITH data (tran_id,tran_name,flag) AS SELECT Lend','A' FROM DUAL UNION ALL SELECT 101, 'Lend','B' FROM DUAL UNION ALL SELECT 101, 'Lend','C' FROM DUAL UNION ALL SELECT 101, 'Lend','D' FROM DUAL UNION ALL SELECT 102,'Borrow','E' FROM DUAL UNION ALL SELECT 101, 'Lend','F' FROM DUAL UNION ALL SELECT 101, 'Lend','G' FROM DUAL )SELECT NVL2(keep,tran_id,null) tran_id, NVL2(keep,tran_name,null) tran_name, flagFROM SELECT tran_id,tran_name,flag, CASE WHEN LAG(tran_id) OVER (ORDER BY flag) tran_id OR LAG(tran_name) OVER (ORDER BY flag) tran_name THEN 1 END keep FROM data) |
|