SQL：分别匹配标签的项目数

4 回答

• 0

  动态构造（在您的应用程序中）以下查询

  SELECT size, COUNT(*)
      FROM tbl
      WHERE color IN ('red', 'blue')
      GROUP BY size;
  

  这将给你有多少小，中，大的东西，因为你只看到阅读或蓝色的东西 .

  每次用户更改选项时，都会重新生成并重新运行查询 . 假设用户选择了一些类型和卖家，而不是制造商 . 然后 WHERE 子句看起来像

  WHERE type IN (...)
        AND manufacturer IN (...)
  

  也就是说，您需要准备好根据用户目前所选择的内容构建 AND 列表 .

  这是应用程序代码;在SQL中完成所有工作是不切实际的 .

  回复于 2024-05-08T12:49:37+08:00
• 0

  试试这个：

  --for colors category
  select l.title as label_title
  , count(product_id) as number_of
  from label_product lp
  inner join labels l on lp.label_id = l.id
  --where label_category_id = 'id_of_colors_category' --if you can use label_category_id directly, or if you must use title of category:
  where l.label_category_id in(select id from label_categories where title = 'colors')
  and exists(
    select 1 from products p
    where p.id = pl.product_id 
    --global rules for products, for example:
    ----and p.deleted = 0
    ----and p.category = 'cars'
    --rules for user selected conditions
    ----for example rule for sizes
    and exists(
      select 1 from label_product clp
      --where label_id in (....) --if you can use label_id's directly
      where clp.product_id = p.id
        and label_id in(
        select id from labels 
        where label_category_id = 'id_of_sizes_category'
        and title in('medium', 'large', 'small')
        )
      )
    ----example 2nd rule for types
    and exists(
      select 1 from label_product clp
      where clp.product_id = p.id
        and label_id in(
        select id from labels 
        where label_category_id = 'id_of_types_category'
        and title in('type1', 'type2', 'type3')
        )
      )
    --don't add rule for current label_category - for this example colors
    --end of user selected conditions
    )
  group by l.title
  

  回复于 2024-05-08T12:49:37+08:00
• 0

  这是用MySQL标记的，所以这是MySQL的一种方式 . 假设1和2是颜色RED和BLUE的ID，并且尚未选择尺寸过滤器 . 然后，第一个查询为您提供每个颜色标签，其中有多少产品与尺寸过滤器匹配 . 同样，最后一个查询为每个尺寸标签提供了与滤色器匹配的产品数量 .

  SET @selected_color_labels = '1,2';
  
  SET @selected_size_labels = NULL;
  
  SELECT l.id, count(DISTINCT lp.product_id)
  FROM label_categories lc
  INNER JOIN labels l ON l.label_category_id = lc.id 
  INNER JOIN label_product lp ON lp.label_id = l.id
  INNER JOIN label_categories lc2 ON lc2.title = 'size'
  INNER JOIN labels l2 ON l2.label_category_id = lc2.id AND ifNull(find_in_set(l2.id, @selected_size_labels) > 0, TRUE)
  INNER JOIN label_product lp2 ON lp2.label_id = l2.id AND lp2.product_id = lp.product_id
  WHERE lc.title = 'color'
  GROUP BY l.id;
  
  SELECT l.id, count(DISTINCT lp.product_id)
  FROM label_categories lc
  INNER JOIN labels l ON l.label_category_id = lc.id 
  INNER JOIN label_product lp ON lp.label_id = l.id
  INNER JOIN label_categories lc2 ON lc2.title = 'color'
  INNER JOIN labels l2 ON l2.label_category_id = lc2.id AND ifNull(find_in_set(l2.id, @selected_color_labels) > 0, TRUE)
  INNER JOIN label_product lp2 ON lp2.label_id = l2.id AND lp2.product_id = lp.product_id
  WHERE lc.title = 'size'
  GROUP BY l.id;
  

  回复于 2024-05-08T12:49:37+08:00
• 0

  首先，您似乎需要按类别和标签进行分组 .

  如果要使用相同的查询列出所有产品并过滤它们，可以检查参数的值，如果为null，则可以显示所有产品 .

  Select lc.title, l.title,  count(Distinct  p.Id) itemCount  from Products p, Label_Product lp, Labels l, Label_categories lc
  where
  p.id = lp.product_id and l.id = lp.label_id and lc.id = l.label_category_id
  and (l.ID = @label or @label  is null)
  and (lc.ID = @category or @category  is null)
  group by 
  lc.title, l.title, l.Id
  

  回复于 2024-05-08T12:49:37+08:00