我有以下需要優化的查詢
Select
1 As status,
e.entity_id,
e.attribute_set_id,
e.type_id,
e.created_at,
e.updated_at,
e.sku,
e.name,
e.short_description,
e.image,
e.small_image,
e.thumbnail,
e.url_key,
e.free,
e.number_of_downloads,
e.sentence1,
e.url_path
From
catalog_product_flat_1 As e
Inner Join catalog_category_product_index_store1 As cat_index
On cat_index.product_id = e.entity_id And
cat_index.store_id = 1 And
cat_index.visibility In (3, 2, 4) And
cat_index.category_id = '2'
Where
e.entity_id Not In (13863, 14096, 13856, 13924, 15875, 15869, 13788, 15977, 15873, 17141, 22214, 16900, 14485,
15628, 15656, 14220, 14259, 14284, 13875, 13216, 14168, 13892, 16540, 19389, 17286, 16591, 30178, 31517, 31734,
31621, 2487, 2486, 2485, 2484, 2483, 2482, 2481, 2480, 2479, 2478, 2477, 2475, 2474, 2473, 13402, 13427, 13694,
13774, 13804, 13837, 13849, 13864, 30299, 30300) And
e.free = 1
Order By
e.number_of_downloads Desc;
這里在 NOT IN() 中傳遞的 id 是來自名為“mcsdownloads”的表中的“product_id”列值
所以我的目標是用表“mcsdownloads”上的 JOIN 操作替換 NOT IN。
請幫忙 ！
uj5u.com熱心網友回復：
我認為沒有一種方法可以執行聯接而不是不加入，但是您可以按以下方式排列代碼，使其正常作業，而無需手動寫下表格中product_id的所有內容mcsdownloads
SELECT
1 AS status, e.entity_id, e.attribute_set_id,
e.type_id, e.created_at, e.updated_at, e.sku,
e.name, e.short_description, e.image, e.small_image,
e.thumbnail, e.url_key, e.free, e.number_of_downloads,
e.sentence1, e.url_path
FROM catalog_product_flat_1 AS e
INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id
AND cat_index.store_id=1
AND cat_index.visibility IN (3,2,4)
AND cat_index.category_id='2'
WHERE (e.entity_id NOT IN (SELECT product_id FROM mcsdownloads))
AND (e.free = 1)
ORDER BY e.number_of_downloads DESC;
這是我改變的重要部分
e.entity_id NOT IN (SELECT product_id FROM mcsdownloads)
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/515042.html
標籤：mysql加入诺丁