php - MYSQL 如何停止行返回两次

Question

我有这样的数据库设置:

Table: Sectionssection_id | section_name  i23kj    |    News   o492q    |   EventsTable: Subsectionssub_section_id | section_id | sub_section_name     q123l      |   i23kj    | Washington Post    z765a      |   i23kj    |    NY Times    w439m      |   o492q    |   Launch Party  

I want a page to print the following information:

NewsWashington PostNy TimesEventsLaunch Party

I'm using the query:

SELECT Sections.section_id, Sections.section_name, Subsections.sub_section_id, Subsections.sub_section_name 
FROM Sections INNER JOIN Subsections 
ON Sections.section_id=Subsections.section_id

但是这会返回:

NewsWashington PostNewsNy TimesEventsLaunch Party

如何更改我的查询以仅显示该部分一次以及该部分下的子部分

Answer 1

你需要使用子查询，刚刚测试过看到 DEMO :

create table sections
(
  section_id varchar(10),
  section_name varchar(50)
);
create table subsections
(
  sub_section_id varchar(10),
  section_id varchar(10),
  sub_section_name varchar(50)
);
insert into sections 
       values('i23kj','News'),
             ('o492q','Events');
insert into subsections
       values('q123l','i23kj','Washington Post'),
             ('z765a','i23kj','NY Times'),
             ('w439m','o492q','Launch Party');

下面查询的结果正是您想要的:

select case 
       when q2.sub_section_id is null then section_name
       else sub_section_name end
from 

(select * from ( select Sections.section_id, Sections.section_name,
       null as sub_section_id, null as sub_section_name 
FROM   Sections
union
SELECT Sections.section_id, Sections.section_name,
       Subsections.sub_section_id, Subsections.sub_section_name 
FROM   Sections INNER JOIN Subsections 
       ON Sections.section_id=Subsections.section_id) as q 
       order by q.section_id,q.sub_section_id) as q2