SQL高手进来解答????????

Question

SELECT
    A.songcode,
    A.songname,
    B.text AS songlanguage,
    A.songlen,
    C.text AS songtype,
    D.name AS singer,
    E.name AS singer2,
    F.name AS singer3,
    G.name AS singer4
FROM Song AS A
    LEFT JOIN e_SongLanguane AS B
       ON A.songlanguage=B.id
    LEFT JOIN e_SingType AS C
       ON A.songtype=C.id
    LEFT JOIN singer AS D
       ON A.singer=D.code
    LEFT JOIN singer AS E
       ON A.singer2=E.code
    LEFT JOIN singer AS F
       ON A.singer3=F.code
    LEFT JOIN singer AS G
       ON A.singer4=G.code

Answer 1

多个表之间的join呀，以Song 表为主表，其他表与它左连接。

Answer 2

混分
SELECT
    A.songcode,
    A.songname,
    B.text AS songlanguage,
    A.songlen,
    C.text AS songtype,
    D.name AS singer,
    E.name AS singer2,
    F.name AS singer3,
    G.name AS singer4
FROM Song AS A
    LEFT JOIN e_SongLanguane AS B
       ON A.songlanguage=B.id
    LEFT JOIN e_SingType AS C
       ON A.songtype=C.id
    LEFT JOIN singer AS D
       ON A.singer=D.code
    LEFT JOIN singer AS E
       ON A.singer2=E.code
    LEFT JOIN singer AS F
       ON A.singer3=F.code
    LEFT JOIN singer AS G
       ON A.singer4=G.code

Answer 3

以song为主表，其他表和他左联接left join.

Answer 4

select  a.songcode,a.songname,b.[text] as songlanguage,a.songlen,c.[text] as songtype,d.name as singer,
isnull(e.name,'') as singer2,isnull(f.name,'') as singer3,isnull(g.name,'') as singer4
From Song a
left join e_SongLanguage b on a.songlanguage=b.id
left join e_SingType c on a.songtype=c.id
left join singer d on a.singer=d.code
left join singer e on a.singer2=e.code
left join singer f on a.singer3=f.code
left join singer g on a.singer4=g.code

Answer 5

create table Song 
(
  songcode int,
  songname varchar(50),
  songlanguage varchar(20),
  songtype varchar(20),
  singer varchar(30),
  singer2 varchar(30),
  singer3 varchar(30),
  singer4 varchar(30)
)
insert into Song select 1001,'冰雨','10008','80008','900001',null,null,null
union all select 1002,'吻别','10008','80009','900002','900003',null,null
union all select 1002,'沉默是金','10009','80009','900004','900005',null,null
union all select 1002,'朋友','10008','80008','900006',null,null,null
union all select 1002,'老鼠爱大米','10008','80009','900006','900001','900007','900002'
create table Singer
(
  Code varchar(30),
  [Name] varchar(30)
)
insert into Singer select '900001','刘德华'
union all select '900002','张学友'
union all select '900003','迪克牛仔'
union all select '900004','许冠杰'
union all select '900005','张国荣'
union all select '900006','周华健'
union all select '900007','阿杜'create table e_SongLanguage
(
  ID varchar(20),
  [Text] varchar(20)
)
insert into e_SongLanguage select '10008','国语'
union all select '10009','粤语'
union all select '10010','英语'create table e_SingType
( 
  ID varchar(20),
  [Text] varchar(20)
)
insert into e_SingType select '80008','独唱'
union all select '80009','合唱'
union all select '80010','其它'
 songcode int,
  songname varchar(50),
  songlanguage varchar(20),
  songtype varchar(20),
  singer varchar(30),
  singer2 varchar(30),
  singer3 varchar(30),
  singer4 varchar(30)
select S.songcode,S.songname,E.[Text],T.[Text],S1.[Name],S2.[Name],S3.[Name],S4.[Name]
from Song S left join e_SongLanguage E on S.songlanguage=E.ID left join e_SingType T on S.songtype=T.ID left join Singer S1 on S.singer=S1.Code left join Singer S2
on S.singer2=S2.Code left join Singer S3 on S.singer3=S3.Code left join Singer S4 on S.singer4=S4.Codesongcode    songname                                           Text                 Text                 Name                           Name                           Name                           Name
----------- -------------------------------------------------- -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1001        冰雨                                                 国语                   独唱                   刘德华                            NULL                           NULL                           NULL
1002        吻别                                                 国语                   合唱                   张学友                            迪克牛仔                           NULL                           NULL
1002        沉默是金                                               粤语                   合唱                   许冠杰                            张国荣                            NULL                           NULL
1002        朋友                                                 国语                   独唱                   周华健                            NULL                           NULL                           NULL
1002        老鼠爱大米                                              国语                   合唱                   周华健                            刘德华                            阿杜                             张学友(5 行受影响)

Answer 6

感谢./zzxap 和 liangCK 都是正确的

Answer 7

不好意思Song  的信息插入错误create table Song 
(
  songcode int,
  songname varchar(50),
  songlanguage varchar(20),
  songtype varchar(20),
  singer varchar(30),
  singer2 varchar(30),
  singer3 varchar(30),
  singer4 varchar(30)
)
insert into Song select 1001,'冰雨','10008','80008','900001',null,null,null
union all select 1002,'吻别','10008','80009','900002','900003',null,null
union all select 1003,'沉默是金','10009','80009','900004','900005',null,null
union all select 1004,'朋友','10008','80008','900006',null,null,null
union all select 1005,'老鼠爱大米','10008','80009','900006','900001','900007','900002'
create table Singer
(
  Code varchar(30),
  [Name] varchar(30)
)
insert into Singer select '900001','刘德华'
union all select '900002','张学友'
union all select '900003','迪克牛仔'
union all select '900004','许冠杰'
union all select '900005','张国荣'
union all select '900006','周华健'
union all select '900007','阿杜'create table e_SongLanguage
(
  ID varchar(20),
  [Text] varchar(20)
)
insert into e_SongLanguage select '10008','国语'
union all select '10009','粤语'
union all select '10010','英语'create table e_SingType
( 
  ID varchar(20),
  [Text] varchar(20)
)
insert into e_SingType select '80008','独唱'
union all select '80009','合唱'
union all select '80010','其它'
 songcode int,
  songname varchar(50),
  songlanguage varchar(20),
  songtype varchar(20),
  singer varchar(30),
  singer2 varchar(30),
  singer3 varchar(30),
  singer4 varchar(30)
select S.songcode,S.songname,E.[Text],T.[Text],S1.[Name],S2.[Name],S3.[Name],S4.[Name]
from Song S left join e_SongLanguage E on S.songlanguage=E.ID left join e_SingType T on S.songtype=T.ID left join Singer S1 on S.singer=S1.Code left join Singer S2
on S.singer2=S2.Code left join Singer S3 on S.singer3=S3.Code left join Singer S4 on S.singer4=S4.Code
songcode    songname                                           Text                 Text                 Name                           Name                           Name                           Name
----------- -------------------------------------------------- -------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1001        冰雨                                                 国语                   独唱                   刘德华                            NULL                           NULL                           NULL
1002        吻别                                                 国语                   合唱                   张学友                            迪克牛仔                           NULL                           NULL
1003        沉默是金                                               粤语                   合唱                   许冠杰                            张国荣                            NULL                           NULL
1004        朋友                                                 国语                   独唱                   周华健                            NULL                           NULL                           NULL
1005        老鼠爱大米                                              国语                   合唱                   周华健                            刘德华                            阿杜                             张学友(5 行受影响)