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
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) */
select count(case when timea between 8 and 11 then a end) as a, count(case when timeb between 8 and 11 then b end) as b from TableName
或 这样写效率高些 select sum(case when timea between 8 and 11 and a is not null then 1 else 0 end) as a, sum(case when timeb between 8 and 11 and b is not null then 1 else 0 end) as b from TableName where (timea between 8 and 11 and a is not null) or (timeb between 8 and 11 and b is not null)
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
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)
*/
count(case when timeb between 8 and 11 then b end) as b
from TableName
这样写效率高些
select sum(case when timea between 8 and 11 and a is not null then 1 else 0 end) as a,
sum(case when timeb between 8 and 11 and b is not null then 1 else 0 end) as b
from TableName
where (timea between 8 and 11 and a is not null) or (timeb between 8 and 11 and b is not null)