MYSQL查询变量价格范围内的产品，不重复

Question

我正在尝试查询我的产品表，以便我的查询返回某个变体价格范围内的产品集——产品有许多变体，变体属于产品。我有下面的查询，它正在工作，但速度很慢——查询需要1.66秒。

SELECT colortags.tag, products.*, (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id LIMIT 1) price, collects.position 
FROM products
INNER JOIN wp_wps_collects collects 
ON products.product_id = collects.product_id 
AND collects.collection_id = 123456788
INNER JOIN wp_wps_tags colortags 
ON colortags.product_id = collects.product_id 
AND (colortags.tag = 'black' OR colortags.tag = 'nav') 
INNER JOIN wp_wps_tags styletags 
ON styletags.product_id = collects.product_id 
AND (styletags.tag = 'purses')
WHERE (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id AND variants.price >= 200 AND variants.price < 300 LIMIT 1) > 0
AND (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id AND variants.price >= 200 AND variants.price < 300 LIMIT 1) <> 0

在WHERE语句中，我只尝试在SELECT语句中检查子查询创建的price值，但MYSQL告诉我price未定义。这是SQL:

SELECT colortags.tag, products.*, (SELECT variants.price FROM wp_wps_variants variants WHERE variants.product_id = products.product_id LIMIT 1) price, collects.position 
FROM wp_wps_products products
INNER JOIN wp_wps_collects collects 
ON products.product_id = collects.product_id 
AND collects.collection_id = 98515058801
INNER JOIN wp_wps_tags colortags 
ON colortags.product_id = collects.product_id 
AND (colortags.tag = 'color:generic-black' OR colortags.tag = 'color:navy') 
INNER JOIN wp_wps_tags styletags 
ON styletags.product_id = collects.product_id 
AND (styletags.tag = 'style:totes')
WHERE price >= 200 
AND price < 300

这是我从上面的SQL:中得到的错误消息

"where clause"中的未知列"price">

我还尝试在variants表上使用一系列联接，但随后返回了重复的产品。这是SQL

SELECT colortags.tag, products.*, (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id LIMIT 1) price, collects.position 
FROM products
INNER JOIN collects 
ON products.product_id = collects.product_id 
AND collects.collection_id = 1234566
INNER JOIN colortags 
ON colortags.product_id = collects.product_id 
AND (colortags.tag = 'black' OR colortags.tag = 'navy') 
INNER JOIN styletags 
ON styletags.product_id = collects.product_id 
AND (styletags.tag = 'purses')
INNER JOIN variants
ON variants.product_id = products.product_id
AND variants.price >= 200 
AND variants.price < 300
AND variants.price > 0
AND variants.price <> 0

我想要的是从第一条SQL语句中获得的查询结果，而不影响性能。我想我可以在变体表上用INNER JOIN编写SQL，以过滤出价格不正确的变体，而不会得到重复的产品，但我无法确定如何做到这一点。

有人知道我如何在这里编写SQL，使我能够查询特定变体价格范围内的产品，而不会返回重复的产品，也不会严重影响性能吗？

Answer 1

您正在执行半联接，这意味着您想要执行联接，但您只想要一行结果，即使有多个匹配项。

实现这一点的一种方法是联接到派生表，该派生表在子查询中使用GROUP BY将变量减少到每个product_id一行。

SELECT colortags.tag, products.*, prices.price, collects.position 
FROM products
INNER JOIN wp_wps_collects collects 
ON products.product_id = collects.product_id 
AND collects.collection_id = 123456788
INNER JOIN wp_wps_tags colortags 
ON colortags.product_id = collects.product_id 
AND (colortags.tag = 'black' OR colortags.tag = 'nav') 
INNER JOIN wp_wps_tags styletags 
ON styletags.product_id = collects.product_id 
AND (styletags.tag = 'purses')
INNER JOIN (
SELECT v.product_id, MIN(v.price) AS price FROM variants v
INNER JOIN wp_wps_collects c ON v.product_id = c.product_id
WHERE c.collection_id = 123456788
GROUP BY product_id ORDER BY NULL
) prices
ON products.product_id = prices.product_id

我想你会对每种产品的最低价格感兴趣。您也可以在派生表中使用MAX()。

我不得不将联接复制到wp_wps_collects，因此派生表将仅限于与您查询的集合相关的产品。否则，如果您有数百万个产品，那么派生表将变得非常大，从而产生不同的性能问题。

Answer 2

如果将查询封装在另一个查询中，则可以在where子句中使用price

SELECT * from (
SELECT colortags.tag, products.*, (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id LIMIT 1) price, collects.position 
FROM products
INNER JOIN wp_wps_collects collects 
ON products.product_id = collects.product_id 
AND collects.collection_id = 123456788
INNER JOIN wp_wps_tags colortags 
ON colortags.product_id = collects.product_id 
AND (colortags.tag = 'black' OR colortags.tag = 'nav') 
INNER JOIN wp_wps_tags styletags 
ON styletags.product_id = collects.product_id 
AND (styletags.tag = 'purses')
WHERE (SELECT variants.price FROM variants WHERE variants.product_id = products.product_id AND variants.price >= 200 AND variants.price < 300 LIMIT 1) > 0
) t 
WHERE t.price >= 200 and t.price < 300