时间戳

首页 > 解决方案 > SQL：如何根据公共/私有类别从计数中排除行？

sql - SQL：如何根据公共/私有类别从计数中排除行？

问题描述

我有两个表（产品和类别）和一个多对多链接表（products_categories）。我要构建的查询应该只返回属于 5 个或更多公共类别的产品。私有类别在“类别”表的“公共”列中有一个“0”，公共类别有一个“1”。

我找不到忽略计数中的私人类别的方法。从我的测试数据来看，只有 Shovel 和 Lighter 才能脱颖而出。目前我得到了摩托车、铲子、篮球、足球、网球、镐和打火机，因为它们属于 5 个或更多类别（公共和私人）。

表格：

CREATE TABLE products(
  id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(25),
  price INT,
  created_at DATE,
  PRIMARY KEY(id)
);

CREATE TABLE categories(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(25),
  public BIT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE products_categories(
  product_id INT,
  category_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

查询：

SELECT products.id, products.name, COUNT(categories.id)
FROM products
INNER JOIN products_categories ON products.id = products_categories.product_id
INNER JOIN categories ON categories.id = products_categories.category_id
GROUP BY products.id
HAVING COUNT(categories.id) >= 5

测试数据：


INSERT INTO categories VALUES
(1, 'Small', b'1'),
(2, 'Medium', b'1'),
(3, 'Large', b'1'),
(4, 'One-size', b'1'),
(5, 'Tool', b'1'),
(6, 'Sport', b'1'),
(7, 'Ball', b'1'),
(8, 'Camping', b'1'),
(9, 'Food', b'1'),
(10, 'Non-food', b'1'),
(11, 'High-return', b'0'),
(12, 'Low-return', b'0'),
(13, 'Dangerous', b'0');

INSERT INTO products VALUES
(1, 'Bicycle', 50, '2021-03-02'),
(2, 'Motorbike', 100, '2021-03-02'),
(3, 'Shovel', 10, '2021-03-02'),
(4, 'Skis', 20, '2021-03-02'),
(5, 'Tent-S', 20, '2021-03-02'),
(6, 'Tent-M', 30, '2021-03-02'),
(7, 'Tent-L', 30, '2021-03-02'),
(8, 'Basketball', 5, '2021-03-02'),
(9, 'Football', 5, '2021-03-02'),
(10, 'Tennisball', 2, '2021-03-02'),
(11, 'Pickaxe', 15, '2021-03-02'),
(12, 'Lighter', 1, '2021-03-02'),
(13, 'Bottle-S', 2, '2021-03-02'),
(14, 'Bottle-M', 3, '2021-03-02'),
(15, 'Bottle-L', 4, '2021-03-02');

INSERT INTO products_categories VALUES
(1, 4),
(1, 6),
(1, 10),
(1, 11),
(2, 4),
(2, 6),
(2, 10),
(2, 11),
(2, 13),
(3, 4),
(3, 5),
(3, 8),
(3, 10),
(3, 12),
(3, 13),
(4, 4),
(4, 6),
(4, 10),
(4, 11),
(5, 1),
(5, 8),
(5, 10),
(5, 12),
(6, 2),
(6, 8),
(6, 10),
(6, 12),
(7, 3),
(7, 8),
(7, 10),
(7, 12),
(8, 4),
(8, 6),
(8, 7),
(8, 10),
(8, 12),
(9, 4),
(9, 6),
(9, 7),
(9, 10),
(9, 12),
(10, 4),
(10, 6),
(10, 7),
(10, 10),
(10, 12),
(11, 4),
(11, 5),
(11, 10),
(11, 12),
(11, 13),
(12, 4),
(12, 5),
(12, 8),
(12, 10),
(12, 11),
(12, 13),
(13, 1),
(13, 8),
(13, 9),
(13, 12),
(14, 2),
(14, 8),
(14, 9),
(14, 12),
(15, 3),
(15, 8),
(15, 9),
(15, 12);

标签： sql

解决方案

你似乎想要：

SELECT p.id, p.name, COUNT(*)
FROM products p JOIN
     products_categories pc
     ON p.id = pc.product_id JOIN
     categories c
     ON c.id = pc.category_id
WHERE c.public = 1
GROUP BY p.id, p.name
HAVING COUNT(*) >= 5

推荐阅读