在sql server中拆分字符串值 [英] split string value in 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.

推荐答案

因为你有一个 1：N 关系来自 tblUserDetail 到 tblOrder ，所以你应该在 tblOrder 中有一个列表存储 UserID ，而不是在 tblUserDetail orderid >表。



因此，表格应如下所示......



tblUserDetail

As you have a 1:N Relation from tblUserDetail to tblOrder, so you should have a Column in tblOrder table storing UserID, instead of having a Column orderid in tblUserDetail table.

So, the tables should look like below...

tblUserDetail

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

HI


请按照以下步骤操作..



创建如下用户定义的功能..

HI
Please follow these steps..

Create an user defined funtion as below..

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

有几种方法可以实现这一点。如果你问，这意味着你没有阅读 CP QA规则 [ ^ ]。



请使用 SearchBox [ ^ ]下次本网站的右上角。

There are several ways to achieve that. If you ask, it means you did not read CP QA rules[^].

Please, use SearchBox[^] at the top-right corner of this site next time.

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