可以在单个选择中合并选择计数（*）和总和（列）吗？

Question

我目前从我的数据库有两个选择拉两个长值：

// We need the number of users and the total bandwidth 
long numUsers, totalBandwidth; 
using (IDbCommand cmd = conn.CreateCommand()) 
{ 
    cmd.CommandText = "select count(*) from [User] where [User].CompanyId = @CompanyId"; 
    DbUtilities.AddParam(cmd, "@CompanyId", id); 
    numUsers = (long)(decimal)cmd.ExecuteScalar(); 
} 
using (IDbCommand cmd = conn.CreateCommand()) 
{ 
    cmd.CommandText = "select sum(BandwidthThisMonth) from [User] where [User].CompanyId = @CompanyId"; 
    DbUtilities.AddParam(cmd, "@CompanyId", id); 
    totalBandwidth = (long)(decimal)cmd.ExecuteScalar(); 
} 

我觉得这个逻辑可以是一个单一的选择，返回两个数字。但是我所尝试过的一切都给了我错误。这可以做到吗？

Answer 1

select count(*) as count_all, 
     sum(BandwidthThisMonth) as sum_BandwidthThisMonth 

from [User] 

where [User].CompanyId = @CompanyId 

但你会得到两列而不是一个标量。 so you'll need to handle that...

Answer 2

如果你希望他们在同一列，你可以使用联合。这扫描表2次，所以效率不高。

select "count" as key 
, count(*) as value 
from [User] 
where [User].CompanyId = @CompanyId 
union all 
select "sum_BandwidthThisMonth" key 
, sum(BandwidthThisMonth) as value 
from [User] 
where [User].CompanyId = @CompanyId 

如果您不想排序，请使用union all。联盟做一个排序。对于2行没有什么大不了的，但...