php+sql，正确的tre与父和子类别和项目

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

10个月前关门了。
改进这个问题
我试图显示正确的数据树，但我是，股票，所以我希望有人能给我指出正确的方向。
我已经尝试了php代码和纯sql代码，让我首先展示一下我迄今为止的尝试。

public function supported($ids) {

        $arr = json_decode($ids, true);

        $STH = $this->dbh->query("SELECT cid,device FROM supported_devices WHERE id IN (".implode(',',$arr).")");

        $a = $STH->fetchAll();

        if(count($a)) {

            foreach($a as $b) {
                $newArr[] = $b['cid'];
            }

            $STH = $this->dbh->query("SELECT cid,cat_name FROM supported_devices_cats WHERE id IN (".implode(',',$newArr).")");

            $c = $STH->fetchAll();

            foreach($c as $d) {
                $newArr2[] = $d['cid'];
            }

            $STH = $this->dbh->query("SELECT cat_name FROM supported_devices_cats WHERE id IN (".implode(',',$newArr2).")");

            $e = $STH->fetchAll();

            foreach($e as $parent) {
                $return[] = $parent['cat_name'];
                foreach($c as $child) {
                    $return[] = $child['cat_name'];
                    foreach($a as $device) {
                        $return[] = $device['device'];
                    }
                }
            }

            return $return;

        } else {
            return array();
        }
    }

SELECT DISTINCT c.*
FROM supported_devices_cats c
    LEFT JOIN supported_devices_cats pc
    ON c.id = pc.cid
WHERE
    c.cid IS NOT NULL 
    OR (c.cid IS NULL
       AND pc.cid IS NULL)

我试图使这样的父类别显示在第一，然后所有的子类别后，正确的设备是在每个子类别。我不知道我还能怎么解释，我加了一张画，也许这能更好地解释我在做什么。
更新，我需要升级我的mysql服务器到一个更高的版本。

我不太清楚为什么在表中需要“cid”列。只需输入外键的id就足够了。
话虽如此，以下是我用作数据库模式和测试内容的内容：

CREATE DATABASE cats_devs;

USE cats_devs

CREATE TABLE supported_devices_cats(
    id INT PRIMARY KEY,
    cid INT NULL DEFAULT NULL,
    FOREIGN KEY(cid) REFERENCES supported_devices_cats(id),
    cat_name VARCHAR(64) NOT NULL,
    dt DATETIME NOT NULL DEFAULT NOW()
);

CREATE TABLE supported_devices(
    id INT,
    FOREIGN KEY(id) REFERENCES supported_devices_cats(id),
    device VARCHAR(128) NOT NULL,
    dt DATETIME NOT NULL DEFAULT NOW()
);

INSERT INTO supported_devices_cats(id, cid, cat_name) VALUES
(1, NULL, "Disk Drives"), (2, NULL, "Monitors"),
(3, 1, "SSD"), (4, 1, "HDD"), 
(5, 2, "TN"), (6, 2, "IPS");

INSERT INTO supported_devices(id, device) VALUES
(4, "Seagate 2TB"), (4, "WD 2TB"),
(3, "Corsair 256GB"), (3, "WD 256GB"),
(5, "ASUS 27in"), (5, "ASUS 21in"),
(6, "Viewsonic 27in"), (6, "LG 24in");

我接下来要做的是一个cte递归查询（在较新的mysql/mariadb服务器中提供—对于较旧的版本，需要一个具有self-join的等效查询）：

WITH RECURSIVE hier_query(id, cat_id, cat_name, dev_name, dt) AS(
    SELECT id, cid, cat_name, CAST(NULL AS VARCHAR(128)), dt
    FROM supported_devices_cats
    WHERE cid IS NULL
    UNION ALL
    SELECT supported_devices_cats.id, supported_devices_cats.cid, 
           supported_devices_cats.cat_name, supported_devices.device, supported_devices_cats.dt
    FROM supported_devices_cats
    JOIN hier_query
         ON hier_query.id = supported_devices_cats.cid
    LEFT JOIN supported_devices ON supported_devices.id = supported_devices_cats.id
)
SELECT id, cat_id, cat_name, dev_name, dt
FROM hier_query;

这将产生包含所有必需信息的以下结果：

+------+--------+-------------+----------------+---------------------+
| id   | cat_id | cat_name    | dev_name       | dt                  |
+------+--------+-------------+----------------+---------------------+
|    1 |   NULL | Disk Drives | NULL           | 2020-06-25 07:52:39 |
|    2 |   NULL | Monitors    | NULL           | 2020-06-25 07:52:39 |
|    3 |      1 | SSD         | Corsair 256GB  | 2020-06-25 07:52:39 |
|    3 |      1 | SSD         | WD 256GB       | 2020-06-25 07:52:39 |
|    4 |      1 | HDD         | Seagate 2TB    | 2020-06-25 07:52:39 |
|    4 |      1 | HDD         | WD 2TB         | 2020-06-25 07:52:39 |
|    5 |      2 | TN          | ASUS 27in      | 2020-06-25 07:52:39 |
|    5 |      2 | TN          | ASUS 21in      | 2020-06-25 07:52:39 |
|    6 |      2 | IPS         | Viewsonic 27in | 2020-06-25 07:52:39 |
|    6 |      2 | IPS         | LG 24in        | 2020-06-25 07:52:39 |
+------+--------+-------------+----------------+---------------------+

现在在这个结果表中，当dev\u name为null时，它是一个父类别。您可以通过这种方式从中获取所有父类别。然后，对于每个父类别，当您将id与cat\ U id进行比较时，可以找到子类别。最后，您可以轻松获得所有设备。
老实说，我试图使它成为一个“全sql”解决方案，但我很难按照您想要的方式对表进行排序。我打赌其他人能做得更好。
p、这里是一个快速肮脏的黑客排序问题。我用“->”分隔符将主类别放在子名称前面。这样你以后就可以很容易地把它们分开。

WITH RECURSIVE hier_query(id, cat_id, cat_name, dev_name, dt) AS(
    SELECT id, cid, cat_name, CAST(NULL AS VARCHAR(128)), dt
    FROM supported_devices_cats
    WHERE cid IS NULL
    UNION ALL
    SELECT supported_devices_cats.id, supported_devices_cats.cid, 
           CONCAT(hier_query.cat_name, "->", supported_devices_cats.cat_name), 
           supported_devices.device, supported_devices_cats.dt
    FROM supported_devices_cats
    JOIN hier_query
         ON hier_query.id = supported_devices_cats.cid
    LEFT JOIN supported_devices ON supported_devices.id = supported_devices_cats.id
)
SELECT id, cat_id, cat_name, dev_name, dt
FROM hier_query
ORDER BY cat_name;

生产：

+------+--------+------------------+----------------+---------------------+
| id   | cat_id | cat_name         | dev_name       | dt                  |
+------+--------+------------------+----------------+---------------------+
|    1 |   NULL | Disk Drives      | NULL           | 2020-06-25 07:52:39 |
|    4 |      1 | Disk Drives->HDD | Seagate 2TB    | 2020-06-25 07:52:39 |
|    4 |      1 | Disk Drives->HDD | WD 2TB         | 2020-06-25 07:52:39 |
|    3 |      1 | Disk Drives->SSD | Corsair 256GB  | 2020-06-25 07:52:39 |
|    3 |      1 | Disk Drives->SSD | WD 256GB       | 2020-06-25 07:52:39 |
|    2 |   NULL | Monitors         | NULL           | 2020-06-25 07:52:39 |
|    6 |      2 | Monitors->IPS    | Viewsonic 27in | 2020-06-25 07:52:39 |
|    6 |      2 | Monitors->IPS    | LG 24in        | 2020-06-25 07:52:39 |
|    5 |      2 | Monitors->TN     | ASUS 27in      | 2020-06-25 07:52:39 |
|    5 |      2 | Monitors->TN     | ASUS 21in      | 2020-06-25 07:52:39 |
+------+--------+------------------+----------------+---------------------+

您甚至可以添加“where cat\u id is not null”条件来删除这两个无用的行。所有其他行都包含您需要的内容。

php+sql，正确的tre与父和子类别和项目

1条答案

相关问题

热门标签

最新问答