时间戳

首页 > 解决方案 > T-SQL 查询未在输出中分组结果

sql-server - T-SQL 查询未在输出中分组结果

问题描述

我在存储过程中嵌入了以下查询

select u.UserName, s.Name, count(i.id) as NumberAccounts, sum(i.total) as CCTotal
  from invoice i 
    inner join visit v on v.id = i.id
    inner join branch b on b.id = v.branchid
    inner join practice p on p.id = b.practiceid
    inner join visitscheme vs on vs.id = v.id
    inner join [plan] pl on pl.id = vs.planid
    inner join scheme s on s.id = pl.schemeid
    inner join creditcontrol cc on cc.SchemeId = s.id
    inner join [user] u on u.id = cc.userid
 where p.APIKey = @pracId
   and (u.id = @CCid OR @CCId = '999999')
   and (s.id = @SchemeId or @SchemeId = 999999)
 group by u.UserName, s.Name
 order by u.username

当我运行它而不是按用户名和方案分组的结果时，我得到以下信息

UserName    Name    NumberAccounts  CCTotal
chanel   BANKMED    9954            11882514.19
ciske    BANKMED    9954            11882514.19
Estee    BANKMED    9954            11882514.19
Feroza   BANKMED    9954            11882514.19

未应用分组，每个分组的值相同。有人知道我哪里出错了吗？

谢谢

标签： sql-servertsql

解决方案

就这样写吧……

select u.UserName, s.Name, count(i.id) as NumberAccounts, sum(i.total) as CCTotal
  from invoice i 
    inner join visit v on v.id = i.id
    inner join branch b on b.id = v.branchid
    inner join practice p on p.id = b.practiceid and p.APIKey = @pracId
    inner join visitscheme vs on vs.id = v.id
    inner join [plan] pl on pl.id = vs.planid
    inner join scheme s on s.id = pl.schemeid and (s.id = @SchemeId or @SchemeId = 999999)
    inner join creditcontrol cc on cc.SchemeId = s.id
    inner join [user] u on u.id = cc.userid and (u.id = @CCid OR @CCId = '999999')
 group by u.UserName, s.Name
 order by u.username

假设您的连接是正确的

推荐阅读