想实现:
select count(*) from student where firstphonecode is not null and firstphonecode!=''
select count(*) from student where secondphonecode is not null and secondphonecode!=''
第一个的结果:791
第二个的结果:398我想一下子得到:1189
select count(*) from student where firstphonecode is not null and firstphonecode!=''
select count(*) from student where secondphonecode is not null and secondphonecode!=''
第一个的结果:791
第二个的结果:398我想一下子得到:1189
select
SUM(CASE WHEN firstphonecode!='' THEN 1 ELSE 0 END )
+
SUM(CASE WHEN secondphonecode!='' THEN 1 ELSE 0 END )
from student 这样应该就可以了。
from student
where firstphonecode!='' and (firstphonecode is not null or secondphonecode is not null)
declare @student table
(firstphonecode varchar(4),secondphonecode varchar(4))
insert into @student
select 1,4 union all
select 2,5 union all
select 3,6 union all
select null,null union all
select null,5 union all
select 8,null union all
select '','' union all
select null,9--select * from @student
select
SUM(CASE WHEN firstphonecode!='' THEN 1 ELSE 0 END )
+
SUM(CASE WHEN secondphonecode!='' THEN 1 ELSE 0 END )
from @student
/*
11
*/
--楼上答案不完整
看错逻辑select count(firstphonecode) + count(secondphonecode)
from student
where isnull(firstphonecode,'')!='' or isnull(secondphonecode,'')!=''
declare @student table
(firstphonecode varchar(4),secondphonecode varchar(4))
insert into @student
select 1,4 union all
select 2,5 union all
select 3,6 union all
select null,null union all
select null,5 union all
select 8,null union all
select '','' union all
select null,9select
(select count(*) from @student
where firstphonecode is not null and firstphonecode<>'')
+
(select count(*) from @student
where secondphonecode is not null and firstphonecode<>'')/* 直接加上就可以了
9
*/
而在一个where中还真难实现!
from student
where isnull(firstphonecode,'')!='' or isnull(secondphonecode,'')!=''
select
SUM(CASE WHEN firstphonecode!='' THEN 1 ELSE 0 END )
+
SUM(CASE WHEN secondphonecode!='' THEN 1 ELSE 0 END )
from @student
where firstphonecode is not null
or secondphonecode is not null;