如何检索行.

问题描述

你好......
我有两个表table1和table2，table1有两个列ID，名称，并且table1中有三个记录
编号名称
7 abc
96 pqr
129 xyz
table1 id保存在table2s mast_id中，例如7 + 96 + 129
table2也有两个列ID，mast_id和一个记录init
id mast_id
1 7 + 96 + 129

如果我检索了table2行，它将像7,96和129这样分隔mast_id.

Hello....
i have two table table1 and table2, table1 have two coloumn id,name and there are three records in table1
id name
7 abc
96 pqr
129 xyz
table1 ids save in table2s mast_id like as 7+96+129
also table2 have two coloumn id,mast_id and one record init
id mast_id
1 7+96+129

whan i retrieve the table2 row it will seprate the mast_id like 7,96 and 129.

推荐答案

declare @Rowresult varchar(80)
select @Rowresult=mastId from table2 where id=1
// @Rowresult='7+96+129'
DECLARE @csv VARCHAR(255)
   SET @csv =@Rowresult;
   with s(start) as(
    SELECT distinct charindex('+','+'+@csv+'+',p)
    FROM
   (
    select * from
   (
     select row_number() over (order by m1.number) p from master..spt_values m1,master..spt_values m2
    ) z
   where p <=len(@csv)+2) x
    ),
    chunks(chunk) as
             (
    select
    substring(@csv,start,(select min(start) from s as s2 where s2.start>s.start)-start-1)
   from s
   where start<len(@csv)+2
     )
    select * from chunks

这篇关于如何检索行.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！