[置顶] 左联接数据不统一问题

来源：互联网发布：淘宝图片版权保护编辑：程序博客网时间：2024/05/16 00:38

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

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) DKHKJEFROM ( 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

查看图片附件

0 0