如何计算sql中的多个字段

Question

嘿，我发现很多人问的问题和我的相似，但并不完全一样。

我有一个存储IP的数据库。有一个服务器字段和一个客户端字段。我想查看这两个字段，并提取出它们的所有不同的IP，然后找出每个IP作为客户端和服务器出现的次数。

http://forums.devx.com/showthread.php?t=19168 <--这有点类似，但不完全是。

这就是我现在所拥有的：

use mydb   
select y.client_ip as "IP",  
(select count(*)  
from t_Events x  
where start_time BETWEEN '10/7/2010 08:00:00 AM' AND '10/7/2010 04:00:00 PM' AND     x.client_ip = y.client_ip) as "Count of client IP",  
(select count(*)  
from t_Events x  
where start_time BETWEEN '10/7/2010 08:00:00 AM' AND '10/7/2010 04:00:00 PM' AND   x.client_ip = y.server_ip) as "Count of Server IP"  
from t_Events y  
Group By y.client_ip, y.server_ip  

这不起作用，因为我意识到我只查看client_ip字段中的in。

那么，我如何计算每个IP出现在服务器和客户端字段的时间？我需要先做一个临时表吗？

Answer 1

尝试如下所示：

select IP, sum(ClientCount) as ClientCount, sum(ServerCount) as ServerCount
from (
    select client_ip as IP, count(*) as ClientCount, null
    from t_Events
    where start_time BETWEEN '10/7/2010 08:00:00 AM' AND '10/7/2010 04:00:00 PM'
    group by client_ip 
    union all
    select server_ip as IP, null, count(*) as ServerCount
    from t_Events
    where start_time BETWEEN '10/7/2010 08:00:00 AM' AND '10/7/2010 04:00:00 PM'
    group by server_ip 
) a
group by IP