在查询中将两个表与类别合并，以检索类别及其父项

一尘不染

在查询中将两个表与类别合并，以检索类别及其父项

sql

我有一个PHP函数，可为我返回一个包含类别信息的数组。应该为它提供一个$ categoryId，然后返回该类别和“之上”的任何类别。这正在工作：

表结构tbl_categories：

CREATE TABLE `tbl_categories` (
  `categoryId` int(11) NOT NULL,
  `categoryParentId` int(11) NOT NULL,
  `categoryName` varchar(50) NOT NULL,
  `categoryDescr` varchar(400) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `tbl_categories` ADD PRIMARY KEY (`categoryId`);
ALTER TABLE `tbl_categories`
  MODIFY `categoryId` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

MySQL查询代码：

SELECT * FROM (
    SELECT
      @r AS _id,
      (SELECT @r := categoryParentId FROM tbl_categories WHERE categoryId = _id) AS categoryParentId,
      @l := @l + 1 AS categoryDepth
    FROM
      (SELECT @r := '.$categoryId.', @l := 0) vars,
      tbl_categories h
WHERE @r <> 0) T1
JOIN tbl_categories T2 ON T1._id = T2.categoryId
ORDER BY T1.categoryDepth DESC

但是现在，我有两个表，而不是一个表：

tbl_categories
tbl_categories_custom

它们的结构相同。将categoryId在tbl_categories_custom从1000开始，并不会“发生冲突”与那些在tbl_categories
中类别tbl_categories_custom可以有自己的categoryParentId一套的categoryId中tbl_categories。

现在，我的问题是，如何才能将上面与just一起使用的查询更改tbl_categories为也可以与一起使用tbl_categories_custom。我想我必须首先以某种方式联合这些表，但我不知道如何。

阅读 158

2021-05-16

共1个答案

一尘不染

首先，您的MySQL版本（MariaDB10.3）支持常见的表表达式，因此您有一种避免在查询中使用变异变量的方法。在支持公用表表达式之前，对查询中的变量进行突变已成为执行分层查询的一种方法，但该策略已被弃用，并且没有书面记录保证它可以始终按预期运行。

因此，这是使用公用表表达式（cte）进行相同操作的查询，其中8是示例值（用PHP表达式替换）：

with recursive 
cte as (
    select 1 as categoryDepth,
           c.* 
    from   tbl_categories c
    where  categoryId = 8
    union
    select cte.categoryDepth + 1, 
           c.*
    from   cte
    inner join tbl_categories c
            on c.categoryId = cte.categoryParentId
)
select   * 
from     cte
order by categoryDepth desc;

现在，当您拥有第二个表时，可以首先执行一个用于定义并集的公用表表达式，然后按上述步骤继续操作：

with recursive 
base as (
    select * from tbl_categories
    union
    select * from tbl_categories_custom
),
cte as (
    select 1 as categoryDepth,
           base.* 
    from   base
    where  categoryId = 8
    union
    select cte.categoryDepth + 1, 
           base.*
    from   cte
    inner join base
            on base.categoryId = cte.categoryParentId
)
select   *
from     cte
order by categoryDepth desc;

2021-05-16