时间戳

首页 > 解决方案 > MYSQL查询不同价格范围内的产品，没有重复

mysql - MYSQL查询不同价格范围内的产品，没有重复

问题描述

我正在尝试查询我的产品表，以便我的查询返回某个变体价格范围内的产品集——产品有许多变体，变体属于产品。我有以下查询正在运行，但速度很慢——查询需要 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语句中，我尝试price在 SELECT 语句中检查子查询创建的值，但 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 子句'中的未知列'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 以允许我查询某个变体价格范围内的产品，而不会返回重复的产品并且不会严重影响性能？

标签： mysqlsemi-join

解决方案

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

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

推荐阅读