php - MySQL，PHP - WHERE 子句

问题描述

我创建如下查询，但 WHERE 部分有问题：

$get_products = "SELECT P.*, C.`category_name`,
                 GROUP_CONCAT(`category_name` SEPARATOR ', ') 
                 AS cat 
                 FROM `products` P 
                 NATURAL JOIN `categories` C 
                 NATURAL JOIN `product_to_categories` 
                 WHERE FIND_IN_SET(`category_id`, '$answers')
                 GROUP BY `product_name` 
                 ORDER BY count(C.`category_id`) DESC";

所以如果我有产品，即： DB Fiddle

APPLE所属分类：FRUIT , GREEN , ROUND
WHEEL所属分类：MACHINES , BLACK , ROUND
BANANA所属分类：FRUIT , YELLOW , CUBOID
PHONE所属分类：ELECTRONICS , BLACK , CUBOID
TOMATO所属分类：ROUND , VEGETABLE , RED
PIZZA属于类别：ROUND , COLORFUL, DISH

并选择类别：ROUND , FRUIT , GREEN , MACHINES , VEGETABLE

感谢 FIND_IN_SET 我将收到：

    +--------------+---------------------+
    | product_name | cat                 |
    +--------------+---------------------+
    | APPLE        | FRUIT, GREEN, ROUND |
    +--------------+---------------------+
    | TOMATO       | VEGETABLE, ROUND    |
    +--------------+---------------------+
    | WHEEL        | MACHINES, ROUND     |
    +--------------+---------------------+
    | BANANA       | FRUIT               |
    +--------------+---------------------+
    | PIZZA        | ROUND               |
    +--------------+---------------------+

现在我正在尝试设置强制类别之一，所以它应该像这样工作：

我正在选择ROUND、FRUIT、GREEN、MACHINES、VEGETABLE类别的产品。

假设ROUND是主要（强制性）类别，我应该收到：

 +--------------+---------------------+
 | product_name | cat                 |
 +--------------+---------------------+
 | APPLE        | FRUIT, GREEN, ROUND |
 +--------------+---------------------+
 | TOMATO       | VEGETABLE, ROUND    |
 +--------------+---------------------+
 | WHEEL        | ROUND, MACHINES.    |
 +--------------+---------------------+

因此，从属于ROUND类别的所有产品中，我也在寻找FRUIT、GREEN、MACHINES、VEGETABLES。

我试过这个条款：

    WHERE (category_id IN ('$main_category')
          AND (FIND_IN_SET(category_id, '$answers')));

它几乎可以工作了。几乎是因为我输ORDER BY count了，它还返回属于ROUND但不属于任何其他类别的PIZZA 。

谢谢！

CREATE TABLE products (
  `product_id` INTEGER NOT NULL PRIMARY KEY,
  `product_name` VARCHAR(31)
);

INSERT INTO products
  (`product_id`, `product_name`)
VALUES
  ('1', 'APPLE'),
  ('2', 'WHEEL'),
  ('3', 'BANANA'),
  ('4', 'PHONE'),
  ('5', 'TOMATO'),
  ('6', 'PIZZA');

CREATE TABLE categories (
  `category_id` INTEGER,
  `category_name` VARCHAR(31)
);

INSERT INTO categories
  (`category_id`, `category_name`)
VALUES
  ('1', 'FRUIT'),
  ('2', 'GREEN'),
  ('3', 'ROUND'),
  ('4', 'MACHINES'),
  ('5', 'BLACK'),
  ('6', 'YELLOW'),
  ('7', 'CUBOID'),
  ('8', 'ELECTRONICS'),
  ('9', 'DISH'),
  ('10', 'VEGETABLE'),
  ('11', 'RED'),
  ('12', 'COLORFUL');



CREATE TABLE product_to_categories (
  `relation_ID` INTEGER,
  `product_ID` INTEGER,
  `category_ID` INTEGER
);

INSERT INTO product_to_categories
  (`relation_ID`, `product_ID`, `category_ID`)
VALUES
  ('1', '1', '1'),
  ('2', '1', '2'),
  ('3', '1', '3'),
  ('4', '2', '4'),
  ('5', '2', '5'),
  ('6', '2', '3'),
  ('7', '3', '1'),
  ('8', '3', '6'),
  ('9', '3', '7'),
  ('10', '4', '5'),
  ('11', '4', '8'),
  ('12', '5', '3'),
  ('13', '5', '10'),
  ('14', '5', '11'),
  ('15', '6', '3'),
  ('16', '6', '9'),
  ('17', '6', '12');

标签： phpmysql

SELECT p.* , GROUP_CONCAT(COALESCE(c2.category_name,c1.category_name)) name FROM products p JOIN product_to_categories pc1 ON pc1.product_id = p.product_id JOIN categories c1 ON c1.category_id = pc1.category_id JOIN product_to_categories pc2 ON pc2.product_id = pc1.product_id JOIN categories c2 ON c2.category_id = pc2.category_id WHERE c1.category_name = 'round' AND c2.category_name IN ('ROUND','FRUIT', 'GREEN', 'MACHINES', 'VEGETABLE') GROUP BY p.product_id HAVING COUNT(*) > 1 ;

php - MySQL，PHP - WHERE 子句

问题描述

解决方案

推荐阅读