SQL，计算已打开和关闭的会话。

Question

鉴于这一表：

  Session | User | Start      | Stop  
        1 |    1 | 2014-10-10 | null  
        2 |    1 | 2014-10-10 | 2014-10-10  
        3 |    1 | 2014-09-10 | 2014-09-10  
        4 |    2 | 2014-10-10 | null  
        5 |    2 | 2014-10-10 | 2014-10-10

我想计算一下每个用户有多少个开放会话，以及给定日期该用户的会话总数：

 User | Date       | Open | Total |
    1 | 2014-10-10 |    1 |     2 |
    1 | 2014-09-10 |    0 |     1 |
    2 | 2014-10-10 |    1 |     2 |

通过对用户进行分组，开始和停止，我可以得到两行，一个是开放的会话，另一个是封闭的，但是我宁愿有两个列.

(我正在使用Server 2008 R2)

Answer 1

使用case表达式有条件地计数：

select
  user,
  start as [date],
  count(case when stop is null then 1 end) as open,
  count(*) as total
from sessions
group by user, start;

Answer 2

试试这个：

SELECT t.user, t.start, 
sum(
case
   when stop is null then 1 else 0
end) as 'Open',
count(t.start) as 'Total'
FROM yourtable t
GROUP BY t.user, t.start