sql根据查询结果,去掉空值

mofeiovi 2012-01-07 04:09:14
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select count(*) from tb3 as t3 whree t3.id=org.id) as number3,
(select count(*) from tb4 as t4 whree t4.id=org.id) as number4,
(select count(*) from tb5 as t5 whree t5.id=org.id) as number5
from org as org,tb as tb where org.id=tb.id
group by org.name having count(*)>0

语句也就是这么个意思,现在是查出来了,可是有那么几行查询结果为0的也显示出来了,我不想显示全为0的这
一行记录
如下:
orgname ------number1-------number2-------number3-------number4------number5
aaaa-----------0--------------2--------------0-------------1------------0
bbbb-----------1--------------0--------------0-------------0------------0
cccc-----------0--------------0--------------0-------------0------------0
dddd-----------0--------------0--------------0-------------0------------0
eeee-----------0--------------1--------------0-------------1------------0

就是不取出number1,number2,number3,number4,number5全为0的这一行记录
求大师指明!!!!!

...全文
2626 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
Spade_J 2012-01-08
  • 打赏
  • 举报
回复
把你原先的结果集当做表表达式,不是直接使用别名作为条件,应该没有问题的
mofeiovi 2012-01-08
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 spade_j 的回复:]
SQL code


select * from
(
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.i……
[/Quote]


这样做条件不行的哇。。。
给别人一提醒,我才又想到这个。。。
select * from
(
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select count(*) from tb3 as t3 whree t3.id=org.id) as number3,
(select count(*) from tb4 as t4 whree t4.id=org.id) as number4,
(select count(*) from tb5 as t5 whree t5.id=org.id) as number5
from org as org,tb as tb where org.id=tb.id
group by org.name having count(*)>0
) A
where (select count(*) from tb1 as t1 whree t1.id=org.id)+
(select count(*) from tb2 as t2 whree t2.id=org.id)+(select count(*) from tb3 as tb3 whree tb3.id=org.id).....=0 这样来做条件,不用number做条件,虽然看起来语句长了点,但是应该 可以了哇。。。
好了洗澡睡觉了,明天还早起呢。楼上的,谢谢了啊
Spade_J 2012-01-08
  • 打赏
  • 举报
回复

select * from
(
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select count(*) from tb3 as t3 whree t3.id=org.id) as number3,
(select count(*) from tb4 as t4 whree t4.id=org.id) as number4,
(select count(*) from tb5 as t5 whree t5.id=org.id) as number5
from org as org,tb as tb where org.id=tb.id
group by org.name having count(*)>0
) A
where number1+number2+number3+number4+number5<>0

这么写应该没有问题的
mofeiovi 2012-01-08
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 snbxp 的回复:]
行不行楼主试一下再说
[/Quote]


试过了,还取过全部number2+...=0这也做过条件,不行的。好像在mysql里别名是可以做为条件来查询的。。。

谢谢你了,这么晚了还回答我的问题。
snbxp 2012-01-08
  • 打赏
  • 举报
回复
行不行楼主试一下再说
mofeiovi 2012-01-08
  • 打赏
  • 举报
回复
那看来还真只有这样写了。。。。。
楼上的这样不行的,用别名做条件查询不行的,会提示number1,number2...无效。我用的是sql server 2008.....悲催啊。
因为我查询返回的是一个分页集合,我又琢磨了下,在action里面把查询结果集合里面的这一行number1,number2...等全为0的记录把它去掉
snbxp 2012-01-07
  • 打赏
  • 举报
回复
select * from
(select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select count(*) from tb3 as t3 whree t3.id=org.id) as number3,
(select count(*) from tb4 as t4 whree t4.id=org.id) as number4,
(select count(*) from tb5 as t5 whree t5.id=org.id) as number5
from org as org,tb as tb where org.id=tb.id
group by org.name having count(*)>0
)A
where number1+number2+number3+number4+number5<>0
郗晓勇 2012-01-07
  • 打赏
  • 举报
回复
没有办法只有2楼那样一列一列的加上去.
yubofighting 2012-01-07
  • 打赏
  • 举报
回复
查询结果后在加sum列,sum为0的排除
mofeiovi 2012-01-07
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 sql77 的回复:]
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select co……
[/Quote]



我现在子查询有十九条number1.......number19,这样的话,在where里面岂不是
select count(*) from tb1 as t1 whree t1.id=org.id)>0 or同样也要写十九条?好像语句有点太多了,还有别的方法没啊
mofeiovi 2012-01-07
  • 打赏
  • 举报
回复
先谢谢楼上了,回头我再测试下。。。。。。

还有别的方法没啊
SQL77 2012-01-07
  • 打赏
  • 举报
回复
select (select orgname from org as og where org.id=og.id),
(select count(*) from tb1 as t1 whree t1.id=org.id) as number1,
(select count(*) from tb2 as t2 whree t2.id=org.id) as number2,
(select count(*) from tb3 as t3 whree t3.id=org.id) as number3,
(select count(*) from tb4 as t4 whree t4.id=org.id) as number4,
(select count(*) from tb5 as t5 whree t5.id=org.id) as number5
from org as org,tb as tb where org.id=tb.id
and (
(select count(*) from tb1 as t1 whree t1.id=org.id)>0 or (select count(*) from tb2 as t2 whree t2.id=org.id)>0 or ...)
group by org.name having count(*)>0
mofeiovi 2012-01-07
  • 打赏
  • 举报
回复
如何去掉查询结果为number全部为0的记录,求助

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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