代码之家 › 专栏 › 技术社区 › Ali Imran

存储过程计数中包含id号的行数

sql-server sql

Ali Imran · 技术社区 · 8 年前

我想得到count个ID为的索赔总数。

SELECT   
    InsuranceId,Name,
    COUNT(claimid) AS TotalPendingClaims
FROM 
    Claim 
GROUP BY
    InsuranceId

这是我的问题,但我想 claimid 这也是在上述查询中计算的。我需要列 claimIds=(1,2,4,5)

预期结果:

InsuranceId  Name TotalPendingClaims  ClaimIds
-----------------------------------------------
1            Med     2                  23,24
2            TX      1                   55
3            TED     3                  44,45,46

4 回复 | 直到 8 年前

Bùi Äá»©c Khánh 8 年前

你可以试试这个

    SELECT   
        InsuranceId
        , count(claimid) as TotalPendingClaims
        , claimids = STUFF((SELECT ',' + cast(claimid as varchar(255)) FROM Claim B WHERE B.InsuranceId = A.InsuranceId FOR XML PATH('')) , 1 , 1 , '' )
            From Claim A
            Group by InsuranceId

Krupa 8 年前

尝试此查询

SELECT InsuranceId, name, COUNT(*) TotalPendingClaims, 
ClaimIds = STUFF((SELECT ', ' + CONVERT(varchar, claimid)
           FROM  dbo.Claim c2
           WHERE c1.InsuranceId = c2.InsuranceId AND c1.name = c2.name
           FOR XML PATH('')), 1, 2, '')
FROM claim c1
GROUP BY InsuranceId, name
ORDER BY InsuranceId, name

Aswani Madhavan 8 年前

您可以使用下面的查询,在该查询中,stuff连接声明ID,并且可以单独计算计数

SELECT DISTINCT t2.InsuranceId ,
    STUFF((Select ','+ CONVERT(VARCHAR(5), claimid) FROM #TEMP_INSURANCE T1 
WHERE T1.InsuranceId=T2.InsuranceId FOR XML PATH('')),1,1,'')  AS ClaimID,
    Cnt
FROM #TEMP_INSURANCE t2 INNER JOIN
    (SELECT InsuranceId,COUNT(claimid) Cnt FROM #TEMP_INSURANCE GROUP BY InsuranceId)T ON t.InsuranceId = t2.InsuranceId

Ali Imran 8 年前

我的问题的答案是:

--changes addded by Ali Imran 12/13/2017 get also the claim number

claimid=stuff
(
 ( select distinct','+cast(c1.claimid as varchar(max))
  from #Claim c1 where
  c1.insuranceid=ins.insuranceid

  for xml path('')
  ),1,1,''
),

--Changes ended by Ali Imran

推荐文章