问题描述
我有3张桌子
表1
ID名称dob
--- ---- ----
101 ABc 10/march/86
102 XYZ 11/三月/1986
表2
ID HobbyID
--- --------
101 1
101 2
102 1
102 2
表3
HobbyID HobbyName
------- ---------
1音乐
2个游戏
因此,请告诉我该查询,以便输出如下所示:
ID名称DOB HobbyName
--- ----- ------ ----------
101 ABC 10/march/86音乐,游戏
102 Xyz 11/march1986 Music,Games
i have 3 tables
table 1
ID Name dob
--- ---- ----
101 ABc 10/march/86
102 XYZ 11/march/1986
Table 2
ID HobbyID
--- --------
101 1
101 2
102 1
102 2
Table 3
HobbyID HobbyName
------- ---------
1 Music
2 Games
So please tell me the query so the output comes like this:
ID Name DOB HobbyName
--- ----- ------ ----------
101 ABC 10/march/86 Music,Games
102 Xyz 11/march1986 Music,Games
推荐答案
SELECT T1.ID, T1.NAME, T1.DOB, A.HOBBYNAME || ',' || B.HOBBYNAME
FROM (SELECT T2.ID, T3.HOBBYNAME
FROM TABLE2 T2,
TABLE3 T3
WHERE T2.HOBBYID = T3.HOBBYID
AND T2.HOBBYID = 1) A,
(SELECT T2.ID, T3.HOBBYNAME
FROM TABLE2 T2,
TABLE3 T3
WHERE T2.HOBBYID = T3.HOBBYID
AND T2.HOBBYID = 2) B,
TABLE1 T1
WHERE T1.ID = A.ID
AND T1.ID = B.ID
ORDER BY T1.ID
这篇关于请告诉我SQL代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!