oracle左联检索不到数据,左联接数据不统一有关问题

在使用左联接时，遇到问题：

1、使用tab1,左联接tab2;

2、tab1有4条数据，tab2中对应tab1的4条数据 有>4条数据与之对应；

3、简单使用左联接之后，会出现，数据总数是后面的tab2与之对应的条数。而不是tab1的总数。解决方案

select * from tab1 left join

(

select * from

(

select a.*,row_number() over(partition by b3 order by b1) r

from tab2 a

)

where r = 1

) tab12 on a1=b3

其中a1,b3是两表的连接条件中的字段

以上感谢csdn的 Robin_Ares

后附上处理统计的相关sql,针对oracle，备忘

为了实现，如下报表功能：

使用sql 如下：

SELECT QYJG,JGMC,SUM(MONEY4) JYZE,SUM(COUNT4) CXZS,SUM(COUNT1) DJFZS,SUM(MONEY1) DJFJE,SUM(COUNT2) ZZZS,SUM(MONEY2) ZZJE,SUM(COUNT3) DKHKZS,SUM(MONEY3) DKHKJE

FROM (

SELECT QYJG,JGMC,DECODE(JYDM,'7502', 1, 0 ) as COUNT1,

DECODE(DECODE(JYDM,'7502', JYJE, 0.00 ),0,'0.00', trim(to_char(DECODE(JYDM,'7502', JYJE, 0.00 ),'99999999999999.99'))) as MONEY1,

DECODE(JYDM,'1011', 1,'1012',1, 0 ) as COUNT2,

DECODE(DECODE(JYDM,'1011', JYJE,'1012',JYJE, 0.00 ),0,'0.00', trim(to_char(DECODE(JYDM,'1011', JYJE,'1012',JYJE, 0.00 ),'99999999999999.99'))) as MONEY2,

DECODE(JYDM,'1100', 1, 0 ) as COUNT3,

DECODE(DECODE(JYDM,'1100', JYJE, 0.00 ),0,'0.00', trim(to_char(DECODE(JYDM,'1100', JYJE, 0.00 ),'99999999999999.99'))) as MONEY3,

DECODE(JYDM,'1011', 0,'1012',0,'7502',0,'1100',0, 1 ) as COUNT4,

DECODE(DECODE(JYDM,'1011', JYJE,'1012',JYJE,'7502',JYJE,'1100',JYJE, 0.00 ),0,'0.00', trim(to_char(DECODE(JYDM,'1011', JYJE,'1012',JYJE,'7502',JYJE,'1100',JYJE, 0.00 ),'99999999999999.99'))) as MONEY4

FROM

(

SELECT BT.ZHDH ZHDH ,BT.JYJE JYJE,BT.JYDM ,( select displayvalue from SYSENUMITEM t where t.enumid =

( select enumid from SYSENUM ss where ss.fieldname='BRANCHNO' and ss.tablename = 'PUBLIC' ) and CS.QYJG = T.FIELDVALUE AND T.FIELDVALUE LIKE '%' ) JGMC, QYJG FROM (SELECT * FROM B_TRANS_LOG WHERE JYRQ >= $P{t_bgnDate} AND JYRQ<= $P{t_endDate} AND JYJE >0 AND ZJJYZT= $P{t_transResult} ) BT left join

(

select * from

(

select a.*, row_number() over(partition by a.ZH order by a.ID ) r

from DEMO a

) where r = 1

) CS ON BT.ZHDH = CS.ZH

)

) GROUP BY QYJG