首页 > itarticle > 写过的复杂sql

写过的复杂sql

admin 11月 13, 2020 0

写过的复杂SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
SELECT
  y.INSTSNAME, y.PAY_INST_CODE, y.SETTDATE,  y.ZJYBS, y.CSJYBS, y.DZFJYBS,
  y.YZFJYBS, y.FSSBJYBS, y.XYCWJYBS, y.WXYJYBS, y.JYJE, y.YHJE, y.YFJE
FROM
  (
    SELECT
      x.instsname, x.pay_inst_code, x.settdate, x.ZJYBS, x.CSJYBS,
      x.DZFJYBS, x.YZFJYBS, x.FSSBJYBS, x.XYCWJYBS, x.WXYJYBS,
      trim( to_char ( x.JYJE / 100, '99999999990.00' ) ) AS JYJE,
      trim( to_char ( x.YHJE / 100, '99999999990.00' ) ) AS YHJE,
      trim( to_char ( x.YFJE / 100, '99999999990.00' ) ) AS YFJE
    FROM
      (
        SELECT
          inst.instsname AS instsname,
          t.pay_inst_code AS pay_inst_code,
          t.settdate AS settdate,
          NVL ( COUNT(*), 0) AS ZJYBS,
          NVL ( SUM(CASE WHEN t.status = '00' THEN 1 END), 0) AS CSJYBS,
          NVL ( SUM(CASE WHEN t.status = '01' THEN 1 END), 0) AS DZFJYBS,
          NVL ( SUM(CASE WHEN t.status = '02' THEN 1 END), 0) AS YZFJYBS,
          NVL ( SUM(CASE WHEN t.status = '07' THEN 1 END), 0) AS FSSBJYBS,
          NVL ( SUM(CASE WHEN t.status = '08' THEN 1 END), 0) AS XYCWJYBS,
          NVL ( SUM(CASE WHEN t.status = '09' THEN 1 END), 0) AS WXYJYBS,
          NVL ( SUM(t.trans_amt), 0 ) AS JYJE,
          NVL ( SUM(t.fav_amt), 0 ) AS YHJE,
          NVL ( SUM(t.real_amt), 0 ) AS YFJE
        FROM tbl_trans_order t
        LEFT JOIN tbl_bse_info_inst inst ON inst.instcd = t.pay_inst_code
        WHERE 1=1 
        GROUP BY
          ROLLUP (
            (inst.instsname, t.pay_inst_code), t.settdate
          )
      ) x
  ) y
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
SELECT
	t1.unit, t1.net, t1.tid, t1.skbs,
  t1.skyj,
	t1.czbs,
	t1.czje,
	t1.hksqbs, t1.hkwcbs,
	t1.hktkyj,
  t1.tksqbs, t1.tkwcbs,
	t1.tkje,
	t1.tkyj,
	t1.zbs,
	t1.zje
FROM
	(
		SELECT
			x.unit, x.net, x.tid, x.skbs,
			trim( to_char ( x.skyj / 100, '99999999990.00' ) ) skyj,
			x.czbs,
			trim( to_char ( x.czje / 100, '99999999990.00' ) ) czje,
			x.hksqbs, x.hkwcbs,
			trim( to_char ( x.hktkyj / 100, '99999999990.00' ) ) hktkyj,
			x.tksqbs, x.tkwcbs,
			trim( to_char ( x.tkje / 100, '99999999990.00' ) ) tkje,
			trim( to_char ( x.tkyj / 100, '99999999990.00' ) ) tkyj,
			trim( x.skbs + x.czbs + x.hksqbs + x.hkwcbs + tksqbs + tkwcbs ) zbs,
			trim( to_char ( ( x.skyj + x.czje - x.hktkyj - x.tkje - x.tkyj ) / 100, '99999999990.00' ) ) zje
		FROM
			(
				SELECT
					nvl (u.mchnt_name, t.txnmchntcd) AS unit,
					nvl (n.netname, t.TXNNETCD) AS net,
					t.TERMID AS tid,
					nvl ( sum( CASE WHEN t.inntype IN (2063, 2067, 2075) THEN t.acdtxncnt END ), 0 ) AS skbs,
					nvl ( sum( CASE WHEN t.inntype IN (2063, 2067, 2075) THEN t.acddptamt END ), 0 ) AS skyj,
					nvl ( sum( CASE WHEN t.inntype = 5163 then t.acdtxncnt END ), 0 ) AS czbs,
					nvl ( sum( CASE WHEN t.inntype = 5163 then t.acdtxnamt END ), 0 ) AS czje,
					nvl ( sum( CASE WHEN t.inntype = 1365 then t.acdtxncnt END ), 0 ) AS hksqbs,
					nvl ( sum( CASE WHEN t.inntype = 1385 then t.acdtxncnt END ), 0 ) AS hkwcbs,
					nvl ( sum( CASE WHEN t.inntype = 1385 AND ( t.CHIPBADTYPE <> 1 OR t.FACEBADTYPE <> 1 ) THEN t.acddptamt END ), 0 ) AS hktkyj,
					nvl ( sum( CASE WHEN t.inntype = 1425then t.acdtxncnt END ), 0 ) AS tzsqbs,
					nvl ( sum( CASE WHEN t.inntype = 1445then t.acdtxncnt END ), 0 ) astzwcbs,
					nvl ( sum( CASE WHEN t.inntype = 1445then t.acdtxnamt END ), 0 ) astzje,
					nvl ( sum( CASE WHEN t.inntype = 1335 THEN t.acdtxncnt END ), 0 ) AS tksqbs,
					nvl ( sum( CASE WHEN t.inntype = 1355 THEN t.acdtxncnt END ), 0 ) AS tkwcbs,
					nvl ( sum( CASE WHEN t.inntype = 1355 THEN t.acdtxnamt END ), 0 ) AS tkje,
					nvl ( sum( CASE WHEN t.inntype = 1355 then t.acddptamt END ), 0 ) AS tkyj
				FROM
					v_sellchargereturnchange t
				LEFT JOIN t_bse_inf_mchnt u ON trim(t.TXNMCHNTCD) = u.mchnt_no
				LEFT JOIN t_bse_inf_net n ON trim(t.TXNNETCD) = n.netcd
				WHERE
					t.errcode = 0 ?: 1
				GROUP BY
					ROLLUP (
						(t.TXNMCHNTCD, u.mchnt_name),
						(t.TXNNETCD, n.netname),
						(t.TERMID)
					)
			) x
	) t1
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
SELECT settdate,txnmchntcd,txnmchntname,sum(SCCZBS),sum(SCCZJE),sum(SCCZSXF),sum(ECCZBS),sum(ECCZJE),sum(ECCZSXF)
FROM (
  SELECT
    t.settdate,
    t.txnmchntcd,
    max(t.txnmchntname) AS txnmchntname,
    SUM(1) AS SCCZBS,
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.TXNAMT / 100 END), 0)  AS SCCZJE,
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.FEE_AMT / 100 END), 0)  AS SCCZSXF,
    0 AS ECCZBS,
    0 AS ECCZJE,
    0 AS ECCZSXF
  FROM t_log_first_charge T
       LEFT JOIN TBL_BSE_INFO_MCHNT M ON T.TXNMCHNTCD = M.MCHNT_NO
  WHERE T.ERRCODE = '0000'
        AND T.CANCEL_FLAG = 'N' ?:1
        AND T.ACQINST *:1
        AND T.TXNMCHNTCD #:1
        AND T.INNTYPE IN ('10000105','10000136')
        AND T.TXNAMT > 0
        GROUP BY t.settdate, t.txnmchntcd
UNION ALL
  SELECT
    t.settdate,
    t.txnmchntcd,
    max(t.txnmchntname),
    0,
    0,
    0,
    SUM(1),
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.TXNAMT / 100 END), 0),
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.FEE_AMT / 100 END), 0)  
  FROM t_log_online_paymenthis T
       LEFT JOIN TBL_BSE_INFO_MCHNT M ON T.TXNMCHNTCD = M.MCHNT_NO
  WHERE T.ERRCODE = '0000'
        AND T.CANCEL_FLAG = 'N'
        AND T.CENSEQ NOT IN (SELECT x.censeq FROM t_log_first_charge x) ?:1
        AND T.ACQINST *:1
        AND T.TXNMCHNTCD #:1
        AND T.TXNAMT > 0
        AND T.INNTYPE IN ('10000105','10000136')
        GROUP BY  t.settdate,   t.txnmchntcd
) GROUP BY  ROLLUP (settdate,(txnmchntcd,txnmchntname))
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SELECT
   t1.settdate,t1.office, t1.line, t1.plateNumber, t1.hjje
FROM
  (
    SELECT
      z.settdate,z.office, z.line, z.plateNumber, z.hjje
    FROM
      (
        SELECT
          t.settdate,
          x.office_name  office,
          y.line_name  line,
          t.plate_number  plateNumber,
         trim( to_char ( sum(t.countamt) / 1, '99999999990.00' ) ) hjje
        FROM
    t_pr_carcount t
          left join t_office_info x on x.office_no = t.office
          left join t_line_info y on y.line_no = t.line
          where 1=1  and t.st = '02' ?:1
        GROUP BY
          ROLLUP
            (t.settdate,x.office_name,y.line_name,t.plate_number)          
      ) z
  ) t1
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
SELECT settdate,txnmchntcd,txnmchntname,sum(SCCZBS),
TO_CHAR(sum(SCCZJE), '9999999990.00'),
TO_CHAR(sum(SCCZSXF), '9999999990.00'),
sum(ECCZBS),
TO_CHAR(sum(ECCZJE), '9999999990.00'),
TO_CHAR(sum(ECCZSXF), '9999999990.00')
FROM (
  SELECT
    t.settdate,
    t.txnmchntcd,
    max(t.txnmchntname) AS txnmchntname,
    SUM(1) AS SCCZBS,
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.TXNAMT / 100 END), 0)  AS SCCZJE,
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.FEE_AMT / 100 END), 0)  AS SCCZSXF,
    0 AS ECCZBS,
    0 AS ECCZJE,
    0 AS ECCZSXF
  FROM t_log_first_charge T
       LEFT JOIN TBL_BSE_INFO_MCHNT M ON T.TXNMCHNTCD = M.MCHNT_NO
  WHERE T.ERRCODE = '0000'
        AND T.CANCEL_FLAG = 'N' 
        --AND T.ACQINST *:1
        --AND T.TXNMCHNTCD #:1
        AND T.SETTDATE = '20190104'
        AND T.INNTYPE IN ('10000105','10000136')
        AND T.TXNAMT > 0
        GROUP BY t.settdate, t.txnmchntcd
UNION ALL
  SELECT
    t.settdate,
    t.txnmchntcd,
    max(t.txnmchntname),
    0,
    0,
    0,
    SUM(1),
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.TXNAMT / 100 END), 0),
    NVL(SUM(CASE WHEN T.INNTYPE IN ('10000105','10000136') THEN T.FEE_AMT / 100 END), 0)  
  FROM t_log_online_paymenthis T
       LEFT JOIN TBL_BSE_INFO_MCHNT M ON T.TXNMCHNTCD = M.MCHNT_NO
  WHERE T.ERRCODE = '0000'
        AND T.CANCEL_FLAG = 'N'
        AND T.CENSEQ NOT IN (SELECT x.censeq FROM t_log_first_charge x) 
        --AND T.ACQINST *:1
        --AND T.TXNMCHNTCD #:1
        AND T.TXNAMT > 0
        AND T.SETTDATE = '20190104'
        AND T.INNTYPE IN ('10000105','10000136')
        GROUP BY  t.settdate,   t.txnmchntcd
) GROUP BY  ROLLUP (settdate,(txnmchntcd,txnmchntname))

微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能