select distinct brands.*
from product_prices
inner join products on products.id = product_prices.product_id
inner join brands on brands.id = products.brand_id
inner join quotation_batches on product_prices.batch_id = quotation_batches.id
where
(quotation_batches.id =76897 OR quotation_batches.group_id =76897)
and products.publish_stage IN (0,1)
and brands.status NOT IN (1,2)
首先粗略猜測一下表名整理關(guān)系：
產(chǎn)品(products)娜庇，都有一個(gè)品牌(brands)星爪，每個(gè)產(chǎn)品都會(huì)有多個(gè)報(bào)價(jià)(product_prices)笛粘，每個(gè)批次(quotation_batches)也都會(huì)有對(duì)應(yīng)的多個(gè)報(bào)價(jià)（這個(gè)關(guān)系稍微復(fù)雜一點(diǎn)）
對(duì)應(yīng)到數(shù)據(jù)量其實(shí)就比較明朗了胚鸯，數(shù)據(jù)最小的應(yīng)該是品牌brands钻弄，可能會(huì)有10w的數(shù)據(jù)乖坠，品牌對(duì)應(yīng)的產(chǎn)品products可能就要乘個(gè)10矮台，就算100w好了漓概，product_prices應(yīng)該會(huì)有1000w漾月，批次quotation_batches跟產(chǎn)品products沒有直接關(guān)系，而一個(gè)quotation_batch也會(huì)對(duì)應(yīng)多個(gè)product_prices胃珍，批次的數(shù)據(jù)跟products差不多梁肿，也算100w好了
數(shù)據(jù)大小我們也有底了：brands(10w),products(100w),quotation_batches(100w),product_prices(1000w)
分析一下sql的目的：找出符合條件的品牌(brands)
條件：某批次或者某一組批次的 且 產(chǎn)品階段在xx范圍 且 產(chǎn)品品牌狀態(tài)不在xx范圍
分析一下這句sql的邏輯： 通過產(chǎn)品價(jià)格找到產(chǎn)品，再找產(chǎn)品的品牌觅彰，再通過批次報(bào)價(jià)找到批次……
以上分析完畢吩蔑，開始修改
<pre>
最終需要找到brands，數(shù)據(jù)表不大填抬，最終結(jié)果去distinct不會(huì)太耗資源
select distinct brands.*
以能找出數(shù)據(jù)最少的表為主表
from quotation_batches
通過批次找到批次價(jià)格(保證這個(gè)大家伙是被join的)
INNER JOIN product_prices
ON product_prices.batch_id = quotation_batches.id
通過產(chǎn)品價(jià)格的產(chǎn)品id找到產(chǎn)品
INNER JOIN products
ON products.id = product_prices.product_id
找到品牌
INNER JOIN brands
ON products.brand_id = brands.id
where
(quotation_batches.id =76897 OR quotation_batches.group_id =76897)
and products.publish_stage IN (0,1)
最好改成in(x,x,x,x)
and brands.status NOT IN (1,2)
</pre>
