› 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
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 1456 天前的主题，其中的信息可能已经有所发展或是发生改变。
没有别的意思就是想让大家，长长见识~ 能看懂算我输
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 条附言 · 2020-12-24 09:24:22 +08:00
主要是这个 sql 是 mysql 慢日志里面搜出来的
业务是前端界面 用户可以任意点击。。。。
业务是前端界面 用户可以任意点击。。。。
109 条回复 • 2020-12-25 16:51:06 +08:00
 |
101
CX 2020-12-24 19:19:09 +08:00
v 站发出来的最高纪录不是 2000 行吗
|
 |
102
stdout 2020-12-24 19:21:18 +08:00
比这多的我都见过。超级痛苦。
|
 |
103
lau52y 2020-12-24 23:01:01 +08:00
一看就是某个人偷懒了,结果玩脱了
|
 |
104
jin7 2020-12-25 08:39:55 +08:00 via Android
这不是很正常
|
 |
105
guanhui07 2020-12-25 08:59:57 +08:00
v 站发出来的最高纪录不是 2000 行吗
|
 |
106
leon9986666 2020-12-25 09:12:57 +08:00
做统计的见过比这复杂多的
|
 |
107
nl101531 2020-12-25 09:18:02 +08:00 via iPhone
在数仓挺常见的。。。
|
 |
108
weizhen199 2020-12-25 09:38:59 +08:00
|
 |
109
kiripeng 2020-12-25 16:51:06 +08:00
其实还行。。。
|
Select Language