提出此问的目的是为了解决如下问题:例如: 如果在b表,c表,d表中,n1的值永远大于0,那么用以下语句可以实现: 返回a表中有,而b表,c表,d表中都没有的id_my数值。select a.id_my from a left outer join b on a.id_my = b.id_my left outer join c on a.id_my = c.id_my left outer join d on a.id_my = d.id_my where isnull(b.n1,0)=0 and isnull(c.n1,0)=0 and isnull(d.n1,0)=0请问:还有没有比此更好,执行更快的SQL语句?
多此一举,用 is not null/is null 就可以判断是否为空!
to: pbsql(风云) where条件似乎是多此一举请问不用where那该如何写呢?难道要用not in 或 exit吗?to :chinaandys(风流泪,雨含笑) 多此一举,用 is not null/is null 就可以判断是否为空!是不是要改成如下的方式: select a.id_my from a left outer join b on a.id_my = b.id_my left outer join c on a.id_my = c.id_my left outer join d on a.id_my = d.id_my where (b.n1 is null) and (c.n1 is null) and (d.n1 is null) 请各位高手指点! 先谢了
当然is null可以判断,不信你到isql里面去试试!!! where (b.n1 is null) and (c.n1 is null) and (d.n1 is null)可以的
to: pbsql(风云) where条件似乎是多此一举请问不用where那该如何写呢?难道要用not in 或 exit吗?
select a.id_my from a left outer join b on a.id_my = b.id_my left outer join c on a.id_my = c.id_my left outer join d on a.id_my = d.id_my where b.n1 is null and c.n1 is null and d.n1 is null 判断一个字段是否为空,用column is null来测试
你是要实现:返回a表中有,而b表,c表,d表中都没有的id_my数值。select a.id_my from a where not exists(...) and ...
下面两种方法肯定都可以的,而且速度比not in 要快;不信到查询分析里面去试!!select a.id_my from a left outer join b on a.id_my = b.id_my left outer join c on a.id_my = c.id_my left outer join d on a.id_my = d.id_my where (b.n1 is null) and (c.n1 is null) and (d.n1 is null)select a.id_my from a left outer join b on a.id_my = b.id_my left outer join c on a.id_my = c.id_my left outer join d on a.id_my = d.id_my where (b.id_my is null) and (c.id_my is null) and (d.id_my is null)
如果二個指定的運算式相同則傳回空值。語法
NULLIF ( expression , expression )
如果在b表,c表,d表中,n1的值永远大于0,那么用以下语句可以实现:
返回a表中有,而b表,c表,d表中都没有的id_my数值。select a.id_my
from a left outer join b on a.id_my = b.id_my
left outer join c on a.id_my = c.id_my
left outer join d on a.id_my = d.id_my
where isnull(b.n1,0)=0 and isnull(c.n1,0)=0 and isnull(d.n1,0)=0请问:还有没有比此更好,执行更快的SQL语句?
where条件似乎是多此一举请问不用where那该如何写呢?难道要用not in 或 exit吗?to :chinaandys(风流泪,雨含笑)
多此一举,用 is not null/is null 就可以判断是否为空!是不是要改成如下的方式:
select a.id_my
from a left outer join b on a.id_my = b.id_my
left outer join c on a.id_my = c.id_my
left outer join d on a.id_my = d.id_my
where (b.n1 is null) and (c.n1 is null) and (d.n1 is null)
请各位高手指点!
先谢了
where (b.n1 is null) and (c.n1 is null) and (d.n1 is null)可以的
where条件似乎是多此一举请问不用where那该如何写呢?难道要用not in 或 exit吗?
from a left outer join b on a.id_my = b.id_my
left outer join c on a.id_my = c.id_my
left outer join d on a.id_my = d.id_my
where b.n1 is null and c.n1 is null and d.n1 is null
判断一个字段是否为空,用column is null来测试
from a left outer join b on a.id_my = b.id_my
left outer join c on a.id_my = c.id_my
left outer join d on a.id_my = d.id_my
where (b.n1 is null) and (c.n1 is null) and (d.n1 is null)select a.id_my
from a left outer join b on a.id_my = b.id_my
left outer join c on a.id_my = c.id_my
left outer join d on a.id_my = d.id_my
where (b.id_my is null) and (c.id_my is null) and (d.id_my is null)