Count(*) 返回 null [英] Count(*) returns null

问题描述

我有通话详情记录表.每行都有 UserId 和 UserBNumber.

I have call details records table. Each row has got UserId and UserBNumber.

我还有一个表，其中包含 UserId 和 Number 以便有号码我可以说出用户的 ID.

I also have got table which has UserId and Number so that having number I can say what is ID of the user.

这样我就可以从每条 CDR 记录中知道呼叫者的 ID 是什么.

So that from each CDR record I can say what is an ID of one who calls an one who receives call.

有时用户呼叫不在我的用户数据库中的号码(网络外呼叫)

Sometimes user calls to number which isnt in my database of users (calls outside the network)

现在我想要查询它给我 UserAId(caller), UserBId(receiver), count(*)

Now I would like to have query which gives me UserAId(caller), UserBId(receiver), count(*)

这样我就能知道用户之间的连接数.

So that I would know number of connections between users.

UserAId, UserBId, NumberOfConnections

如果接听电话的号码不在我的表中，则用户 A 呼叫网络外的某人.

If number who receives call isnt in my table then UserA calls someone outside the network.

我想要结果:

UserAId, NULL, NumberOfConnectionsOutsideTheNetwork

这是我的查询:

TableA: CDR Table
TableB: User -> Number table

select A.UserId, B.UserId, count(*)
from select tableA A 
left outer join tableB B
on A.UserBNumber = B.Number
group by A.UserId, B.UserId   

问题是我 Count(*) 有时会返回 NULL.

problem is that I Count(*) sometimes returns NULL.

我做错了什么?

推荐答案

您的查询在尝试执行计数时正在执行外连接.如果 B.UserId 被列为 NULL，那么 count(*) 也将返回 NULL.您可以通过使用count (A.*)"显式执行 A 的计数或将其包装在 ISNULL() 中来解决此问题.

Your query is performing an outer join while trying to perform a count. If B.UserId is listed as NULL, then the count(* ) will return NULL, as well. You can fix this by explicitly performing a count of A using "count (A.*)" or by wrapping it in ISNULL().

select A.UserId, B.UserId, count(A.*)
  from select tableA A 
  left outer join tableB B
    on A.UserBNumber = B.Number
 group by A.UserId, B.UserId   

或

select A.UserId, B.UserId, isnull(count(*),0)
  from select tableA A 
  left outer join tableB B
    on A.UserBNumber = B.Number
 group by A.UserId, B.UserId   

这篇关于Count(*) 返回 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！