用于选择父名称和子名称的 SQL 查询

发布于 2024-11-03 22:01:05 字数 484 浏览 2 评论 0原文

我有一个具有以下结构的表 -

Category {Id, Name, ParentId}

我有这样的值 -

id  name               parentid
-------------------------------
1   Technology Focus   NULL
2   Tools              1
3   Database           1

如何编写显示如下的查询 -

name (parent)      name (child)             
--------------------------------
Technology Focus   Tools
Technology Focus   Database
etc..

我相信我需要使用 Group By 子句，但我不太明白。

原文

I have a table with the following structure -

Category {Id, Name, ParentId}

I have values like this -

id  name               parentid
-------------------------------
1   Technology Focus   NULL
2   Tools              1
3   Database           1

How do I write a query that displays like this -

name (parent)      name (child)             
--------------------------------
Technology Focus   Tools
Technology Focus   Database
etc..

I believe I need to use the Group By clause but I'm not quite getting it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦罢 2024-11-10 22:01:05

如果我没看错的话，我想你只需要

select parent.name, child.name 
from category child
  inner join category parent
    on parent.id = child.parentid

if i'm looking at that correctly, i think you just need

select parent.name, child.name 
from category child
  inner join category parent
    on parent.id = child.parentid

回复收藏 0 原文

素年丶 2024-11-10 22:01:05

您需要像这样将表本身连接起来：

select Cat.Name, Par.Name
from category as cat
inner join category par on cat.id = par.id

You need to join the table on itself like this:

select Cat.Name, Par.Name
from category as cat
inner join category par on cat.id = par.id

回复收藏 0 原文

清醇 2024-11-10 22:01:05

如果您尝试在 SQL 中实现树状结构，那么这有点错误。

您应该使用两个表来实现树：

CREATE TABLE Categories (
   category_id INT AUTO_INCREMENT,
   name VARCHAR(40),
   PRIMARY KEY (category_id)
);
CREATE TABLE Tree (
   ancestor INT NOT NULL,
   descendant INT NOT NULL,
   PRIMARY KEY(ancestor, descendant),
   FOREIGN KEY (ancestor) REFERENCES Categories(category_id),
   FOREIGN KEY (descendant) REFERENCES Categories(category_id)
);

这种结构（称为闭包表）更易于维护（预制更新、重新排列结构等）。

然后您选择如下数据：

SELECT 
   parent.name AS parent,
   item.name AS item
FROM Categories AS parent
LEFT JOIN Tree AS path ON parent.category_id = path.ancestor
LEFT JOIN Categories AS item ON item.category_id = path.descendant
WHERE parent.category_id = 1

无论如何，请阅读闭包表，你就会明白为什么..

If you are trying to implement at tree-like structure in SQL, then this, kinda, is the wrong way to do this.

You should use two tables to implement a tree:

CREATE TABLE Categories (
   category_id INT AUTO_INCREMENT,
   name VARCHAR(40),
   PRIMARY KEY (category_id)
);
CREATE TABLE Tree (
   ancestor INT NOT NULL,
   descendant INT NOT NULL,
   PRIMARY KEY(ancestor, descendant),
   FOREIGN KEY (ancestor) REFERENCES Categories(category_id),
   FOREIGN KEY (descendant) REFERENCES Categories(category_id)
);

This structure (know as Closure Table )is easier to maintain (preform updates, rearrange structure , etc. ).

Then you select the data like:

SELECT 
   parent.name AS parent,
   item.name AS item
FROM Categories AS parent
LEFT JOIN Tree AS path ON parent.category_id = path.ancestor
LEFT JOIN Categories AS item ON item.category_id = path.descendant
WHERE parent.category_id = 1

Anyway , read about Closure Tables, you will understand why ..

回复收藏 0 原文

~没有更多了~