需要一个 qry 将逗号分隔的列与另一个包含其 ID 的表连接以查找代码 [英] Need a qry to join a comma separated column with another table with its Id in it to find the code
本文介绍了需要一个 qry 将逗号分隔的列与另一个包含其 ID 的表连接以查找代码的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子
<前>表名 TB1mpeFromWHId mpeToStoreList8 16、18、24和表tb2
是逗号分隔的storeid代码
我需要一个结果为
的查询<前>列 1 列 28 ncl、mcl、dcl 解决方案
首先你需要一个函数来解析逗号分隔的字符串到表格中,你可以使用这个(找到 [here])1:
CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000))退货@Result 表(值 BIGINT)作为开始声明 @str VARCHAR(20)声明 @ind IntIF(@input 不为空)开始SET @ind = CharIndex(',',@input)而@ind >0开始SET @str = SUBSTRING(@input,1,@ind-1)SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)INSERT INTO @Result 值 (@str)SET @ind = CharIndex(',',@input)结尾SET @str = @inputINSERT INTO @Result 值 (@str)结尾返回结尾
然后你可以使用这样的东西(当然还有更多的选择):
声明@searchId int设置@searchId = 8声明@tb1 表 (mpeFromWHId int, mpeToStoreList varchar(100))插入@tb1选择 8, '16,18,24'声明@tb2 表(nlid int,nlcode varchar(30))插入@tb2选择 16, 'ncl' 联合选择 18, 'mcl' 联合选择 24, 'dcl'选择东西((选择 ',' + nlcode来自@tb2其中 nlid 在 (选择值from dbo.Split1((从@tb1 中选择 mpeToStoreList 其中 mpeFromWHId = @searchId)))按 nlcode 订购对于 xml 路径(''),输入).value('(./text())[1]','varchar(max)'), 1, 2, '')
I have a table with
Table name TB1 mpeFromWHId mpeToStoreList 8 16,18,24
and Table tb2
are the codes of the comma separated storeid
nlid nlcode 16 ncl 18 mcl 24 dcl
I need a query that will result in
col1 Col2 8 ncl,mcl,dcl
解决方案
First you need a function to parse comma delimited string into table, you can use this (found [here])1:
CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000) )
RETURNS
@Result TABLE(Value BIGINT)
AS
BEGIN
DECLARE @str VARCHAR(20)
DECLARE @ind Int
IF(@input is not null)
BEGIN
SET @ind = CharIndex(',',@input)
WHILE @ind > 0
BEGIN
SET @str = SUBSTRING(@input,1,@ind-1)
SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
INSERT INTO @Result values (@str)
SET @ind = CharIndex(',',@input)
END
SET @str = @input
INSERT INTO @Result values (@str)
END
RETURN
END
Then you can use something like this (but there are many more options off course):
declare @searchId int
set @searchId = 8
declare @tb1 table (mpeFromWHId int, mpeToStoreList varchar(100))
insert into @tb1
select 8, '16,18,24'
declare @tb2 table (nlid int, nlcode varchar(30))
insert into @tb2
select 16, 'ncl' union
select 18, 'mcl' union
select 24, 'dcl'
select stuff((
select ',' + nlcode
from @tb2
where nlid in (
select Value
from dbo.Split1((select mpeToStoreList from @tb1 where mpeFromWHId = @searchId))
)
order by nlcode
for xml path(''), type
).value('(./text())[1]','varchar(max)'), 1, 2, '')
这篇关于需要一个 qry 将逗号分隔的列与另一个包含其 ID 的表连接以查找代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文