又一条sql

最新推荐文章于 2024-10-16 23:34:20 发布

weixin_34380781

最新推荐文章于 2024-10-16 23:34:20 发布

阅读量83

点赞数

文章标签： python

原文链接：https://my.oschina.net/marjeylee/blog/834633

版权

2019独角兽企业重金招聘Python工程师标准>>>

SELECT 
  jpaemploye0_.ID AS col_0_0_,
  (SELECT 
    SUM(jpaemploye1_.PAYM_AMT) 
  FROM
    EO_EMP_ADV_ITEM jpaemploye1_,
    EO_EMP_ADV jpaemploye2_ 
  WHERE jpaemploye1_.EMP_ADV_ID = jpaemploye2_.ID 
    AND jpaemploye1_.EMP_ADV_ID = jpaemploye0_.ID) AS col_1_0_,
  (SELECT 
    MAX(jpaemploye3_.CURRENCY_NAME) 
  FROM
    EO_EMP_ADV_ITEM jpaemploye3_,
    EO_EMP_ADV jpaemploye4_ 
  WHERE jpaemploye3_.EMP_ADV_ID = jpaemploye4_.ID 
    AND jpaemploye3_.EMP_ADV_ID = jpaemploye0_.ID) AS col_2_0_,
  (SELECT 
    MAX(jpapayment5_.PAYM_BILL_ID) 
  FROM
    EO_PAYM_BILL_WRITE_OFF jpapayment5_ 
  WHERE jpapayment5_.WRITTEN_OFF_BILL_ID = jpaemploye0_.ID 
    AND jpapayment5_.WRITTEN_OFF_BILL_TYPE = 'E_ADV' 
    AND jpapayment5_.PAYM_BILL_TYPE = 'E_REIM') AS col_3_0_,
  (SELECT 
    MAX(jpaemploye6_.COMPANY_NAME) 
  FROM
    EO_EMP_ADV jpaemploye6_) AS col_4_0_,
  (SELECT 
    MAX(jpaemploye7_.STAT_TYPE_NAME) 
  FROM
    EO_EMP_ADV_STAT jpaemploye7_,
    EO_EMP_ADV jpaemploye8_ 
  WHERE jpaemploye7_.EMP_ADV_ID = jpaemploye8_.ID 
    AND jpaemploye7_.EMP_ADV_ID = jpaemploye0_.ID 
    AND (jpaemploye7_.TO_DATE IS NULL)) AS col_5_0_,
  jpaemploye0_.APP_DATE AS col_6_0_,
  jpaemploye0_.APP_USER_NAME AS col_7_0_,
  jpaemploye0_.PAYEE_USER_NAME AS col_8_0_,
  (SELECT 
    MAX(jpauimsynu9_.USER_REAL_NAME) 
  FROM
    UIM_SYN_USER jpauimsynu9_ 
  WHERE jpauimsynu9_.USER_NO = jpaemploye0_.APP_USER_NAME) AS col_9_0_,
  jpaemploye0_.COMPANY_NAME AS col_10_0_,
  (SELECT 
    MAX(jpaemploye10_.CURRENCY_CODE) 
  FROM
    EO_EMP_ADV_ITEM jpaemploye10_,
    EO_EMP_ADV jpaemploye11_ 
  WHERE jpaemploye10_.EMP_ADV_ID = jpaemploye11_.ID 
    AND jpaemploye10_.EMP_ADV_ID = jpaemploye0_.ID) AS col_11_0_ 
FROM
  EO_EMP_ADV jpaemploye0_ 
WHERE jpaemploye0_.COMPANY_CODE = '3001' 
  AND jpaemploye0_.PAYEE_USER_CODE = '0007875' 
  AND jpaemploye0_.SUB_TYPE = '3' 
  AND jpaemploye0_.APP_USER_NAME = '0007875' 
  AND (
    EXISTS 
    (SELECT 
      1 
    FROM
      EO_PAYM_BILL_WRITE_OFF jpapayment12_ 
    WHERE jpapayment12_.WRITTEN_OFF_BILL_ID = jpaemploye0_.ID 
      AND jpapayment12_.WRITTEN_OFF_BILL_TYPE = 'E_ADV' 
      AND jpapayment12_.PAYM_BILL_TYPE = 'E_REIM' 
      AND jpapayment12_.PAYM_BILL_ID = jpaemploye0_.id) 
    OR NOT (
      EXISTS 
      (SELECT 
        1 
      FROM
        EO_PAYM_BILL_WRITE_OFF jpapayment13_ 
      WHERE jpapayment13_.WRITTEN_OFF_BILL_ID = jpaemploye0_.ID 
        AND jpapayment13_.WRITTEN_OFF_BILL_TYPE = 'E_ADV')
    )
  ) 
  AND 
  (SELECT 
    MAX(jpaemploye14_.STAT_TYPE) 
  FROM
    EO_EMP_ADV_STAT jpaemploye14_ 
  WHERE jpaemploye14_.EMP_ADV_ID = jpaemploye0_.ID 
    AND (jpaemploye14_.TO_DATE IS NULL)) = 'PAID'

EO_EMP_ADV jpaemploye0_ 借支单

EO_EMP_ADV_ITEM jpaemploye1_ 明细

EO_EMP_ADV jpaemploye2_

EO_EMP_ADV_ITEM jpaemploye3_

EO_EMP_ADV jpaemploye4_

EO_PAYM_BILL_WRITE_OFF jpapayment5_ 借支报销关联关系表

EO_EMP_ADV jpaemploye6_

EO_EMP_ADV_STAT jpaemploye7_ 状态

EO_EMP_ADV jpaemploye8_

UIM_SYN_USER jpauimsynu9_ 用户信息

EO_EMP_ADV_ITEM jpaemploye10_

EO_EMP_ADV jpaemploye11_