我有一个这样的数据库设置:
表格:章节section_id|section_namei23kj|新闻o492q|事件表:小节sub_section_id|section_id|sub_section_nameq123l|i23kj|《华盛顿邮报》z765a|i23kj|《纽约时报》w439m|o492q|发布会
我想要一个页面来打印以下信息:
新闻《华盛顿邮报》纽约时报事件发布会
我正在使用查询:
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
然而,这会返回:
新闻《华盛顿邮报》新闻纽约时报事件发布会
如何将查询更改为只显示一次Section和该Section下的Subsections
您需要使用子查询,刚刚测试了它,查看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