如何计算具有相同数字组合的行号 [英] how to count the row number for the having a combination of same numbers
本文介绍了如何计算具有相同数字组合的行号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我们提供多个值作为输入时,如何计算下表的行号,该行具有相同的数字组合.
Hi,
How to count the row number for the below table which is having a combination of same numbers when we are giving multiple values as input.
@Member_ID = 39,51,---
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
上面的组合是通过编写以下查询生成的-
The above combination is generated by writing the following 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 for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account;
推荐答案
应用如下行号
Apply row number as below
select RowNo,Id,A_Ids from
(select Row_Number() over(order by Applicant_Member_Ids) as RowNo, ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) as A_Ids 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
) as temp order by RowNo;
祝您编码愉快!
:)
Happy Coding!
:)
首先,请参阅我对您的帖子的评论.
如果您要计算ID
First of all, see my comment to your post.
If you want to count ID''s
SELECT COUNT(ID) AS [CountOfID's]
FROM YourTable
或
or
SELECT COUNT(DISTINCT ID) AS [CountOfID's]
FROM YourTable
类似地, 如果您要计算MemebrID的数量
Analogically, if you want to count MemebrID''s
SELECT COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable
或
or
SELECT COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable
最后, 如果您要为每个ID计算MemberID
Finally, if you want to count MemberID''s for each ID
SELECT ID, COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable
或
or
SELECT ID, COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable
这篇关于如何计算具有相同数字组合的行号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文