跨多行的MySQL、SQL查询

Question

我有一个名为clothes_properties的表，其中一些行如下所示：

id  product_id  property_name   property
55  189000      color           blue
56  189000      type            tshirt
57  189000      size            medium
58  189001      color           red
59  189001      type            tshirt
60  189001      size            medium

我想创建一个SQL查询，它能够从用户搜索的内容中选择正确的product_id。因此，如果用户搜索T恤和medium，我希望SQL查询返回: 189000 & 189001。如果用户搜索T恤和blue，我只希望返回这个产品id : 189000。如何创建执行此操作的SQL查询？

Answer 1

这种表通常被称为键/值存储。这是处理可扩展属性列表的有效方法，但使用起来可能有点麻烦。

这样的查询将按照与您的条件列表匹配程度的顺序给出product_id值，最佳匹配在第一位。(http://sqlfiddle.com/#!9/7870b5/2/0)

select count(*) matches, product_id
  from prop
 where property in ('tshirt','medium')
 group by product_id
 order by 1 desc 

但是，该查询忽略了大小、颜色和类型之间的差异。

如果你想精确地匹配大小、颜色等，就会变得有点复杂。您需要从一个透视您的键/值属性表的查询开始--将行转换为列。(http://sqlfiddle.com/#!9/7870b5/3/0)

select id.product_id, 
       color.property color,
       type.property type,
       size.property size
 from (select distinct product_id from prop) id
 left join prop color on id.product_id = color.product_id and color.property_name = 'color'
 left join prop type on id.product_id = type.product_id and type.property_name = 'type'
 left join prop size on id.product_id = size.product_id and size.property_name = 'size'

然后，您需要将其视为一个虚拟表并对其进行查询，可能如下所示。(http://sqlfiddle.com/#!9/7870b5/4/0)

select *
  from (

        select id.product_id, 
               color.property color,
               type.property type,
               size.property size
         from (select distinct product_id from prop) id
         left join prop color on id.product_id = color.product_id and color.property_name = 'color'
         left join prop type on id.product_id = type.product_id and type.property_name = 'type'
         left join prop size on id.product_id = size.product_id and size.property_name = 'size'
         ) allprops
 where size='medium' and color = 'blue'

许多开发人员和dbas都会创建一个类似于allprops的视图来简化这一过程。

Answer 2

是否要完全忽略property_name列？

SELECT DISTINCT product_id
FROM   clothes_properties
WHERE  property IN ('tshirt', 'medium');

SELECT DISTINCT product_id
FROM   clothes_properties
WHERE  property IN ('tshirt', 'blue');

Answer 3

我将使用GROUP BY和HAVING来完成此操作

select product_id
from clothes_properties
where property_type = 'type' and property = 'tshirt' or
      property_type = 'size' and property = 'medium'
group by product_id
having count(distinct property_type) = 2;

注意:如果给定产品没有重复的属性类型，则不需要distinct。

如果你想要一些通用的东西，你可以使用：

select product_id
from clothes_properties
where property_type = 'type' and (property = $type or $type = '') or
      property_type = 'size' and (property = $size or $size = '') or
      property_type = 'color' and (property = $color or $color = '')
group by product_id
having count(distinct property_type) = (($type <> '') + ($size <> '') + ($color <> ''));