SQL多表查询问题

Question

数据设计如下books表书籍表,每本书的有很多文章，保存在articles字段中articles表包为文章表，每篇文章由多个人编辑完成（保存在authors列中）author表包含作者信息不使用游标，查询参与编写ID号为1的书的作者结果为id    authorName1     张三2     李四3     王五4     小麦5     小王6     小李 各表数据 ---------------------------------------------------------------books表 数据如下id articles1  1,2,32  4,5,6---------------------------------------------------------articles 表数据如下id   authors1    1,2,32    4,5,63    4,5,64    4,5,65    1,2,36    1,2,3--------------------------------------------author表 数据如下id    authorName1     张三2     李四3     王五4     小麦5     小王6     小李

Answer 1

USE tempdb
GO

CREATE TABLE BOOKS
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ARTICLES NVARCHAR(20)
)
GO
INSERT INTO BOOKS
SELECT '1,2,3' UNION
SELECT '4,5,6' 
GO

CREATE TABLE ARTICLES
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
AUTHORS NVARCHAR(20)
)
GO

INSERT INTO ARTICLES
SELECT '1,2,3' UNION
SELECT '4,5,6' UNION
SELECT '4,5,6' UNION
SELECT '4,5,6' UNION
SELECT '1,2,3' UNION
SELECT '1,2,3' 
GO

CREATE TABLE AUTHORS
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
AUTHORNAME NVARCHAR(8)
)
GO
INSERT INTO AUTHORS
SELECT '张三' UNION
SELECT '李四' UNION
SELECT '王五' UNION
SELECT '小麦' UNION
SELECT '小王' UNION
SELECT '小李' 
GO

SELECT * FROM BOOKS
SELECT * FROM ARTICLES
SELECT * FROM AUTHORS
GO

ALTER PROC GETBOOKAUTHORSBYID
@BOOKID INT
AS
DECLARE @SQL NVARCHAR(200),@ARTICLES NVARCHAR(20),@AUTHORS NVARCHAR(50)
SET @AUTHORS=''
SELECT @ARTICLES=ARTICLES FROM BOOKS WHERE ID=@BOOKID
SET @SQL = 'SELECT @A=@A+AUTHORS+'','' FROM ARTICLES WHERE ID IN ('+@ARTICLES+')'
EXEC SP_EXECUTESQL @SQL,N'@A NVARCHAR(50) OUT',@AUTHORS OUT
IF(LEN(@AUTHORS)>0)
BEGIN
SET @AUTHORS=SUBSTRING(@AUTHORS,0,LEN(@AUTHORS))
SET @SQL = 'SELECT ID,AUTHORNAME FROM AUTHORS WHERE ID IN ('+@AUTHORS+')'
EXEC(@SQL)
END
GO

EXEC GETBOOKAUTHORSBYID 1

Answer 2

你最终要查询的结果是什么？