如何按升序获取计算列数据 [英] how to get computed column data in ascending order

问题描述

使用以下查询，我得到的结果为

using the below query i am getting the result as

select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ','  as [text()] from Savings_Account_Applicant where ID =saa.ID  for xml path(''))  as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ORDER BY ID

输出:

output:

ID Member_ID
18 39,39,39,51,39
25 51
97 41
102 41,51
113 41,116
155 39,217
888 41,49,147,149,151,148,175,68
662 202,202
841 116,3,3

如何以升序获取输出Member_ID值

例如:

how to get output Member_ID values in ASCENDING order

ex:

ID Member_ID
18 39,39,39,39,51
25 51
97 41
102 41,51
113 41,116
155 39,217
888 41,49,68,147,148,149,151,175
662 202,202
841 3,3,116

推荐答案

在子查询中使用顺序，

use order by in sub query,

select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ORDER BY ID

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATEFunction [dbo].[fn_Splitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于如何按升序获取计算列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！