代码之家 › 专栏 › 技术社区 › Jonathan Komar

如何从两个表中查询相邻的分层数据,其中一行是类别,其余是该类别的内容?

hierarchy database sql mysql

Jonathan Komar · 技术社区 · 13 年前

免责声明 :我是一名语言学家,而不是一名计算机科学家。我熟悉编程,但我不是专家。这个项目是为一种濒危语言的大型词典而设计的。我需要对大约1000个词进行分类,这样我就可以直观地看到哪些词缺失了,哪些词可能会被添加。

我在MySQL中有两个表。

词汇表
类别表

词典表中的每个记录(行)都有一个父id值( pid控制 )与类别表的id相对应(我已重命名该表 文件夹 ).

我根据邻接模型设计了这个(我需要灵活性来轻松地更改树)。

类别

| folder |     name    |Â pfolder |
+--------+-------------+---------+
| 1      | Animals     | NULL    |
| 2      | Wild        | 1       |
| 3      | Domestic    | 1       |

词典

| id     | pid   | word        | translation |
+--------+-------+-------------+-------------+
| 1      | 3     | Hund        | dog         |
| 2      | 2     | Rentier     | reindeer    |
| 3      |       | Maus        | Mouse       |

目标

| main    | main_content  |  sub1     | sub1_content | sub2      | sub2_content |
+---------+---------------+-----------+--------------+-----------+--------------+
| Animals | NULL          | Domestic  | Hund         | NULL      | NULL         |
| Animals | NULL          | Wild      | Rentier      | NULL      | NULL         |
| Animals | Maus          | NULL      | NULL         | NULL      | NULL         |

示例查询

我不知道该怎么做,也不能简单地跟着做 this tutorial 因为我有多张桌子。

这个简单的查询不应该工作吗?

SELECT main.name  AS main
FROM categories AS main
    LEFT JOIN hd FROM lexicon ON lexicon.pid = categories.main

最终

最后,我想列出一份很好的有组织的词汇表。在这里,我用粗体表示文件夹,用斜体表示列表项。

动物
- 自然生长的
  - 出租人
- 本国的
  - Hund公司
- 毛斯

这些数据将使用xelatex打印。为了简单起见,我尽量减少上面的示例表。对词典表的实际查询将包括 lexeme , orthographic word 和 phonetic word (在国际音标中)。

1 回复 | 直到 13 年前

Nenad Zivkovic 13 年前

这有点复杂,但我认为它能满足你的需要。有一个变化很难,如果你想在Animals下显示,你需要在Lexicon的Maus行中设置pid=1。

SELECT
   COALESCE(c3.name, c2.name, c1.name) as main
  ,CASE WHEN (c3.name is null and c2.name is null) then lex.word end as main_content
  ,CASE WHEN (c3.name is null and c2.name is not null) then c1.name 
        WHEN (c3.name is not null and c2.name is not null) then c2.name end as sub1
  ,CASE WHEN (c3.name is null and c2.name is not null) then lex.word end as sub1_content
  ,CASE WHEN (c3.name is not null and c2.name is not null) then c1.name end as sub2
  ,CASE WHEN (c3.name is not null and c2.name is not null) then lex.word end as sub2_content
FROM
  Lexicon lex
LEFT JOIN 
  Categories c1 ON lex.pid = c1.folder
LEFT JOIN
  Categories c2 on c1.pfolder = c2.folder
LEFT JOIN
  Categories c3 on c2.pfolder = c3.folder

SQL Fiddle Demo -我还增加了一个子类别(Wild下的WildCats)来测试SUB2是否正确?