在sql server中拆分字符串值

问题描述

我有两个表tblUserDetail和tblOrder。

这些字段在下面

tblUserDetail-

id（int）username（nvarchar ）orderid（nvarchar）

1 john 1

2 smith 2,3



tblOrder-

orderid（int）trainno（int）

1 12520

2 12521

3 12522





i需要以下输出

orderid用户名trainno

1 john 12520

2史密斯12521

3史密斯12522



i使用以下连接查询

i have two tables tblUserDetail and tblOrder.
the field of these are below
tblUserDetail-
id(int) username(nvarchar) orderid(nvarchar)
1 john 1
2 smith 2,3

tblOrder-
orderid(int) trainno(int)
1 12520
2 12521
3 12522


i want the following ouput
orderid username trainno
1 john 12520
2 smith 12521
3 smith 12522

i use the following join query

SELECT ,tblOrder.OrderId tblUserDetail.UserName, tblOrder.TrainNo
FROM tblOrder INNER JOIN tblUserDetail ON   ( tblUserDetail.OrderId)=convert(nvarchar, tblOrder.OrderId)

输出是

orderid用户名trainno

1 john 12520



最后一个两行未取。请建议我新的查询或编辑此查询，以便我得到我想要的输出。

that's output is
orderid username trainno
1 john 12520

the last two rows in not fetch. please suggest me new query or edit this query so i get my desired output.

推荐答案

id(int)  username(nvarchar)
1          john
2          smith

tblOrder

tblOrder

orderid(int) trainno(int)  UserID(int)
1              12520           1
2              12521           2
3              12522           2

现在，对于您需要的输出，查询将如下所示...

Now, for the output you needed, the query will be something like below...

SELECT
    tblOrder.OrderId,
    tblUserDetail.UserName,
    tblOrder.TrainNo
FROM
    tblOrder
INNER JOIN
    tblUserDetail
ON  tblUserDetail.id = tblOrder.UserID

create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (DU varchar(8000))
as
begin
   declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
           set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(DU) values(@slice)

       set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end


--select * from dbo.split('1,2',',')

______________________________________________________________________________________ __________



使用以下查询来执行您的操作...

________________________________________________________________________________________________

Use this below query to perform your action...

 declare @tble table
 ( id int ,
 username nvarchar(50),
 orderid nvarchar(50) )

 declare  @id int , @user nvarchar(50) , @orderid nvarchar(50)

DECLARE @cur    CURSOR
SET @cur = CURSOR FAST_FORWARD
 FOR SELECT * FROM tblUserDetail
OPEN @cur
FETCH NEXT
FROM @cur INTO @id,@user,@orderid
WHILE @@FETCH_STATUS = 0
BEGIN

 declare @tmp table ( id int)
 delete @tmp
   insert into @tmp select * from  dbo.split(@orderid,',')

    declare @idd int
    declare @cur_inner cursor
    set @cur_inner = cursor fast_forward
    for select * from @tmp
    open @cur_inner
    fetch next from @cur_inner into @idd
    WHILE @@FETCH_STATUS = 0
BEGIN

  insert into @tble (id,orderid ,username) values ( @id, @idd ,@user)


fetch next from @cur_inner into @idd
END
CLOSE @cur_inner
DEALLOCATE @cur_inner



FETCH NEXT
FROM @cur INTO @id,@user,@orderid
END
CLOSE @cur
DEALLOCATE @cur

select t.id,t.orderid,t.username,ord.trainno  from @tble t inner join tblorder ord
on t.orderid = ord.orderid

这篇关于在sql server中拆分字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！