左联接数据不统一问题

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

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

 

评论

4 楼 月亮不懂夜的黑 2014-04-17  

terry813 写道

其实还有另外一种SQL写法

求指教

3 楼 terry813 2014-04-17  

其实还有另外一种SQL写法

2 楼 月亮不懂夜的黑 2014-04-16  

hamber 写道

oracle可以使用开窗函数、但是mysql呢？该如何处理的？

其他的sqlserver，mysql可以使用 类似于：

select
substr(mh.FMsgTime,0,10) as FMsgTime,
sum(case when mh.FState='1' or FState='4' or FState='5' then 1 else 0 end) as allcount,
sum(case when mh.FState='7' then 1 else 0 end) sum8,
sum(case when mh.FState='0' or FState='2' then 1 else 0 end) sum0,
sum(case when mh.FState='1' then 1 else 0 end) sum1,

条件判断来实现

1 楼 hamber 2014-04-16  

oracle可以使用开窗函数、但是mysql呢？该如何处理的？