sql中如何求两列的和

qiuqingpo 2010-10-10 03:23:34
想实现:
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
...全文
1147 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
闹铃 2010-10-10
  • 打赏
  • 举报
回复

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;
打一壶酱油 2010-10-10
  • 打赏
  • 举报
回复
select count(*) from student where (firstphonecode is not null or secondphonecode is not null ) and firstphonecode!=''
SQLCenter 2010-10-10
  • 打赏
  • 举报
回复
唉,晕了,还有=''的

select count(nullif(firstphonecode,'')) + count(nullif(secondphonecode,''))
from student
where isnull(firstphonecode,'')!='' or isnull(secondphonecode,'')!=''
qiuqingpo 2010-10-10
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 sqlcenter 的回复:]

SQL code
select count(firstphonecode) + count(secondphonecode)
from student
where firstphonecode!='' and (firstphonecode is not null or secondphonecode is not null)
[/Quote]
1楼的正解.2楼的条件没有法一致.我是想只所有不为空都统计.
而在一个where中还真难实现!
叶子 2010-10-10
  • 打赏
  • 举报
回复

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
(select count(*) from @student
where firstphonecode is not null and firstphonecode<>'')
+
(select count(*) from @student
where secondphonecode is not null and firstphonecode<>'')

/* 直接加上就可以了
9
*/
SQLCenter 2010-10-10
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 sqlcenter 的回复:]

SQL code
select count(firstphonecode) + count(secondphonecode)
from student
where firstphonecode!='' and (firstphonecode is not null or secondphonecode is not null)
[/Quote]

看错逻辑

select count(firstphonecode) + count(secondphonecode)
from student
where isnull(firstphonecode,'')!='' or isnull(secondphonecode,'')!=''
叶子 2010-10-10
  • 打赏
  • 举报
回复

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
*/
--楼上答案不完整
SQLCenter 2010-10-10
  • 打赏
  • 举报
回复
select count(firstphonecode) + count(secondphonecode)
from student
where firstphonecode!='' and (firstphonecode is not null or secondphonecode is not null)
振乾 2010-10-10
  • 打赏
  • 举报
回复

select
SUM(CASE WHEN firstphonecode!='' THEN 1 ELSE 0 END )
+
SUM(CASE WHEN secondphonecode!='' THEN 1 ELSE 0 END )
from student

这样应该就可以了。

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧