我想查询一些数据,其中有三个表中各有一个字段作为查询条件,我想当这三个字段都为空时就不输出,相反则输出,我加了下面的查询条件,可是查询结果为空,不加时能查询到结果
AND (x2.price_newegg != null OR j2.price_360buy != null OR t2.price_taobao != null)
这样限定不行吗?
AND (x2.price_newegg != null OR j2.price_360buy != null OR t2.price_taobao != null)
这样限定不行吗?
AND (x2.price_newegg != null AND j2.price_360buy != null AND t2.price_taobao != null)
即可
三个字段都为空时就不输出
相当于
三个字段都不为空时就输出按这样逻辑去写SQL才正确
AND (x2.price_newegg IS NOT NULL OR j2.price_360buy IS NOT NULL OR t2.price_taobao IS NOT NULL)
{
$sql = "SELECT cid,pid FROM pcnp_category_buynow";
$r = $this->selectRow($sql);
if(!$r)return false;
$cate = new category($r);
$a = $cate->getChilds($arr['cid']);
if( count($a) == 0 ){
$where = 'WHERE b1.cid = '.$arr['cid'];
}else{
$where = 'WHERE b1.cid in ('.implode(',',$a).')';
}
if(!empty($arr['keyword'])) {
$where .=" AND b1.name_buynow LIKE '%{$arr['keyword']}%' ";
}
$where .=" AND (x2.price_newegg IS NOT NULL OR j2.price_360buy IS NOT NULL OR t2.price_taobao IS NOT NULL) ";
$offset=($arr['p']-1)*20;
$where .= ' LIMIT '.$offset.',20';
$sql = 'select b1.prid_buynow as prid_buynow,
b1.name_buynow as name_buynow,
b2.price_buynow as price_buynow,
j1.prid_360buy as prid_360buy,
j2.price_360buy as price_360buy,
x1.prid_newegg as prid_newegg,
x2.price_newegg as price_newegg,
t1.prid_taobao as prid_taobao,
t2.price_taobao as price_taobao
from pcnp_buynow as b1
left join pcnp_price_buynow as b2 on b1.prid_buynow=b2.prid_buynow
left join pcnp_360buy as j1 on j1.prid_buynow=b1.prid_buynow
left join pcnp_price_360buy as j2 on j2.prid_360buy=j1.prid_360buy
left join pcnp_newegg as x1 on x1.prid_buynow=b1.prid_buynow
left join pcnp_price_newegg as x2 on x2.prid_newegg=x1.prid_newegg
left join pcnp_taobao as t1 on t1.prid_buynow=b1.prid_buynow
left join pcnp_price_taobao as t2 on t2.prid_taobao=t1.prid_taobao
'.$where;
// echo $sql;
// exit;
return $this->select($sql);
}
列中有一列含有null,该列就会从索引中排除。
也就是说如果某列存在空值,即使对该列建索引也不会提高性能。