gpt4 book ai didi

mysql - 如何添加计数和清理/简化涉及 4 个 INNER JOIN 和 2 个 LEFT JOIN 的派生查询

转载 作者:行者123 更新时间:2023-11-29 14:28:06 26 4
gpt4 key购买 nike

我对 MySQL 的派生查询不熟悉,并且在两件事上遇到了麻烦。

1) 如何引用 t1.dCount 以便计算每个 optionId 的 xcart_images_D.productId 数量?

2) 如何简化此查询的派生 SELECT? (目前此查询需要 45 秒才能运行)

 SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path, t1.dCount
FROM xcart_products xp
INNER JOIN xcart_variants xv ON xp.productid = xv.productid
INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid
INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid
INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR'
LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid
LEFT JOIN (
SELECT COUNT(xiD.optionid) as dCount
FROM xcart_products xp2
INNER JOIN xcart_classes xc2 ON xp2.productid = xc2.productid AND xc2.class = 'Color'
INNER JOIN xcart_class_options xco2 ON xc2.classid = xco2.classid
LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco2.optionid
) as t1 ON xiW.id = xvi.variantid
GROUP BY xco.optionid
ORDER by xp.product DESC

这是计数的工作版本,但没有派生选择 -

SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiD.image_path, xiD.path_on_server, count(xiD.optionid) as cnt
FROM xcart_products xp
INNER JOIN xcart_classes xc ON xp.productid = xc.productid AND xc.class = 'Color'
INNER JOIN xcart_class_options xco ON xc.classid = xco.classid
LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco.optionid
GROUP BY xp.product, xco.optionid
ORDER by xp.product DESC

如果需要,这里是数据库布局 -

+ xcart_products
- productid*
- product
+ xcart_variants
- variantid*
- productid (xcart_products.productid)
+ xcart_variant_items [bridge table]
- optionid*
- variantid (xcart_variants.variantid)
+ xcart_classes
- classid*
- productid (xcart_products.productid)
- class
+ xcart_class_options
- optionid*
- option_name
- classid (xcart_classes.classid)
+ xcart_images_W
- imageid*
- id (xcart_variants.variantid)
- image_path
+ xcart_images_D
- imageid* [not relational with xcart_images_W.imageid]
- id (xcart_products.productid)
- optionid (xcart_class_options.optionid)

* Primary Key
() relational data
[] notes

最佳答案

这是您的原始查询,其中包含一些描述我认为问题所在的注释:

 SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path, t1.dCount
FROM xcart_products xp
INNER JOIN xcart_variants xv ON xp.productid = xv.productid
INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid
INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid
INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR'
LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid
-- all of the above is pretty standard.

-- but this part is a little wonky!
LEFT JOIN (
SELECT COUNT(xiD.optionid) as dCount
FROM xcart_products xp2
INNER JOIN xcart_classes xc2 ON xp2.productid = xc2.productid AND xc2.class = 'Color'
INNER JOIN xcart_class_options xco2 ON xc2.classid = xco2.classid
LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco2.optionid
) as t1 ON xiW.id = xvi.variantid
-- The inner query here only returns one column, which means it's
-- *not related* to anything. That's why, outside, you're using an
-- ON query that has nothing to do with the data inside the table
-- you're joining! You need to group by in the inner select.

GROUP BY xco.optionid
ORDER by xp.product DESC

尝试如下:

SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path, t1.dCount
FROM xcart_products xp
INNER JOIN xcart_variants xv ON xp.productid = xv.productid
INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid
INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid
INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR'
LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid
-- Keep the above

-- but change this one to add a group by.
LEFT JOIN (
SELECT xco2.optionId as optionid, count(*) as dCount
-- this could be cleaned up if the products and class aren't
-- really relevant- as it is, it serves only to narrow the set
-- of xcart_class_options you'll be looking at/counting for.
FROM xcart_products xp2
INNER JOIN xcart_classes xc2 ON xp2.productid = xc2.productid AND xc2.class = 'Color'
INNER JOIN xcart_class_options xco2 ON xc2.classid = xco2.classid

-- bring in the image_ds that match the class_option we are looking
-- at
LEFT JOIN xcart_images_D xiD ON xiD.optionid = xco2.optionid

-- And group by those option_ids.
group by xid.optionid
) as t1 ON xco.optionid = t1.optionid
-- we join the inner select based on the optionid we were looking at.

GROUP BY xco.optionid
ORDER by xp.product DESC

为了加快速度,这可能会起作用,具体取决于您正在寻找的结果:

SELECT xp.productid, xp.product, xc.classid, xco.optionid, xco.option_name, xiW.id, xiW.image_path, t1.dCount
FROM xcart_products xp
INNER JOIN xcart_variants xv ON xp.productid = xv.productid
INNER JOIN xcart_variant_items xvi ON xv.variantid = xvi.variantid
INNER JOIN xcart_class_options xco ON xvi.optionid = xco.optionid
INNER JOIN xcart_classes xc ON xco.classid = xc.classid AND xc.class = 'COLOR'
LEFT JOIN xcart_images_W xiW ON xiW.id = xvi.variantid

-- since the optionId is on the thing you're counting,
-- don't bother with any joins in the nested query and just
-- count and group.
LEFT JOIN (
SELECT xiD.optionId as optionid, count(*) as dCount
FROM xcart_images_D xiD
GROUP BY xiD.optionId
) as t1 ON xco.optionid = t1.optionid
-- still join the inner select based on the optionid

GROUP BY xco.optionid
ORDER by xp.product DESC

关于mysql - 如何添加计数和清理/简化涉及 4 个 INNER JOIN 和 2 个 LEFT JOIN 的派生查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10555860/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com