>我有一个数据库,采用邻接列表方法来处理产品的分类,其中一种产品可以在许多类别下找到。查看以下数据库布局:
cats
id parent title desc
1 0 top top level
2 1 Electronics
3 2 Gaming
4 2 Computers
5 4 Tablets
6 1 Food
7 3 Xbox
products
id title qty
1 ToshibaTV 5
2 I-PAD2 9
3 Laser Pen 24
4 Asus Notebook 5
cats_products
id product_id cat_id
1 2 3
2 2 5
3 1 2
4 3 2
5 4 4
在上面的示例中,我需要一个 SQL 查询,该查询能够检索在"电子产品"类别中找到的所有产品以及具有任何级别的任何子类别(例如,它不是电子的直接子类别),而无需重复在多个类别中找到的产品,如 I-PAD2。
我可以在 PHP 应用程序的帮助下做到这一点,但我想知道是否可以在 MySQL 中仅使用纯 sql 来做到这一点?
使用邻接列表模型,您尝试执行的操作相当困难。 正如@Mike所建议的,使用嵌套集模型将使这变得容易得多。 或者通过您的 php 代码来做到这一点甚至会更容易。
但是,假设您知道可以有多少个父子级别(或者您可以假设不会超过 X),您可以尝试这样的事情。 如果MySQL支持CTE,这将更容易阅读,但不幸的是,它没有。 在这个例子中,我已经深入了4个层次 - 你可以得到更深入的想法。
SELECT p.Id, p.Title, p.Qty
FROM Products p
JOIN Cats_Products cp on p.id = cp.product_id
WHERE cp.cat_id IN (
SELECT c.id
FROM Cats c
WHERE c.title = 'Electronics'
UNION ALL
SELECT c2.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
WHERE c.title = 'Electronics'
UNION ALL
SELECT c3.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
LEFT JOIN Cats c3 ON c2.id = c3.parent
WHERE c.title = 'Electronics'
UNION ALL
SELECT c4.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
LEFT JOIN Cats c3 ON c2.id = c3.parent
LEFT JOIN Cats c4 ON c3.id = c4.parent
WHERE c.title = 'Electronics'
)
GROUP BY p.Id, p.Title, p.Qty
这是SQL小提琴。
祝你好运。
这是你问题的第一部分。你能更详细地解释一下孩子部分吗?我想这与猫的父母有关。
试试这个:
SELECT distinct(p.title)
FROM cats AS c
JOIN cats_products AS cp
ON cp.cat_id = c.id AND c.title = "Electronics"
JOIN products AS p
ON p.id = cp.product_id;
示例数据:
CREATE TABLE cats
(`id` int, `parent` int, `title` varchar(11), `desc` varchar(9))
;
INSERT INTO cats
(`id`, `parent`, `title`, `desc`)
VALUES
(1, 0, 'top', 'top level'),
(2, 1, 'Electronics', ''),
(3, 2, 'Gaming', ''),
(4, 2, 'Computers', ''),
(5, 4, 'Tablets', ''),
(6, 1, 'Food', ''),
(7, 3, 'Xbox', '')
;
CREATE TABLE products
(`id` int, `title` varchar(13), `qty` int)
;
INSERT INTO products
(`id`, `title`, `qty`)
VALUES
(1, 'ToshibaTV', 5),
(2, 'I-PAD2', 9),
(3, 'Laser Pen', 24),
(4, 'Asus Notebook', 5)
;
CREATE TABLE cats_products
(`id` int, `product_id` int, `cat_id` int)
;
INSERT INTO cats_products
(`id`, `product_id`, `cat_id`)
VALUES
(1, 2, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 2),
(5, 4, 4)
;
SQL 小提琴演示
编辑第二部分:
SELECT distinct(v.title) FROM products AS v
JOIN (
SELECT Y.product_id FROM cats_products AS Y
JOIN(
SELECT distinct(z.id) FROM cats AS Z
JOIN
(SELECT c.parent
FROM cats AS c
JOIN cats_products AS cp
ON cp.cat_id = c.id
AND c.title = "Electronics") AS X
ON x.parent = z.parent) AS W
ON W.id = Y.cat_id) AS u
ON u.product_id = v.id
;
示例数据
CREATE TABLE cats
(`id` int, `parent` int, `title` varchar(11), `desc` varchar(9))
;
INSERT INTO cats
(`id`, `parent`, `title`, `desc`)
VALUES
(1, 0, 'top', 'top level'),
(2, 1, 'Electronics', ''),
(3, 2, 'Gaming', ''),
(4, 2, 'Computers', ''),
(5, 4, 'Tablets', ''),
(6, 1, 'Food', ''),
(7, 3, 'Xbox', '')
;
CREATE TABLE products
(`id` int, `title` varchar(13), `qty` int)
;
INSERT INTO products
(`id`, `title`, `qty`)
VALUES
(1, 'ToshibaTV', 5),
(2, 'I-PAD2', 9),
(3, 'Laser Pen', 24),
(4, 'Asus Notebook', 5),
(5, 'French Fries', 50)
;
CREATE TABLE cats_products
(`id` int, `product_id` int, `cat_id` int)
;
INSERT INTO cats_products
(`id`, `product_id`, `cat_id`)
VALUES
(1, 2, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 2),
(5, 4, 4),
(5, 5, 6)
;
这是我对您的问题的解释:每个类别都有一个父类别,您希望选择具有相同父级的所有其他类别。更具体地说,具有相同父类别的所有产品。SQL 小提琴演示