日常涨知识系列-惊天地泣鬼神的 sql， 300 行~~

2020-12-23 14:19:12 +08:00
 funbox

没有别的意思就是想让大家，长长见识~ 能看懂算我输

SELECT
	* 
FROM
	(
	SELECT
		`inventory`.`id` AS `inventoryId`,
		`inventory`.`location_id` AS `locationId`,
		`inventory`.`storehouse_id` AS `storehouseId`,
		`product`.`id` AS `productId`,
		`product`.`name` AS `name`,
		`product`.`number` AS `number`,
		`brand`.`name` AS `brandName`,
		`product`.`size` AS `size`,
		`category`.`name` AS `categoryName`,
		`product`.`unit` AS `unit`,
		`storehouse`.`name` AS `storehouseName`,
		`location`.`name` AS `locationName`,
		IFNULL( inventory.quantity, 0 ) AS `remainQuantity`,
		IFNULL( inventoryBath.avgPrice, 0 ) AS `avgPrice`,
		IFNULL( inventoryBath.totalPrice, 0 ) AS `totalPrice`,
		`inventoryWarning`.`lowerLimit` AS `lowerLimit`,
		`inventoryWarning`.`upperLimit` AS `upperLimit`,(
			IFNULL( repairOrderItem.repairRemainQuantity, 0 )+ IFNULL( purchaseReturnOrderItem.purchaseReturnRemainQuantity, 0 )+ IFNULL( otherOutOrderItem.otherInOutstockQuantity, 0 )) AS `waitOutQuantity`,(
		IFNULL( purchaseOrderItem.purchaseRemainQuantity, 0 )+ IFNULL( otherInOrderItem.otherInOutstockQuantity, 0 )+ IFNULL( onlineInOrderItem.onlineRemainInstockQuantity, 0 )) AS `waitIntQuantity`,
		`product`.`barcode` AS `barcode`,
		`supplier`.`name` AS `supplierName`,
		`product`.`valid_period` AS `validPeriod`,
		`product`.`standard_id` AS `standardId`,
		`product`.`from_id` AS `fromId`,
		`product`.`source` AS `source`,
		`oeTable`.`oeNumber` AS `oeNumber`,
		`product`.`vehicle_note` AS `vehicleNote`,
		`product`.`description` AS `note`,
		`store`.`name` AS `storeName`,
		`product`.`create_time` AS `createTime`,
		`inventory`.`tenant_id`,
		`product`.`sale_price1` AS `productSalePrice`,
		`product`.`price_mode` AS `productPriceMode`,
		`product`.`markup_type` AS `productMarkupType`,
		IFNULL( lastPurchasePrice.lastPurchasePrice, 0 ) AS `lastPurchasePrice`,(
		CASE
				
				WHEN product.price_mode = 1 THEN
				product.sale_price1 
				WHEN product.price_mode = 2 
				AND product.markup_type = 1 
				AND IFNULL( inventory.quantity, 0 )= 0 THEN
					IFNULL( lastOutPrice.unitPrice, 0 )* product.sale_price1 
					WHEN product.price_mode = 2 
					AND product.markup_type = 1 
					AND IFNULL( inventory.quantity, 0 ) != 0 THEN
						IFNULL( inventoryBath.avgPrice, 0 )* product.sale_price1 
						WHEN product.price_mode = 2 
						AND product.markup_type != 1 THEN
							IFNULL( lastPurchasePrice.lastPurchasePrice, 0 )* product.sale_price1 
						END 
						) AS salePrice 
					FROM
						`inventory`
						LEFT JOIN `product` ON `inventory`.`product_id` = `product`.`id`
						LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
						LEFT JOIN `brand` ON `product`.`brand_id` = `brand`.`id`
						LEFT JOIN `storehouse` ON `inventory`.`storehouse_id` = `storehouse`.`id`
						LEFT JOIN `location` ON `inventory`.`location_id` = `location`.`id`
						LEFT JOIN `supplier` ON `product`.`supplier_id` = `supplier`.`id`
						LEFT JOIN `store` ON `inventory`.`store_id` = `store`.`id`
						LEFT JOIN (
						SELECT
							SUBSTRING_INDEX(
								GROUP_CONCAT( unit_price ORDER BY outstock_time DESC ),
								',',
								1 
							) AS `unitPrice`,
							`product_id` AS `productId` 
						FROM
							`inventory_batch` 
						WHERE
							( `store_id` = '27358' ) 
							AND ( `inventory_batch`.`tenant_id` = 1 ) 
						GROUP BY
							`product_id` 
						) `lastOutPrice` ON `inventory`.`product_id` = lastOutPrice.productId
						LEFT JOIN (
						SELECT
							group_concat( product_number.number SEPARATOR ";" ) AS `oeNumber`,
						`product_number`.`product_id` AS `productId` 
						FROM
							`product_number` 
						WHERE
							( `product_number`.`type` = 1 ) 
							AND ( `product_number`.`tenant_id` = 1 ) 
						GROUP BY
							`product_number`.`product_id` 
						) `oeTable` ON `inventory`.`product_id` = oeTable.productId
						LEFT JOIN (
						SELECT
							`storehouse_id` AS `storehouseId`,
							SUM( quantity )- SUM( outstock_quantity ) AS `remainQuantity`,
							SUM(
								unit_price *(
									quantity - outstock_quantity 
								)) AS `totalPrice`,
							`product_id` AS `productId`,
						IF
							(
								inventoryBathAll.currentQuantity = 0,
								inventoryBathLast.unitPrice,
							round( inventoryBathAll.avgPrice, 2 )) AS `avgPrice` 
						FROM
							`inventory_batch`
							LEFT JOIN (
							SELECT
								`product_id` AS `productId`,
								SUM( quantity )- SUM( outstock_quantity ) AS `currentQuantity`,
								CONVERT (
									SUM(
										unit_price *(
											quantity - outstock_quantity 
										))/(
									SUM( quantity )- SUM( outstock_quantity )),
								DECIMAL ( 20, 2 )) AS `avgPrice` 
							FROM
								`inventory_batch` 
							WHERE
								( `store_id` = '27358' ) 
								AND ( `inventory_batch`.`tenant_id` = 1 ) 
							GROUP BY
								`product_id` 
							) `inventoryBathAll` ON `inventory_batch`.`product_id` = inventoryBathAll.productId
							LEFT JOIN (
							SELECT
								SUBSTRING_INDEX(
									GROUP_CONCAT( unit_price ORDER BY batch_number DESC ),
									",",
									1 
								) AS `unitPrice`,
								`product_id` AS `productId`,
								max( batch_number ) AS `batchNumber` 
							FROM
								`inventory_batch` 
							WHERE
								( `store_id` = '27358' ) 
								AND ( `inventory_batch`.`tenant_id` = 1 ) 
							GROUP BY
								`product_id` 
							ORDER BY
								`batch_number` DESC 
							) `inventoryBathLast` ON `inventory_batch`.`product_id` = inventoryBathLast.productId 
						WHERE
							( `store_id` = '27358' ) 
							AND ( `inventory_batch`.`tenant_id` = 1 ) 
						GROUP BY
							`product_id`,
							`storehouse_id` 
						) `inventoryBath` ON ( `inventory`.`product_id` = inventoryBath.productId ) 
						AND ( `inventory`.`storehouse_id` = inventoryBath.storehouseId )
						LEFT JOIN (
						SELECT
							`product_id` AS `productId`,
							`lower_limit` AS `lowerLimit`,
							`upper_limit` AS `upperLimit` 
						FROM
							`inventory_warning` 
						WHERE
							( `store_id` = '27358' ) 
						AND ( `inventory_warning`.`tenant_id` = 1 )) `inventoryWarning` ON `inventory`.`product_id` = inventoryWarning.productId
						LEFT JOIN (
						SELECT
							SUM( repair_order_product_item.quantity - repair_order_product_item.use_quantity ) AS `repairRemainQuantity`,
							`repair_order_product_item`.`product_id` AS `productId` 
						FROM
							`repair_order_product_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`repair_order` 
							WHERE
								( `status` != 21000 ) 
								AND ( `repair_order`.`store_id` = '27358' ) 
							AND ( `repair_order`.`tenant_id` = 1 )) `repairOrder` ON `repair_order_product_item`.`order_id` = repairOrder.id
							INNER JOIN (
							SELECT DISTINCT
								`product_id` 
							FROM
								`inventory` 
							WHERE
								( `store_id` = '27358' ) 
								AND ( `inventory`.`tenant_id` = 1 ) 
							GROUP BY
								`product_id` 
							) `inventoryQue` ON `repair_order_product_item`.`product_id` = inventoryQue.product_id 
						WHERE
							`repair_order_product_item`.`tenant_id` = 1 
						GROUP BY
							`repair_order_product_item`.`product_id` 
						) `repairOrderItem` ON `inventory`.`product_id` = repairOrderItem.productId
						LEFT JOIN (
						SELECT
							SUM( purchase_return_order_item.quantity - purchase_return_order_item.outstock_quantity ) AS `purchaseReturnRemainQuantity`,
							`purchase_return_order_item`.`product_id` AS `productId` 
						FROM
							`purchase_return_order_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`purchase_return_order` 
							WHERE
								(
								`purchase_return_order`.`status` IN ( 12000, 13000 )) 
								AND ( `purchase_return_order`.`store_id` = '27358' ) 
							AND ( `purchase_return_order`.`tenant_id` = 1 )) `purchaseReturnOrder` ON `purchase_return_order_item`.`order_id` = purchaseReturnOrder.id 
						WHERE
							`purchase_return_order_item`.`tenant_id` = 1 
						GROUP BY
							`purchase_return_order_item`.`product_id` 
						) `purchaseReturnOrderItem` ON `inventory`.`product_id` = purchaseReturnOrderItem.productId
						LEFT JOIN (
						SELECT
							SUM( other_in_out_stock_order_item.quantity ) AS `otherInOutstockQuantity`,
							`other_in_out_stock_order_item`.`product_id` AS `productId` 
						FROM
							`other_in_out_stock_order_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`other_in_out_stock_order` 
							WHERE
								( `other_in_out_stock_order`.`type` = 1 ) 
								AND ( `other_in_out_stock_order`.`status` = 1 ) 
								AND ( `other_in_out_stock_order`.`store_id` = '27358' ) 
							AND ( `other_in_out_stock_order`.`tenant_id` = 1 )) `otherInOutOrder` ON `other_in_out_stock_order_item`.`order_id` = otherInOutOrder.id 
						WHERE
							`other_in_out_stock_order_item`.`tenant_id` = 1 
						GROUP BY
							`other_in_out_stock_order_item`.`product_id` 
						) `otherOutOrderItem` ON `inventory`.`product_id` = otherOutOrderItem.productId
						LEFT JOIN (
						SELECT
							SUM( purchase_order_item.quantity - purchase_order_item.instock_quantity ) AS `purchaseRemainQuantity`,
							`purchase_order_item`.`product_id` AS `productId` 
						FROM
							`purchase_order_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`purchase_order` 
							WHERE
								(
								`purchase_order`.`status` IN ( 12000, 13000 )) 
								AND ( `purchase_order`.`store_id` = '27358' ) 
							AND ( `purchase_order`.`tenant_id` = 1 )) `purchaseOrder` ON `purchase_order_item`.`order_id` = purchaseOrder.id 
						WHERE
							`purchase_order_item`.`tenant_id` = 1 
						GROUP BY
							`purchase_order_item`.`product_id` 
						) `purchaseOrderItem` ON `inventory`.`product_id` = purchaseOrderItem.productId
						LEFT JOIN (
						SELECT
							SUM( other_in_out_stock_order_item.quantity ) AS `otherInOutstockQuantity`,
							`other_in_out_stock_order_item`.`product_id` AS `productId` 
						FROM
							`other_in_out_stock_order_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`other_in_out_stock_order` 
							WHERE
								( `other_in_out_stock_order`.`type` = 2 ) 
								AND ( `other_in_out_stock_order`.`status` = 1 ) 
								AND ( `other_in_out_stock_order`.`store_id` = '27358' ) 
							AND ( `other_in_out_stock_order`.`tenant_id` = 1 )) `otherInOutOrder` ON `other_in_out_stock_order_item`.`order_id` = otherInOutOrder.id 
						WHERE
							`other_in_out_stock_order_item`.`tenant_id` = 1 
						GROUP BY
							`other_in_out_stock_order_item`.`product_id` 
						) `otherInOrderItem` ON `inventory`.`product_id` = otherInOrderItem.productId
						LEFT JOIN (
						SELECT
							SUM( online_purchase_order_item.quantity - online_purchase_order_item.instock_quantity ) AS `onlineRemainInstockQuantity`,
							`online_purchase_order_item`.`product_id` AS `productId` 
						FROM
							`online_purchase_order_item`
							INNER JOIN (
							SELECT
								* 
							FROM
								`online_purchase_order` 
							WHERE
								( `online_purchase_order`.`status` = 1 ) 
								AND ( `online_purchase_order`.`store_id` = '27358' ) 
							AND ( `online_purchase_order`.`tenant_id` = 1 )) `onlineOrder` ON `online_purchase_order_item`.`order_id` = onlineOrder.id 
						WHERE
							`online_purchase_order_item`.`tenant_id` = 1 
						GROUP BY
							`online_purchase_order_item`.`product_id` 
						) `onlineInOrderItem` ON `inventory`.`product_id` = onlineInOrderItem.productId
						LEFT JOIN (
						SELECT
							`product_id` AS `productId`,
							`last_price` AS `lastPurchasePrice` 
						FROM
							`purchase_price` 
						WHERE
							( `store_id` = '27358' ) 
						AND ( `purchase_price`.`tenant_id` = 1 )) `lastPurchasePrice` ON `inventory`.`product_id` = lastPurchasePrice.productId 
					WHERE
						( `product`.`status` != 90 ) 
						AND ( `inventory`.`store_id` = '27358' ) 
					AND ( `inventory`.`tenant_id` = 1 )) `table` 
				WHERE
					`table`.`tenant_id` = 1 
				ORDER BY
				`createTime` DESC 
	LIMIT 10
13393 次点击
所在节点    MySQL
109 条回复
iRiven
2020-12-23 17:25:03 +08:00
嵌套多层，可以用 with as 把水平代码拆分成垂直，更好理解。
lap510200
2020-12-23 17:35:35 +08:00
联了很多张表 嵌套子查询 临时表 效率低下 但是写的人也蛮苦了 至少根据理清业务逻辑写出来这些 sql 加上调试这打断 sql 是否满足需求 这些所花费的时间估计至少 1 天

除此之外把技术总监拖出去毙了 居然没有代码审核
sampeng
2020-12-23 17:42:37 +08:00
才 300 行。。见过几千行的么？弟弟。。。
kisshere
2020-12-23 17:42:55 +08:00
MySQL：你是不是觉得你很幽默？
KaynW
2020-12-23 17:43:42 +08:00
我只看到了 27358
quan01994
2020-12-23 17:54:39 +08:00
还好啦
IrisFrankie
2020-12-23 17:57:12 +08:00
挺整齐的，命名也没啥问题。你理解需求后再读应该会挺简单的。。。不要试图否定它，除非你能优化它
unicloud
2020-12-23 18:00:03 +08:00
打扰了。。。
DarkCat123
2020-12-23 18:04:06 +08:00
这是人类文明的精华。
OldHu
2020-12-23 18:21:34 +08:00
见过 sql 保存成文本文件有 2MB 的吗？ 你们啊，too naive ！

ERP 系统啥的，长 sql 还是很多的。 所以，ERP 系统大部分还是 Oracle 这种商业数据库多。
MySQL 是借着互联网起来的。业务相对简单，开发人员多，很多逻辑用开发代码解决，而不是用数据库。

我以前做 Oracle ERP 的很多业务开发，就是 plsql + shell， 就跟现在后端写 java 一样。

v2ex 大部分都是互联网从业者为主，还有很多朋友是前端，所以对这样有点 old style 的东西很陌生，陌生了又容易导致反感。
limuyan44
2020-12-23 18:25:13 +08:00
才 300 行而已，刚毕业那会写了半年 sql,300 行算是起步了。
la2la
2020-12-23 18:25:17 +08:00
@OldHu 对的，oracle erp 系统，包括之前的一些报表系统，300 行基本操作
Vegetable
2020-12-23 18:27:23 +08:00
这个虽然长，但是逻辑不复杂吧，只是字段多而已。
mengdodo
2020-12-23 18:29:22 +08:00
牛皮
yeqizhang
2020-12-23 18:29:36 +08:00
感觉 erp 报表常见，反正数仓数据处理的我是见过写很多行的
optional
2020-12-23 18:34:58 +08:00
不说 erp，搞数据分析也觉得这个没什么啊
Mohanson
2020-12-23 18:40:41 +08:00
哈哈, 楼主本来想带节奏, 结果楼下一堆人才把楼主描述成一个"没见识的"...
liuxey
2020-12-23 18:54:50 +08:00
PL/SQL：行数算什么，调用栈堪比 Java
mamahaha
2020-12-23 18:56:00 +08:00
这是不想让人抢饭碗吧？升级业务老老实实还得找他。
Umenezumi
2020-12-23 21:49:31 +08:00
数据分析那边儿经常是这种 SQL 啊，这个还算逻辑清晰了

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/738226

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX