V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
funbox
V2EX  ›  MySQL

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

  •  3
     
  •   funbox · 298 天前 · 11418 次点击
    这是一个创建于 298 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    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
    
    第 1 条附言  ·  298 天前
    主要是这个 sql 是 mysql 慢日志里面搜出来的
    业务是前端界面 用户可以任意点击。。。。
    109 条回复    2020-12-25 16:51:06 +08:00
    1  2  
    CX
        101
    CX   297 天前
    v 站发出来的最高纪录不是 2000 行吗
    stdout
        102
    stdout   297 天前
    比这多的我都见过。超级痛苦。
    lau52y
        103
    lau52y   297 天前
    一看就是某个人偷懒了,结果玩脱了
    jin7
        104
    jin7   297 天前 via Android
    这不是很正常
    guanhui07
        105
    guanhui07   297 天前
    v 站发出来的最高纪录不是 2000 行吗
    leon9986666
        106
    leon9986666   297 天前
    做统计的见过比这复杂多的
    nl101531
        107
    nl101531   297 天前 via iPhone
    在数仓挺常见的。。。
    weizhen199
        108
    weizhen199   297 天前
    @lau52y 哈哈哈,我来尝试回访下作者心情
    某猿:欸,领导又要整个表
    :诶,这临表好烦还要建立,好想 select into #
    :算了直接上
    ......
    kiripeng
        109
    kiripeng   296 天前
    其实还行。。。
    1  2  
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3959 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 03:30 · PVG 11:30 · LAX 20:30 · JFK 23:30
    ♥ Do have faith in what you're doing.