mariadb 使用Map表的SQL自连接

5fjcxozz 于 2023-10-20 发布在其他

关注(0)|答案(2)|浏览(119)

下面定义了两个表。一个是我们的组的表，一个是将一个组Map为另一个组的子组的表。

table customers_groups:
    id              int auto_increment primary key
    group_shortname varchar(16)           not null

table subgroup_mappings:
    group_id    int not null,
    subgroup_id int not null,
    constraint group_id unique (group_id, subgroup_id),
    constraint customers_subgroups_table_ibfk_1 foreign key (group_id) references customers_groups (id),
    constraint customers_subgroups_table_ibfk_2 foreign key (subgroup_id) references customers_groups (id)

我希望能够将customers_groups表与subgroup_mappings表沿着连接到自身，以便生成的表包含父组和子组。

mariadb

来源：https://stackoverflow.com/questions/77182830/sql-self-join-with-mapping-table

2条答案

按热度按时间

irtuqstp1#

select 
   cg.id as group_id,
   cg.group_shortname as group_shortname, 
   cs.id as subgroup_id,
   cs.group_shortname as subgroup_shortname 
from subgroup_mappings s
   inner join customers_groups cg on s.group_id = cg.id
   inner join customers_groups cs on s.group_id = cs.id

赞(0）回复(0）举报 2023-10-20

c9qzyr3d2#

你可以在一张table上做
当然，还有更多的可能性，以获得vwanted信息。

CREATE TABLE  customers_groups(
    id              int auto_increment primary key,
    group_shortname varchar(16)           not null,
  parent_id int,
  constraint customers_subgroups_table_ibfk_1 foreign key (parent_id) references customers_groups (id))

INSERT INTO customers_groups (group_shortname,parent_id) VALUES ('A', NULL)

INSERT INTO customers_groups (group_shortname,parent_id) VALUES ('B', 1),('C', 1),('D', 1)

INSERT INTO customers_groups (group_shortname,parent_id) VALUES ('E', 2),('F', 3),('G', 5)

WITH RECURSIVE cte (id, group_shortname) AS
(
  SELECT id,group_shortname  FROM customers_groups WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, CONCAT(cte.group_shortname,'->', c.group_shortname)  
  FROM cte  JOIN customers_groups c ON cte.id = c.parent_id
)
SELECT * FROM cte;

| ID|组短名|
| --|--|
| 1 |一|
| 2 |A->B|
| 3 |A->C|
| 4 |A->D|
| 5 |A->B->E|
| 6 |A->C->F|
| 7 |A->B->E->G|
fiddle

赞(0）回复(0）举报 2023-10-20

我来回答

mariadb 使用Map表的SQL自连接

2条答案

相关问题

热门标签

最新问答