需要 MySQL 查询建议 [英] MySQL query advice required

问题描述

我有下表代表产品及其属性.对于 First Choice Material 为 Gold 的产品，我需要编写一个查询，该查询将获取名为 Insert Style 的属性的 value.

我做到了:

SELECT *FROM shop_product_propertiesWHERE name = '插入尺寸'AND product_id IN (SELECT DISTINCT product_idFROM shop_product_propertiesWHERE ((LOWER(name) = '首选材料'AND value = 'Aluminium') AND (LOWER(name) = 'insert style' AND value = 'CCGT')));

解决方案

我需要编写一个查询来获取名称为插入样式的所有值

所以开始:

SELECT *FROM shop_product_propertiesWHERE name = '插入样式';

<块引用>

其中首选材料 = Gold 并且它们具有相同的 product_id

然后引入一个子查询:

SELECT *FROM shop_product_propertiesWHERE name = '插入样式'AND product_id IN (SELECT DISTINCT product_idFROM shop_product_propertiesWHERE name = '首选材料'AND 值 = '黄金');

http://sqlfiddle.com/#!2/ecfb4/1

I have the following table representing products and their attributes/properties. I need to write a query that will get the value of the property named Insert Style for products where the First Choice Material is Gold.

I got this far:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Size'
AND product_id IN (
    SELECT DISTINCT product_id
    FROM shop_product_properties
    WHERE ((LOWER(name) = 'first choice material'
    AND value = 'Aluminium') AND (LOWER(name) = 'insert style' AND value = 'CCGT'))
);

解决方案

I need to write a query that will get all value with name Insert Style

So start with that:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Style';

where First Choice Material = Gold AND they have the same product_id

Then introduce a subquery:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Style'
AND product_id IN (
    SELECT DISTINCT product_id
    FROM shop_product_properties
    WHERE name = 'First Choice Material'
    AND value = 'Gold'
);

http://sqlfiddle.com/#!2/ecfb4/1

这篇关于需要 MySQL 查询建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！