具有自连接表的SELECT子句 [英] SELECT clause with self joining table

问题描述

两个表:

//SKILL
SNAME
---------------------
C++
C#
C
JAVA

//REQUIRED
SNAME          REQUIRED
------------------------
C++               C
C#             reading
C              writing
C              reading
JAVA              C

我使用了这个查询

SELECT DISTINCT sq.SNAME,sq1.requires AS "requires" FROM SREQUIRED sq
INNER JOIN SREQUIRED sq1
ON sq.SNAME='C++';

但我的输出是

SNAME                REQUIRES
-------------------------------
 C++                     C
 C++                  reading
 C++                  writing

我的问题是，如果我想找到C ++技能直接要求的技能，我应该使用什么查询?输出应该是这样的:

my question is if i want to find the skill that directly required by the skills C++, what query should i use ? output should be like this:

SNAME                 REQUIRES
-------------------------------
 C++                     C
 C                    reading
 C                    writing

因为C ++需要C，而C需要读写.就像第一级，第二级等等.

Because C++ required C , and C required reading and writing. it's just like first level second level and etc.

推荐答案

使用CTE使其通用.级别可能不止2. 使用以下查询来获取所需的结果.

Use CTE to make it generic. Levels may go deeper than just 2. Use the following query to get required result.

with cte
As
(
Select SName, [Required] from courses where SName = 'C++'
Union All
Select courses.SName, courses.[Required] from courses 
   inner join cte on courses.SName = cte.[Required]
)
select * from cte

希望有帮助.

这篇关于具有自连接表的SELECT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！