使用SQL和PHP从不同的引用表中获取层次树

debugcn 发表于 Dev

温泽尔

我有多个具有相似基本结构的表：

biopsy_p0
id | biopsy_id    | introduced

biopsy_p1
id | biopsy_p0_id | introduced

biopsy_p2
id | biopsy_p1_id | introduced

我的目标是获取依赖项之间的树状视图

biopsy_p0.id-> biopsy_p1.biopsy_p0_id-> biopsy_p2.biopsy_p1_id

我尝试仅使用SQL来执行此操作，但是从我的问题可以看出，我对此并不十分了解。到目前为止，我能找到的所有内容都是关于分层树的参考。但是那些总是只使用一个带有内部引用的表。

---更新：现在我可以在PHP上使用它了，这确实不是一个很好的解决方案，我希望我可以在SQL中使用它，这样扩展起来会更好一些：

PHP代码：

  $database = DatabaseFactory::getFactory()->getConnection();
      // Get all p0 element asociated with the biopsy
      $sql = "SELECT *
              FROM biopsy_p0
              WHERE biopsy_id = :id";
      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p0 = $query->fetchAll();

      // Get all p1 elements
      $sql="SELECT *
            FROM biopsy_p0 as p0
            RIGHT JOIN biopsy_p1 as p1
            ON p0.id=p1.biopsy_p0_id
            WHERE biopsy_id = :id;";

      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p1 = $query->fetchAll();

      for ($i=0; $i < count($p0); $i++)
      {
        $p1Array = new ArrayObject();
        foreach ($p1 as $key => $value)
        {
          if ($value->biopsy_p0_id == $p0[$i]->id)
          {
             $p1Array->append($value);
          }
          $p0[$i]->p1 = $p1Array;
        }
        unset($p1Array);
      }
      if ($p0 != NULL){
        return $p0;
      }
      return FALSE;

结果：这正是我所需要的，但是PHP太凌乱了，而且随着我想检查的每个子级别，它的复杂性都会增加。

SQL数据：

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL,
  `biopsy_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14'),
(5, 255, '2015-03-10'),
(6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
`id` int(11) unsigned NOT NULL,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`) VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

也许最好有一个层次结构表通过ID引用另一个存储实际数据的表...这样我就只涉及两个表，如果添加新元素，它会更加灵活...

伦纳特

让我们从声明密钥开始：

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL primary key,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL primary key,
`biopsy_id` int(11) unsigned NOT NULL,
`introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p0 add constraint fk_biopsy
    foreign key (biopsy_id)
    references biopsy (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14');

-- violates the f.k. introduced
-- (5, 255, '2015-03-10'),
-- (6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
  `id` int(11) unsigned NOT NULL primary key,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p1 add constraint fk_biopsy_p0
    foreign key (biopsy_p0_id)
    references biopsy_p0 (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`)     
VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

我建议您为它们的名称命名，即不要命名id列id并在模型中的其他地方更改名称。例子：

CREATE TABLE IF NOT EXISTS biopsy (
  biopsy_id int unsigned NOT NULL primary key,
  creation_date date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

但我会把它放在一边。现在我们知道数据是一致的：

select x.id as biopsy_id, x.creation_date
     , y.id as biopsy_p0_id, y.introduced as biopsy_p0_introduction
     , z.id as biopsy_p1_id, z.introduced as biopsy_p1_introduction 
from biopsy as x 
left join biopsy_p0 as y 
    on y.biopsy_id = x.id 
left join biopsy_p1 as z 
    on z.biopsy_p0_id = y.id 
order by x.id, y.id, z.id;

+-----------+---------------+--------------+------------------------+--------------+------------------------+
| biopsy_id | creation_date | biopsy_p0_id | biopsy_p0_introduction | biopsy_p1_id | biopsy_p1_introduction |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
|       226 | 2015-03-08    |            1 | 2014-12-31             |            1 | 2015-03-18             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            3 | 2015-03-17             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            4 | 2015-03-18             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            2 | 2015-03-31             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            6 | 2015-03-01             |
|       226 | 2015-03-08    |            3 | 2014-12-31             |            5 | 2015-03-11             |
|       227 | 2015-03-08    |            4 | 2015-03-14             |         NULL | NULL                   |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
7 rows in set (0.00 sec)

剩下的只是纯粹的演示，最好在php中完成。

关于您的一般性问题，最好将结构信息保留在一个表中，我想说的是，如果您的固定级别较少，那么解决方案就可以了。

对于大量级别或数量未知的情况，您需要某种递归结构（请注意，您还需要询问此类问题的方法，如今大多数DBMS：s都具有递归公用表表达式，而MySQL却没有。您可以使用变量来解决某些问题，但是很快就会变得混乱。Troels Arvin在以下位置有一组链接：

http://troels.arvin.dk/db/rdbms/links/#hierarchical

您可能会发现有用。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。