问个查询语句！该怎么处理

问个查询语句！！！

如图所示，用SQL语句查询record表，得出record表下方的临时表（对a、b非NULL值进行计数，条件是8<=timea<=11的时候a才能计数，8<=timeb<=11的时候b才能计数，也就是说record表上红色部分是可计数的）
请问查询语句要怎么写呢？求指教感激不尽！

------解决方案--------------------
Select Sum(T.a), Sum(T.b)
From (Select Count(a) As a, 0 b From Rercord Where 8<=timea<=11 And a is Not Null
Union
Select 0 a, Count(b) As b From Rercord Where 8<=timeb<=11 And b is Not Null) T

------解决方案--------------------

SQL code


with Record as
(
    --虚拟出一张表
    select 1 as ID,null as a, 22 as b,10 as timea,7 as timeb union all
    select 2,33,null,11,8 union all
    select 3,44,33,9,9 union all
    select 4,11,null,3,9 union all
    select 5,22,31,7,11 union all
    select 6,null,5,13,10
)
select 
    sum(
        --增加一列，判断 timea如果在8 和 11 之间 并且 a 不为空 设置为1 ，最后sum统计
        case when timea between 8 and 11 and a IS not null 
             then 1
        else 0 
        end
    ) as a,
    SUM
    (
        --增加一列，判断 timea如果在8 和 11 之间 并且 b 不为空 设置为1 ，最后sum统计
        case when timeb between 8 and 11 and b IS not null 
             then 1
        else 0
        end 
    )as b 
from Record
/*
a           b
----------- -----------
2           3

(1 row(s) affected)
*/

问个查询语句！该怎么处理

相关推荐