项目背景
业务系统上线经常面临历史数据与系统同步问题,我们项目中的做法是
1. 新发生的项目严格走系统;
2.历史项目逐年倒推,导入系统;
因为工程项目的周期一般要超过1年,在导入2018年项目时,就会面临项目数据分布在17年、18年,甚至16年,需要对比系统中数据与财务数据、部门统计数据的差异,调整数据一致后再统一导入系统中,完成线下到线上的切换;
需求描述
需要导出系统中所有工程项目的基本信息、相关采购订单、相关合同额/送审额/审定额、开票次数、开票金额、单项拆分金额(一次开票涉及多个单项)、回款次数、回款金额
表结构
SQL
SELECT
project_engineerings.id AS '单项工程ID',
project_engineerings.`code` AS '单项工程编号',
project_engineerings.`name` AS '单项工程名称',
project_contract_subs.`code` AS '采购订单编号',
project_engineerings.contract_split_money AS '合同额',
c.ssj AS '送审金额',
d.sdj AS '审定金额',
billing_applies.invoice_date AS '开票日期',
billing_applies.invoice_code AS '发票号',
count(billing_applies.pcs_kpze) AS '开票次数',
SUM(billing_applies.pcs_kpze) AS '开票金额',
SUM(billing_apply_pes.pe_total) AS '单项拆分金额',
e.`回款次数`,
e.`回款金额`
FROM
project_engineerings
#关联开票申请中单项信息表,获取单项拆分金额
LEFT JOIN billing_apply_pes ON billing_apply_pes.project_engineering_id = project_engineerings.id
#关联开票申请表,获取开票信息
LEFT JOIN billing_applies ON billing_apply_pes.billing_apply_id = billing_applies.id
#关联采购订单表,获取采购订单编号
LEFT JOIN project_contract_subs ON project_contract_subs.id = project_engineerings.project_contract_sub_id
#关联送内审节点,获取送审价格
LEFT JOIN (
SELECT
#送审价格保存在data_json中,需要解析
SUBSTRING_INDEX(
SUBSTRING_INDEX(
project_engineerings_nodes.data_json,
'"',
14
),
'"',
- 1
) AS 'ssj',
project_engineerings_nodes.project_engineerings_id
FROM
project_engineerings_nodes
WHERE
project_engineerings_nodes.node_name LIKE '%送内审%'
LIMIT 1,
100
) AS c ON c.project_engineerings_id = project_engineerings.id
#关联审定单节点,获取审定价格
LEFT JOIN (
SELECT
#审定价格保存在data_json中,需要解析
SUBSTRING_INDEX(
SUBSTRING_INDEX(
project_engineerings_nodes.data_json,
'"',
14
),
'"',
- 1
) AS 'sdj',
project_engineerings_nodes.project_engineerings_id
FROM
project_engineerings_nodes
WHERE
project_engineerings_nodes.node_name LIKE '%审定单%'
LIMIT 1,
100
) AS d ON d.project_engineerings_id = project_engineerings.id
#关联回款表,获取回款次数及金额
LEFT JOIN (
SELECT
invoice_returns.pe_code,
count(invoice_returns.id) AS '回款次数',
sum(invoice_returns.ret_money) AS '回款金额'
FROM
invoice_returns
GROUP BY
invoice_returns.pe_code
) AS e ON e.pe_code = project_engineerings.`code`
WHERE
project_engineerings.id <> 1
#针对单项分组,统计各项关联数据
GROUP BY
project_engineerings.`code`
#根据单项id排序,方便数据核对
ORDER BY
project_engineerings.id ASC
最终结果
Excel中进行对比的公式
=VLOOKUP(B2,部门提供数据!A:K,5,0)-F2
查找 部门提供数据!A:K 中与B2值相同的行,取该行第5列的数字,减去F2的值
示例:系统导出的单项合同额与部门提供的合同额偏差;
快速复制公式到列
在左上角名字处输入:Q2:Q5149 这样选中多个列;
然后 shift 输入公式 =VLOOKUP(B2,部门提供数据!A:K,6,0)-G2
然后 Ctrl+回车