表1:bookbookid(int) bookname(nvarchar)
1 西游记
2 红楼梦
3 水浒传
4 三国演义表2:stockstockid(int) stockname(nvarchar) stockbook(nvarchar)
1 库1 1,2,4怎么根据表2的stockbook 字符值 "1,2,4" 查询得到 "西游记,红楼梦,三国演义"
1 西游记
2 红楼梦
3 水浒传
4 三国演义表2:stockstockid(int) stockname(nvarchar) stockbook(nvarchar)
1 库1 1,2,4怎么根据表2的stockbook 字符值 "1,2,4" 查询得到 "西游记,红楼梦,三国演义"
stockbook=stuff((select ','+bookname from book
where charindex(','+rtrim(bookid)+',',','+stockbook+',')>0 for xml path('')),1,1,'')
from stock a
insert into book select 1,'西游记'
insert into book select 2,'红楼梦'
insert into book select 3,'水浒传'
insert into book select 4,'三国演义'
create table stock(stockid int,stockname nvarchar(20), stockbook nvarchar(20))
insert into stock select 1,'库1','1,2,4'
go
select a.* from book a inner join stock b on charindex(','+LTRIM(a.bookid)+',' , ','+b.stockbook+',')>0
/*
bookid bookname
----------- --------------------
1 西游记
2 红楼梦
4 三国演义(3 行受影响)*/
go
drop table book,stock
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-28 14:15:42
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[book]
if object_id('[book]') is not null drop table [book]
go
create table [book]([bookid] int,[bookname] varchar(8))
insert [book]
select 1,'西游记' union all
select 2,'红楼梦' union all
select 3,'水浒传' union all
select 4,'三国演义'
--> 测试数据:[stock]
if object_id('[stock]') is not null drop table [stock]
go
create table [stock]([stockid] int,[stockname] varchar(3),[stockbook] varchar(5))
insert [stock]
select 1,'库1','1,2,4'
--------------开始查询--------------------------
select
stockid,stockname,
stockbook=stuff((select ','+bookname from book where charindex(','+rtrim(bookid)+',',','+stockbook+',')>0 for xml path('')),1,1,'')
from
stock t
----------------结果----------------------------
/* stockid stockname stockbook
----------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 库1 西游记,红楼梦,三国演义(1 行受影响)*/
create table books (bookid int,bookname nvarchar(20))
insert into books
select 1,N'西游记' union
select 2,N'红楼梦' union
select 3,N'水浒传' union
select 4,N'三国演义'
create table stock(stockid int,stockname nvarchar(5),stockbook nvarchar(20))
insert into stock select 1,'库1',N'1,2,4'select stockid,stockname,
stockbook=stuff((select ','+bookname from books
where charindex(','+rtrim(bookid)+',',','+a.stockbook+',')>0 for xml path('')),1,1,'')
from stock a/*
stockid stockname stockbook
----------- --------- --------------------------
1 库1 西游记,红楼梦,三国演义(1 行受影响)
--分解字符串包含的信息值后然后合并到另外一表的信息
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-23 广东深圳)/*问题描述
tba
ID classid name
1 1,2,3 西服
2 2,3 中山装
3 1,3 名裤
tbb
id classname
1 衣服
2 上衣
3 裤子我得的结果是
id classname name
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法,创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id classid name
----------- ------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID classid name
----------- -------------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) T
)A
OUTER APPLY
(
SELECT [classname]= STUFF(REPLACE(REPLACE((
SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from
(
SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
) tbc , tbb where tbc.classid = tbb.id
) N
WHERE id = A.id and name = A.name
FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id classname name
----------- -------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(3 行受影响)
*/
where charindex(',' + cast(bookid as varchar) + ',' , ',' + n.stockbook + ',' ) > 0
','+(SELECT stockbook FROM stock WHERE stockid='1')+',')>0