For help! Urgent ~ ~ query fields blank skip the where condition, when no is empty, concludes that t-CodePudding

Such as A table with id, name, age three fields
When the id field is empty query condition, the SQL statement for the where name='x' and age='y'
When the name field is empty query condition, the SQL statement for the where id='x' and age='y'
When the age field input query condition is empty, the SQL statement for the where id='x' and name='y'.
How do you write for SQL '

CodePudding user response:

Everyone a great god the great god,

CodePudding user response:

Dynamic splicing SQL,

The original poster can draw lessons from the mybatis,

CodePudding user response:

Select id, age, name the from the TAB where id is null and age='x' and name='y'
Union all
Select id, age, name the from the TAB where the name is null and id='x' and name='y'
Union all
Select id, age, name the from the TAB where age is null and id='x' and name='y'

CodePudding user response:

reference 4 floor WBHKKK response:

select id, age, name the from the TAB where id is null and age='x' and name='y'
Union all
Select id, age, name the from the TAB where the name is null and id='x' and name='y'
Union all
Select id, age, name the from the TAB where age is null and id='x' and name='y'

fiddle

CodePudding user response:

Use mybatis these all have their own judgment

CodePudding user response:

SELECT *
FROM a
The 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')

CodePudding user response:

refer to 7th floor zcs_zzh response:

SELECT *
FROM a
The 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')

this not line, if a field IS empty, do not have to this field in the query conditions, rather than use NULL to screening

CodePudding user response:

reference wmxcn2000 reply: 3/f

dynamic splicing SQL,

The original poster can draw lessons from the mybatis,

webmaster detail - -,

CodePudding user response:

 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'

CodePudding user response:

Use the or or the union

CodePudding user response:

Can use this way of mybatis

CodePudding user response:

Decode and the when the case can be solved

CodePudding user response:

10th floor has written out, logic may have a look