首先添加测试表和数据,Book表中的booktype存的是对应字典表中的id。
--创建测试表Book
CREATE TABLE Book (
name VARCHAR(100),
booktype VARCHAR(100)
);
--插入测试数据
INSERT INTO Book VALUES ('水浒传', '1,2,3');
INSERT INTO Book VALUES ('三体', '1,4');
INSERT INTO Book VALUES ('斗破苍穹', '3,4');
--创建测试表Dictionary
CREATE TABLE Dictionary(
id int,
value VARCHAR(100)
);
--插入测试数据
INSERT INTO Dictionary VALUES (1, '名著');
INSERT INTO Dictionary VALUES (2, '历史');
INSERT INTO Dictionary VALUES (3, '武侠');
INSERT INTO Dictionary VALUES (4, '玄幻');
Book表:
Dictionary(字典)表:
最终我想要的样子是这样:
遍历表生成为一行数据可以巧用 for xml path 方法,一开始我是这样写的,结果sql语句报错了。
select name,booktype,(select value+',' from Dictionary where id in(booktype) for xml path('')) as BookTypeStr from Book;
报错原因是booktype为VARCHAR类型,id为int类型,sql中的“ id in(booktype) ”这一点就会出现类型转换出错,所以要记得in()方法不能这样使用。
更改方法后的sql如下:
select name,booktype,(select value+',' from Dictionary where CHARINDEX( ','+rtrim(id)+',',','+booktype+',')>0 for xml path('')) as BookTypeStr from Book;
可以用CHARINDEX方法来查询id被包含于对应booktype中的所有数据,再用for xml path(’’)方法来生成为一行。注意:id需要加上rtrim()或者是ltrim()方法,来使id转为字符类型,这样才能符合CHARINDEX()的参数类型,否则会报错。
这样就能达到想要的效果了。
发现BookTypeStr中后面多了一个逗号,可以使用LEFT()函数,或者STUFF()函数来截取一下。最终sql如下:
select a.name,a.booktype,left(a.BookTypeStr,LEN(a.BookTypeStr)-1) as BookTypeStr from (
select name,booktype,(select value+',' from Dictionary where CHARINDEX( ','+rtrim(id)+',',','+booktype+',')>0 for xml path('')) as BookTypeStr from Book
)a
效果图为:
把工作中遇到的问题记录下来,方便自己查看的同时,希望也能帮助到大家!
2018/12/25