分面搜索属性计数

问题描述

我想对以下小提琴进行属性计数查询. FIDDLE

I want to have an attribute count query for following fiddle.FIDDLE

它的工作正常，但是我无法使用它来计算属性.不知道如何实现，因为花了很多小时并更改查询后，它仍然对我不起作用.有人有建议吗?

Its working perfect but I'm failed to count the attributes using this. Don't know how it'll be achieved because after spending many hours and changing query still its not working for me.Anyone has suggestions??

推荐答案

这是您的方法:

SELECT meta_name, meta_value, COUNT(DISTINCT item_id) count
  FROM meta m JOIN item_meta im
    ON im.field_id = m.id
 GROUP BY meta_name, meta_value

输出:

|      META_NAME | META_VALUE | COUNT |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     1 |
|          Color |      Black |     1 |
|          Color |        Red |     1 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     1 |
|          Model |    2Series |     1 |
|          Model |    3Series |     1 |
|          Model |      Civic |     1 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

更新:

已过滤:

SELECT meta_name, meta_value, COUNT(DISTINCT item_id) count
  FROM meta m JOIN item_meta im
    ON im.field_id = m.id
 WHERE item_id IN
(
  SELECT i.id
  FROM item_meta im JOIN items i
    ON im.item_id = i.id JOIN meta m
    ON im.field_id = m.id
 GROUP BY i.id
HAVING MAX(meta_name = 'Make' AND meta_value = 'BMW') = 1
   AND MAX(meta_name = 'Car Type' AND meta_value = 'Coupe') = 1
)
 GROUP BY meta_name, meta_value;

输出:

|      META_NAME | META_VALUE | COUNT |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|          Color |      Black |     1 |
|          Color |      White |     1 |
| Interior Color |      Black |     1 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|          Model |    2Series |     1 |
|          Model |    3Series |     1 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

UPDATE2:

很容易.自然，不必在HAVING子句中使用AND(汽车不能同时为黑色和红色)，您必须像这样使用OR

It is easy. Naturally instead of using AND in HAVING clause (a car can't be black and red at the same time) you have to use OR like so

HAVING MAX(meta_name = 'Color' AND meta_value = 'Black') = 1
    OR MAX(meta_name = 'Color' AND meta_value = 'Red')   = 1

或者这是表达相同意图的另一种方式

or here is another way to express the same intent

HAVING MAX(meta_name = 'Color' AND meta_value = 'Black') +
       MAX(meta_name = 'Color' AND meta_value = 'Red') > 0

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于分面搜索属性计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！