分面搜索属性计数 [英] Faceted Search Attribute Count

问题描述

我想对以下小提琴进行属性计数查询. 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:

还要如何处理即将来临的2次相同功能，例如，我只 搜索黑色和红色，别无其他.目前未显示任何结果. 但它应该同时显示两个结果.但是如果查询像宝马黑色和 红色，则应该仅显示宝马黑色或红色，否则显示0 找到

Also how to handle it for same feature coming 2 times, like, i only search black and red nothing else. It is currently showing no results. but it should show both results. but if query is like bmw black and red then it should show only bmw black or red, otherwise 0 if not found

很容易.自然，不必在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

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