首先要说明一点,这是一个非常复杂的数据结构,对于精通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就可以了
