首頁(yè) 猿問(wèn) SQL多表查詢問(wèn)題

SQL多表查詢問(wèn)題

SQL Server

回首憶惘然 2018-12-07 06:49:58

數(shù)據(jù)設(shè)計(jì)如下books表書(shū)籍表,每本書(shū)的有很多文章，保存在articles字段中articles表包為文章表，每篇文章由多個(gè)人編輯完成（保存在authors列中）author表包含作者信息不使用游標(biāo)，查詢參與編寫(xiě)ID號(hào)為1的書(shū)的作者結(jié)果為id??? authorName1???? 張三2???? 李四3???? 王五4???? 小麥5???? 小王6???? 小李各表數(shù)據(jù) ---------------------------------------------------------------books表數(shù)據(jù)如下id articles1? 1,2,32? 4,5,6---------------------------------------------------------articles 表數(shù)據(jù)如下id?? authors1??? 1,2,32??? 4,5,63??? 4,5,64??? 4,5,65??? 1,2,36??? 1,2,3--------------------------------------------author表數(shù)據(jù)如下id??? authorName1???? 張三2???? 李四3???? 王五4???? 小麥5???? 小王6???? 小李

查看完整描述

2 回答

翻閱古今

TA貢獻(xiàn)1780條經(jīng)驗(yàn) 獲得超5個(gè)贊

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