如何计算具有相同数字组合的行号 [英] 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屋！