左联接数据不统一问题
在使用左联接时,遇到问题:
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
人生苦短,我用python