mysql进行sum多个表多个字段的时候数据很大的问题的解决以及6表联合复杂结构查询

首先要说明一点,这是一个非常复杂的数据结构,对于精通mysql的人来说,理解很好理解.但是可能还有更大的速度提升空间.

这种涉及到6个表的查询,使用一句话来完成,7000多商品,总数据量应该是十万级,200毫秒应该还是可以接受了.毕竟使用的rds性能也不是特别好.

而且多个表在一句话里面来进行查询的话,有助于练习mysql思维而且灵动性比较好.视图多是很方便写语句,但是效率有时候非常低.

本文主要着重于解决如下问题,都是属于个人笔记

1 如何用一条语句执行多个表的联合查询并提升效率

2 多个表联合查询的时候,sum函数数字很大不准确的问题解决,比如表a,b,c 都有字段需要进行sum统计的错误结果解决

3 left join 的使用

 

用到的表有:

1.商品信息表

2.出库明细表

3.出库单表

4.入库明细表

5.入库单表

6.每次盘点情况的信息表

商品媒体信息表

 

解决如下需求:

1 一条语句罗列并统计出来:itemid,name,imgurl,incount,outcount,hascount,outpopolaration

分别意思为 商品编号,名称,图片url,本阶段总出库数量,本阶段总出库数量,本阶段出库商品的流行度

信息分别来源于:

商品表,商品表,商品媒体信息表,入库明细表,出库明细表,临时创建的表,出库信息表

其中有没展示数据的表为:盘点信息表,入库单表,出库单表.   这三个表都是做关联用的

另外上面提到一个 临时创建的表   有hascount 字段  其实不是表,而是直接用两个列的减法来做的.

 

先贴出来最终的查询语句

 

SELECT
	a.itemid,
	a.`name`,
	d.filepath AS imgurl,
	c.incount,
	b.outcount,
	(c.incount - b.outcount) AS hascount,
	b.outpopularation
FROM
	iteminfo a
LEFT JOIN (
	(
		SELECT
			outstockbilldetial.itemid,
			sum(outstockbilldetial.count) AS outcount,
			ifnull(count(*) ,- 1) AS outpopularation
		FROM
			outstockbilldetial,
			outstockbill
		WHERE
			outstockbill.Time > (
				SELECT
					max(FinishTime)
				FROM
					ventorycheckbatchinfo
			)
		AND outstockbill.Canceled = 0
		AND outstockbill.OutStockBillId = outstockbilldetial.OutStockBillId
		GROUP BY
			itemid
	) AS b
	INNER JOIN (
		SELECT
			instockbilldetial.itemid,
			ifnull(
				sum(instockbilldetial.count),
				0
			) AS incount
		FROM
			instockbilldetial,
			instockbill
		WHERE
			instockbill.Time > (
				SELECT
					max(FinishTime)
				FROM
					ventorycheckbatchinfo
			)
		AND instockbill.Canceled = 0
		AND instockbill.InStockBillId = instockbilldetial.InStockBillId
		GROUP BY
			itemid
	) AS c ON b.itemid = c.itemid
) ON a.itemid = b.itemid
LEFT JOIN itemmediainfo AS d ON a.itemid = d.itemid
GROUP BY
	a.itemid
ORDER BY
	hascount ASC

 

 

再贴出来最后的查询结果 

 

然后 我们发现 查询的数据中,有的是有入库没有出库的,那么连入库记录都看不到了,也自然看不到实际的库存了.这怎么办?

 把入库记录挪到前面去. 这样就能保证商品和入库记录都有,然后虽然有的商品入库了没出库,那也离统计出来更进一步了

SELECT
	a.itemid,
	a.`name`,
	d.filepath AS imgurl,
	c.incount,
	b.outcount,
	(c.incount - b.outcount) AS hascount,
	b.outpopularation
FROM
	iteminfo a
LEFT JOIN (
		SELECT
			instockbilldetial.itemid,
			ifnull(
				sum(instockbilldetial.count),
				0
			) AS incount
		FROM
			instockbilldetial,
			instockbill
		WHERE
			instockbill.Time > (
				SELECT
					max(FinishTime)
				FROM
					ventorycheckbatchinfo
			)
		AND instockbill.Canceled = 0
		AND instockbill.InStockBillId = instockbilldetial.InStockBillId
		GROUP BY
			itemid
	) AS c ON a.itemid = c.itemid

LEFT JOIN (
	(
		SELECT
			outstockbilldetial.itemid,
			ifnull(sum(outstockbilldetial.count),-1000) AS outcount,
			ifnull(count(*),-1000) AS outpopularation
		FROM
			outstockbilldetial,
			outstockbill
		WHERE
			outstockbill.Time > (
				SELECT
					max(FinishTime)
				FROM
					ventorycheckbatchinfo
			)
		AND outstockbill.Canceled = 0
		AND outstockbill.OutStockBillId = outstockbilldetial.OutStockBillId
		GROUP BY
			itemid
	) AS b) on b.itemid = c.itemid
LEFT JOIN itemmediainfo AS d ON a.itemid = d.itemid

GROUP BY
	a.itemid
ORDER BY
	outpopularation ASC

再次进行改善以后,出库如果是没有没有记录的话,也不影响hascount也就是剩余库存的统计

另外 popularation 字段,如果是没有出库过的,就标记为-1

总记录条数 7820条 改善后  0.23秒

 

 

然后如果我们查询商品有库存但是没有出库记录的  

我们加一下where查询条件即可

where 
(
        c.incount - ifnull(b.outcount, 0)
    ) >0

 

 

然后我们再根据拥有库存的多少,来排序一下滞销品

SELECT
	a.itemid,
	a.`name`,
	d.filepath AS imgurl,
	c.incount,
	b.outcount,
	(
		c.incount - ifnull(b.outcount, 0)
	) AS hascount,
	ifnull(b.outpopularation, - 1) AS popularation
FROM
	iteminfo a
LEFT JOIN (
	SELECT
		instockbilldetial.itemid,
		ifnull(
			sum(instockbilldetial.count),
			0
		) AS incount
	FROM
		instockbilldetial,
		instockbill
	WHERE
		instockbill.Time > (
			SELECT
				max(FinishTime)
			FROM
				ventorycheckbatchinfo
		)
	AND instockbill.Canceled = 0
	AND instockbill.InStockBillId = instockbilldetial.InStockBillId
	GROUP BY
		itemid
) AS c ON a.itemid = c.itemid
LEFT JOIN (
	(
		SELECT
			outstockbilldetial.itemid,
			ifnull(
				sum(outstockbilldetial.count) ,- 1000
			) AS outcount,
			ifnull(count(*) ,- 1000) AS outpopularation
		FROM
			outstockbilldetial,
			outstockbill
		WHERE
			outstockbill.Time > (
				SELECT
					max(FinishTime)
				FROM
					ventorycheckbatchinfo
			)
		AND outstockbill.Canceled = 0
		AND outstockbill.OutStockBillId = outstockbilldetial.OutStockBillId
		GROUP BY
			itemid
	) AS b
) ON b.itemid = c.itemid
LEFT JOIN itemmediainfo AS d ON a.itemid = d.itemid
WHERE
	(
		c.incount - ifnull(b.outcount, 0)
	) > 0
AND outpopularation IS NULL
GROUP BY
	a.itemid
ORDER BY
	hascount DESC

如果查询滞销的40条商品 加上 limit 40就可以了