求助！紧急啊~~查询字段为空的时候where条件略过，不为空时则判断该字段，怎么写！！！ [问

[weixin_38048234]

比如A表有id,name,age三个字段
当id字段查询条件为空时，sql语句为where name='x' and age='y';
当name字段查询条件为空时，sql语句为where id='x' and age='y‘;
当age字段输入的查询条件为空时，sql语句为where id='x' and name='y';
求助sql怎么写’

[weixin_38048234]

各位大神大神。

[weixin_38048234]

[吃肉的懒羊羊]

select id,age,name from tab where id is null and age = 'x' and name = 'y' union all select id,age,name from tab where name is null and id = 'x' and name = 'y' union all select id,age,name from tab where age is null and id = 'x' and name = 'y'

[卖水果的net]

动态拼接SQL。 楼主可以借鉴一下 mybatis 。

[zcs_zzh]

SELECT * FROM a WHERE (id IS NULL AND NAME = 'x' AND age = 'y') OR (NAME IS NULL AND id = 'x' AND age = 'y') OR (age IS NULL AND id = 'x' AND NAME = 'y')

[静坐墙角待红杏]

引用 4 楼 wbhkkk 的回复:

select id,age,name from tab where id is null and age = 'x' and name = 'y' union all select id,age,name from tab where name is null and id = 'x' and name = 'y' union all select id,age,name from tab where age is null and id = 'x' and name = 'y'

瞎搞

[静坐墙角待红杏]

用mybatis这些都有自己判断的

[weixin_38048234]

引用 7 楼 zcs_zzh 的回复:

SELECT * FROM a WHERE (id IS NULL AND NAME = 'x' AND age = 'y') OR (NAME IS NULL AND id = 'x' AND age = 'y') OR (age IS NULL AND id = 'x' AND NAME = 'y')

这个不行，如果某个字段为空那么查询条件里就不要这个字段，而不是用null来筛选

[weixin_38048234]

引用 3 楼 wmxcn2000 的回复:

动态拼接SQL。 楼主可以借鉴一下 mybatis 。

版主详细一点啊- - 。

[qq_30421439]

select * from a
where decode(id,null,name,'x')='x' and decode(id,null,age,'y')='y'
  and decode(name,null,id,'x')='x' and decode(name,null,age,'y')='y'
  and decode(age,null,id,'x')='x' and decode(age,null,name,'y')='y';

[kiss阳光]

使用or 或union

[tingting627177510]

<!-- ElementID:selectCycleByRepId,Generated=false --> <select id="selectCycleByRepId" parameterType="map" resultMap="T_STAT_CONFIG_ASSESSMENTResult"> select <include refid="column_sql"/> from T_STAT_CONFIG_ASSESSMENT where 1=1 <if test="conassid != null">and T_STAT_CONFIG_ASSESSMENT.conassid = #{conassid}</if> <if test="repId != null">and T_STAT_CONFIG_ASSESSMENT.rep_id = #{repId}</if> <if test="cycleType != null">and T_STAT_CONFIG_ASSESSMENT.cycle_type = #{cycleType}</if> </select> 可以用这种mybatis的方式

[慕容忧洋]

decode和when case可以解决

[慕容忧洋]

10楼已经写出来了，可以看下逻辑