跟着项目经理学SQL(第一期)

看了一眼窗外灰蒙蒙的天空，我的心情略有低落，这种景象无疑是在预示着大雨将至，而我今天却没有带伞…

正当思绪神游之际，助理小芳在会议室那边打开门伸长脖子喊我名字说经理找我。我甚至都来不及看一眼她那精致的妆容，小芳便扭头把门又关上了。我起身拖着疲惫的身躯往会议室那边挪去…

打开门，看见坐在桌子一侧的经理，我打起精神挤出笑脸问道：经理，您找我？

经理没有回我，直接将一份需求文档丢在我面前，我拿起文档看了起来，大致内容如下：

…

客户现在有一张库存表(pro_inventory)：

货物编号(productNo)	货物名称(productName)	数量(num)

客户有三张出库单据表，他们表结构类似：表一(pro_out_a)、表二(pro_out_b)、表三(pro_out_c)：

单据类型(billType)	单据状态(billStatus)	货物编号(productNo)	领用数量(outNum)	已发数量(alOutNum)

这三张表分别存放三个不同的业务模块库存领用单据，单据类型分别为：101、102、103，单据状态有四种：01(创建)、02(提交)、03(部分发货)、04(完全发货)。

三种单据相互独立，但都是根据库存数量来领取货物的，比如：库存货物A有100个，现在101单据领用100个A并提交，在收到单子不立即发货的前提下，创建102单据的时候看见的库存数量仍然是100个，所以102单据也能领100个。此时就产生了问题，因为当前库存数量只能满足其中一个单子，这就意味着在后续货源没有得到及时补充的情况下，这个单子将会一直挂着。如果这种情况发生多次，那么库存势必就会有越来越多的单子无法及时关闭，这是客户不能接受的。

…

花了大概30分钟看完了整个需求，说实话我很生气，因为我不明白为什么这种级别的东西经理会找我。我看着落地窗台上一株几近枯萎的绿萝，不想说话。沉默许久后我问经理：有烟吗？经理递过来一只烟，是1916，我平常抽的都是软黄红梅的。我猛吸了一口烟，仰起头朝空中缓缓吐出烟雾，尼古丁的味道让我的心情略有放松，我看着经理决定实话实说：

”经理，这种级别的需求，不应该找我的！我不会啊…“

30秒钟后，我捂着脸，躲在办公室的角落里泣不成声，经理放下手中的椅子，居高临下地看着我：

”不会~~你装什么B？赶紧起来跟着老子学！“然后只见经理径直走到白板前拿起了笔，开始写着对我而言就像魔术般的SQL语句，他边写边说：

要想达到客户这种要求，我们需要明确以下几点：

• 构建一张新的库存表(pro_inventory_new)，这张表里的库存量 = 实际库存量 - 领用汇总量，这张表作为单据的库存参照表。
• 02(提交)、03(部分发货)状态的单据才需要被汇总数量。
• 领用汇总量(未发数量)=领用数量-已发数量，因为已发数量会在实际库存量中减去，所以我们要汇总的始终应该是未发数量。

以上几点确定后，接下来就是SQL实现，我们分成三步：

1. 单张表数量汇总

表一(pro_out_a) 未发货量汇总

SELECT
poa.productNo,
sum(GREATEST(NVL(poa.outNum,0)-NVL(poa.alOutNum,0),0)) AS num
FROM pro_out_a poa
where poa.billStatus in ('02','03')
group by
poa.productNo

表二(pro_out_b)、表三(pro_out_c) 汇总方式和表一一样。

2. 三张表汇总数量联合汇总

三张表的量各自汇总好之后，我们接下来要做的就是将三张表的汇总量再汇总到一起，没错，用 UNION ALL。

SELECT
productNo,
sum(num)
FROM
(
	(
		SELECT
		poa.productNo,
		sum(GREATEST(NVL(poa.outNum,0)-NVL(poa.alOutNum,0),0)) AS num
		FROM pro_out_a poa
		where poa.billStatus in ('02','03')
		group by
		poa.productNo
	)
	UNION ALL
	(
		SELECT
		pob.productNo,
		sum(GREATEST(NVL(pob.outNum,0)-NVL(pob.alOutNum,0),0)) AS num
		FROM pro_out_b pob
		where pob.billStatus in ('02','03')
		group by
		pob.productNo
	)
	UNION ALL
	(
		SELECT
		poc.productNo,
		sum(GREATEST(NVL(poc.outNum,0)-NVL(poc.alOutNum,0),0)) AS num
		FROM pro_out_a poc
		where poc.billStatus in ('02','03')
		group by
		poc.productNo
	)
)
GROUP BY
productNo

3. 结合实际库存量进行汇总

将三张表汇总到一起之后，实际上就已经得到了每个产品需要被减去的数量，这时我们再用实际库存量减去这个汇总量，不就得到了剩余库存量(新单子能被领取的最大量)吗？继续写：

SELECT
pi.productNo,
(NVL(pi.num,0)-NVL(t1.num,0)) AS num,
FROM pro_inventory pi
LEFT JOIN
(
	SELECT
	productNo,
	sum(num) AS num
	FROM
	(
		(
			SELECT
			poa.productNo,
			sum(GREATEST(NVL(poa.outNum,0)-NVL(poa.alOutNum,0),0)) AS num
			FROM pro_out_a poa
			where poa.billStatus in ('02','03')
			group by
			poa.productNo
		)
		UNION ALL
		(
			SELECT
			pob.productNo,
			sum(GREATEST(NVL(pob.outNum,0)-NVL(pob.alOutNum,0),0)) AS num
			FROM pro_out_b pob
			where pob.billStatus in ('02','03')
			group by
			pob.productNo
		)
		UNION ALL
		(
			SELECT
			poc.productNo,
			sum(GREATEST(NVL(poc.outNum,0)-NVL(poc.alOutNum,0),0)) AS num
			FROM pro_out_a poc
			where poc.billStatus in ('02','03')
			group by
			poc.productNo
		)
	)
	GROUP BY
	productNo
) t1
on pi.productNo = t1.productNo

到这里就完成了所有步骤，单据操作表由之前的库存表(pro_inventory）换成可领库存表(pro_inventory_new)之后，现在新单子看到的库存量就不是实际库存量了，而是实际库存量减去了其他单据的领用量后的剩余量。

…

经理花了大概不到10分钟就写完了所有SQL，他放下笔靠在桌子上久久无语。又过了一会儿，他看了一眼还在角落抽泣的我，松了松领带，然后点了一支烟慢慢地抽。我看着烟雾不断在经理头上飘荡游曳，不知道他究竟在想什么，一旁的小芳也一直盯着经理看，她神态盎然，两眼流光，仿佛看见了宇宙繁星。