sql查询问题

Question

数据设计如下
books表书籍表,每本书的有很多文章，保存在articles字段中

articles表包为文章表，每篇文章由多个人编辑完成（保存在authors列中）
author表包含作者信息
不使用游标，查询参与编写ID号为1的书的作者
结果为
id authorName
1 张三
2 李四
3 王五
4 小麦
5 小王
6 小李

各表数据

---------------------------------------------------------------
books表 数据如下
id articles
1 1,2,3
2 4,5,6
---------------------------------------------------------
articles 表数据如下
id authors
1 1,2,3
2 4,5,6
3 4,5,6
4 4,5,6
5 1,2,3
6 1,2,3
--------------------------------------------
author表 数据如下
id authorName
1 张三
2 李四
3 王五
4 小麦
5 小王
6 小李
特意设置成字符间的引用，而不是采用零时表，是考虑数据查询，与程序扩展性

Answer 1

create table books(id int primary key,articles varchar(100))
insert into books values(1,'1,2,3')
insert into books values(2,'4,5,6')
--select * from books

create table articles(id int primary key,authors varchar(100))
insert into articles values(1,'1,2,3')
insert into articles values(2,'4,5,6')
insert into articles values(3,'4,5,6')
insert into articles values(4,'4,5,6')
insert into articles values(5,'1,2,3')
insert into articles values(6,'1,2,3')
--select * from articles

create table author(id int primary key,authorName varchar(100))
insert into author values(1,'张三')
insert into author values(2,'李四')
insert into author values(3,'王五')
insert into author values(4,'小麦')
insert into author values(5,'小王')
insert into author values(6,'小李')

--select * from author

--drop function MySql1
create function MySql1(@id int)
returns varchar(8000)
as
begin
declare @len int,@ar varchar(100),@sql varchar(8000)
set @sql='select distinct authors from articles where '
select @ar=articles from books where id=1
select @len=len(@ar)
while(@len>0)
begin
if(left(@ar,1)<>',')
begin
select @sql=@sql+'id='+left(@ar,1)+' '
if(@len>1)
select @sql=@sql+'or '
end
set @len=@len-1
set @ar=substring(@ar,2,@len)
end
return (@sql)
end
--select dbo.MySql1(1)
--exec sp_helptext Mysql1

--drop proc Myprc

create table MyetempTB(chr varchar(100))

create proc Myprc(@Myii int)
as
begin
declare @sql varchar(8000) set @sql=dbo.Mysql1(@Myii)
exec (@sql)
end
go

--drop proc Myprc2
create proc Myprc2(@Myii int)
as
begin
delete from MyetempTB
insert into MyetempTB exec dbo.Myprc @Myii
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+chr from MyetempTB
set @ret = stuff(@ret,1,1,'')
declare @sql varchar(8000)
set @sql='select distinct * from author where '
declare @len int set @len=len(@ret)
while(@len>0)
begin
if(left(@ret,1)<>',')
begin
select @sql=@sql+'id='+left(@ret,1)+' '
if(@len>1)
select @sql=@sql+'or '
end
set @len=@len-1
set @ret=substring(@ret,2,@len)
end
exec (@sql)
end
go
执行:
exec Myprc2 1

测试结果如下:
（所影响的行数为 2 行）

（所影响的行数为 2 行）

id authorName
----------- ----------------------------------------------------------------------------------------------------
1 张三
2 李四
3 王五
4 小麦
5 小王
6 小李

（所影响的行数为 6 行）

--基本逻辑如下:

--首先查询出books表中的articles
--MySql1函数:
--它是将articles查询出的结果拼凑成一条查询语句,此条查询语句拼凑后基本如下:
--'select distinct authors from articles where id=1 or id=2 or id=3'
--当然如果查询出的结果是7,8,9 那拼凑后的语句就是:
--'select distinct authors from articles where id=7 or id=8 or id=9'

--create table MyetempTB(chr varchar(100))是建立一个转储表,这个表是不可缺少的

--存储过程 Myprc(@Myii int)是将拼凑的语句查询出表

--存储过程Myprc2函数主要步骤:
--将Myprc查询出的表利用临时转储表储存,在将转储表的authors字段内的所有内容组合起来拼凑成查询语句
--转储表的内容是两行数据:
--第一行: 1,2,3
--第二行: 4,5,6
--内容组合: 1,2,3,4,5,6
--查询语句拼凑后如下:
--'select distinct * from author where id=1 or id=2 or id=3 or id=4 or id=5 or id=6'

--执行Myprc2 参数是你要查询的ID

--执行结果当然是楼主你想要的数据了
--楼主可以多插入一些测试数据,测试一下是否正确

Answer 2

select * from author where id in(select authors from articles where id in (select artices from books where id =1) )

没列名，不能给出查询条件。

没有LIKE就不嫩查吗？什么逻辑？这里都说了查ID是1的名单，LIKE个毛啊！

Answer 3

select * from author a ,authors b ,books c
where a.id =b.authors
and b.id =c.articles
and c.id =1

Answer 4

上面两位纯粹是胡乱回答

一对多连接关系, 外键关系需要保存在多的那张表里, 的你这数据表设置完全反了, 如果是刚开始做, 建议修改数据结构吧, Books表删除Articles列, Articles表增加BooksId列来保存文章对应的书籍, 并删除authors列, authors里面增加articlesId列来保存作者对应的书籍, 如果是在多对多关系, 则关系需要另外建表

追问

特意这样设计的，以提高程序的扩展性。

追答

晕, 没看出来扩展性哪里好, 反倒是你以后删除, 更新, 查询起来要麻烦多了
你可以试试以下的查询

--首尾加逗号是为了避免类似 '121' 和'21' 这样的匹配问题
select c.id, c.authorName
from books b
join articles a on ',' + b.articles + ',' like '%,' + cast(a.id as varchar) + ',%'
join authors c on ',' + a.authors + ',' like '%,' + cast(c.id as varchar) + ',%'
where b.id = 1

追问

like字句效率不高吧..

追答

没办法, 谁让你用这种方式来保存数据呢, 前两位同志倒是没用like, 但一看就是错的
或者你写一个函数拆分这些字符串, 但估计效率也不高

追问

我已经有一种方法了 但是不是最简洁的。。这里有人用这个函数说可以解决 但是我一直卡在了author的结果条件在一个多行一列的结果集当中select distinct authors from articles
where CHARINDEX(',',(select articles from books where id=1))>0 ，最好要筛选author 的id在这个结果集中，现在不用游标 没其他思路

追答

where CHARINDEX(',',(select articles from books where id=1))>0
你这是什么逻辑? 看不懂, 你确认这条件能查出来正确的结果?
是不是这样? CHARINDEX(cast(id as varchar) ,(select articles from books where id=1))>0
这个是肯定不行的, 比如 articles.id = 10, books.articles = '101, 102, 103' 这样CHARINDEX也会返回非0值, 但事实上这是不对的

华信333 的回答我不多做评论, 事实上这种写法肯定不行的, 至少SQL Server是不支持, 楼主给出了数据表结构和里面的数据, 你自己把它们创建出来查一查就知道了

Answer 5

兄弟你这样设计好像违反了3范式。。好像连第一范式不满足不了。。如果要多对多的话。。建一个中间表吧。。。