费用申请单和差旅费报销单我用SQL创建了一个报表，费用申请、差旅费报销分别用了一个select语句进行查询，然后用union all进行合并成一个新表，但是当查询后，前面这五个字段都一样的情况下，申请金额不会合并生成，费用申请单一行记录，差旅费报销一条记录图片.png

SQL语句：

select
/*teert.FBillNo AS 单据编码,
djlx.FDJLXName AS 单据类型,*/
sqzz.SQZZname AS 申请组织,
bm.FBMname AS 费用承担部门,
fyxm.FYXMname AS 费用项目,
yfaxm.FDATAVALUE  AS 销售项目,
yfaxm1.FDATAVALUE AS 研发项目,
/*teert.FAPPROVEDATE AS 审核日期,
CASE
WHEN teert.FDOCUMENTSTATUS = 'A' THEN '创建'
WHEN teert.FDOCUMENTSTATUS = 'B' THEN '审核中'
WHEN teert.FDOCUMENTSTATUS = 'C' THEN '已审核'
WHEN teert.FDOCUMENTSTATUS = 'D' THEN '重新审核'
ELSE  '暂存'
END
AS 单据状态,*/
sum(teery.FOrgAmount) AS 申请金额,
sum(case when MONTH(teert.FAPPROVEDATE)= 1 THEN teery.FOrgAmount ELSE 0 end) AS 一月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 2 THEN teery.FOrgAmount ELSE 0 end) AS 二月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 3 THEN teery.FOrgAmount ELSE 0 end) AS 三月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 4 THEN teery.FOrgAmount ELSE 0 end) AS 四月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 5 THEN teery.FOrgAmount ELSE 0 end) AS 五月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 6 THEN teery.FOrgAmount ELSE 0 end) AS 六月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 7 THEN teery.FOrgAmount ELSE 0 end) AS 七月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 8 THEN teery.FOrgAmount ELSE 0 end) AS 八月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 9 THEN teery.FOrgAmount ELSE 0 end) AS 九月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 10 THEN teery.FOrgAmount ELSE 0 end) AS 十月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 11 THEN teery.FOrgAmount ELSE 0 end) AS 十一月份,
sum(case when MONTH(teert.FAPPROVEDATE)= 12 THEN teery.FOrgAmount ELSE 0 end) AS 十二月份
from T_ER_ExpenseRequest teert
left join T_ER_ExpenseRequestEntry teery on teert.FID = teery.FID
/*单据类型*/
inner join (SELECT t0.FBILLTYPEID AS FBILLTYPEID,t0.FNUMBER AS FNumber,t0.FBILLFORMID AS FBillFormID,t0_L.FNAME AS FDJLXName
            FROM T_BAS_BILLTYPE t0
            LEFT JOIN T_BAS_BILLTYPE_L t0_L ON (t0.FBILLTYPEID=t0_L.FBILLTYPEID AND t0_L.FLocaleId=2052))djlx on teert.FBILLTYPEID = djlx.FBILLTYPEID
/*部门*/
inner join (select tbdt_l.FNAME AS FBMname,tbdt.FDEPTID AS FDEPTID
        from t_bd_department tbdt
        left join t_bd_department_l tbdt_l on (tbdt.FDEPTID = tbdt_l.FDEPTID AND tbdt_l.FLOCALEID=2052)) bm on  teery.FEntryCostDeptID = bm.FDEPTID
/*费用项目*/
inner join (select tbe_l.FNAME AS FYXMname, tbe.FEXPID AS FEXPID
        from t_bd_expense tbe
        left join t_bd_expense_l tbe_l on (tbe.FEXPID = tbe_l.FEXPID AND tbe_l.FLOCALEID=2052))fyxm on teery.FExpenseItemID = fyxm.FEXPID
/*申请组织*/
inner join (select tbac_l.FNAME AS SQZZname, tbak.FNUMBER AS SQZZnumber,tbak.FBOOKID AS FBOOKID,tbak.FACCOUNTORGID AS FACCOUNTORGID
        from t_bd_accountbook tbak
        left join t_bd_accountbook_l tbac_l on ( tbak.FBOOKID = tbac_l.FBOOKID AND tbac_l.FLOCALEID=2052))sqzz on teert.FOrgID = sqzz.FACCOUNTORGID
/*销售项目*/
inner join (select tbaa.FNUMBER AS FNUMBER ,tbaa.FID AS FID, tbay_l.FDATAVALUE AS FDATAVALUE,tbay_l.FENTRYID as FENTRYID
        from T_BAS_ASSISTANTDATA tbaa
        left join T_BAS_ASSISTANTDATAENTRY tbay on tbay.FID = tbaa.FID
        left join T_BAS_ASSISTANTDATAENTRY_L tbay_l on (tbay.FENTRYID = tbay_l.FENTRYID and tbay_l.FLocaleid =2052 ))yfaxm  on  teert.F_syri_Assistant1 = yfaxm.FENTRYID
/*研发项目 */
left join (select tbaa.FNUMBER AS FNUMBER ,tbaa.FID AS FID, tbay_l.FDATAVALUE AS FDATAVALUE,tbay_l.FENTRYID as FENTRYID
        from T_BAS_ASSISTANTDATA tbaa
        left join T_BAS_ASSISTANTDATAENTRY tbay on tbay.FID = tbaa.FID
        left join T_BAS_ASSISTANTDATAENTRY_L tbay_l on (tbay.FENTRYID = tbay_l.FENTRYID and tbay_l.FLocaleid =2052 ))yfaxm1  on  teert.F_ora_Assistant = yfaxm1.FENTRYID
where teert.FDOCUMENTSTATUS='C' AND (yfaxm1.FDATAVALUE = '通用' or yfaxm1.FDATAVALUE is null ) AND  (djlx.FDJLXName = '对公费用申请单' or djlx.FDJLXName = '个人费用申请单')
group by sqzz.SQZZname,bm.FBMname,fyxm.FYXMname,yfaxm.FDATAVALUE,yfaxm1.FDATAVALUE

union

select
/*teerb.FBillNo AS 单据编码,
djlx1.FDJLXName AS 单据类型, */
sqzz1.SQZZname AS 申请组织,
bm1.FBMname AS 费用承担部门,
fyxm3.FYXMname AS 费用项目,
yfaxm4.FDATAVALUE  AS 销售项目,
yfaxm5.FDATAVALUE AS 研发项目,
/*teerb.FAPPROVEDATE AS 审核日期,
CASE
WHEN teerb.FDOCUMENTSTATUS = 'A' THEN '创建'
WHEN teerb.FDOCUMENTSTATUS = 'B' THEN '审核中'
WHEN teerb.FDOCUMENTSTATUS = 'C' THEN '已审核'
WHEN teerb.FDOCUMENTSTATUS = 'D' THEN '重新审核'
ELSE  '暂存'
END
AS 单据状态,*/
/*month(teerb.FAPPROVEDATE ) AS 月份,*/
sum(teery1.FRELRQAMOUNT) AS 申请金额,
sum(case when MONTH(teerb.FAPPROVEDATE)= 1 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 一月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 2 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 二月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 3 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 三月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 4 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 四月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 5 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 五月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 6 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 六月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 7 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 七月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 8 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 八月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 9 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 九月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 10 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 十月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 11 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 十一月份,
sum(case when MONTH(teerb.FAPPROVEDATE)= 12 THEN teery1.FRELRQAMOUNT ELSE 0 end) AS 十二月份
from t_ER_ExpenseReimb teerb
left join t_ER_ExpenseReimbEntry teery1 on teery1.FID = teerb.FID
/*单据类型*/
inner join (SELECT t0.FBILLTYPEID AS FBILLTYPEID,t0.FNUMBER AS FNumber,t0.FBILLFORMID AS FBillFormID,t0_L.FNAME AS FDJLXName
            FROM T_BAS_BILLTYPE t0
            LEFT JOIN T_BAS_BILLTYPE_L t0_L ON (t0.FBILLTYPEID=t0_L.FBILLTYPEID AND t0_L.FLocaleId=2052))djlx1 on teerb.FBILLTYPEID = djlx1.FBILLTYPEID
/*部门*/
inner join (select tbdt_l1.FNAME AS FBMname,tbdt1.FDEPTID AS FDEPTID
            from t_bd_department tbdt1
            left join t_bd_department_l tbdt_l1 on (tbdt1.FDEPTID = tbdt_l1.FDEPTID AND tbdt_l1.FLOCALEID=2052))bm1 on  teery1.FExpenseDeptEntryID = bm1.FDEPTID
/*费用项目*/
inner join (select tbe_l1.FNAME AS FYXMname, tbe1.FEXPID AS FEXPID
            from t_bd_expense tbe1
            left join t_bd_expense_l tbe_l1 on (tbe1.FEXPID = tbe_l1.FEXPID AND tbe_l1.FLOCALEID=2052))fyxm3 on teery1.FExpID = fyxm3.FEXPID
/*申请组织*/
inner join (select tbac_l1.FNAME AS SQZZname, tbak1.FNUMBER AS SQZZnumber,tbak1.FBOOKID AS FBOOKID,tbak1.FACCOUNTORGID AS FACCOUNTORGID
            from t_bd_accountbook tbak1
            left join t_bd_accountbook_l tbac_l1 on ( tbak1.FBOOKID = tbac_l1.FBOOKID AND tbac_l1.FLOCALEID=2052)) sqzz1 on teerb.FExpenseOrgId = sqzz1.FACCOUNTORGID
/*销售项目*/
inner join (select tbaa1.FNUMBER AS FNUMBER ,tbaa1.FID AS FID, tbay_l1.FDATAVALUE AS FDATAVALUE,tbay_l1.FENTRYID as FENTRYID
            from T_BAS_ASSISTANTDATA tbaa1
            left join T_BAS_ASSISTANTDATAENTRY tbay1 on tbay1.FID = tbaa1.FID
            left join T_BAS_ASSISTANTDATAENTRY_L tbay_l1 on (tbay1.FENTRYID = tbay_l1.FENTRYID and tbay_l1.FLocaleid =2052 ))yfaxm4  on  teerb.F_syri_Assistant = yfaxm4.FENTRYID
/*研发项目 */
left join (select tbaa1.FNUMBER AS FNUMBER ,tbaa1.FID AS FID, tbay_l1.FDATAVALUE AS FDATAVALUE,tbay_l1.FENTRYID as FENTRYID
            from T_BAS_ASSISTANTDATA tbaa1
            left join T_BAS_ASSISTANTDATAENTRY tbay1 on tbay1.FID = tbaa1.FID
              left join T_BAS_ASSISTANTDATAENTRY_L tbay_l1 on (tbay1.FENTRYID = tbay_l1.FENTRYID and tbay_l1.FLocaleid =2052 ))yfaxm5  on  teerb.F_ora_Assistant = yfaxm5.FENTRYID
where teerb.FDOCUMENTSTATUS = 'C'   AND (yfaxm5.FDATAVALUE = '通用' or yfaxm5.FDATAVALUE is null) AND  djlx1.FDJLXName = '差旅费报销单(2019新政)'
group by sqzz1.SQZZname,bm1.FBMname,fyxm3.FYXMname,yfaxm4.FDATAVALUE,yfaxm5.FDATAVALUE

产品问题SQL账表问题请教

50 产品问题 SQL账表问题请教

恭喜您！

产品问题
SQL账表问题请教