SQL查询数据库中数据统计信息

项目背景

业务系统上线经常面临历史数据与系统同步问题,我们项目中的做法是

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+回车

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

铭记北宸

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值