I'm don't have much experience in writing SQL so maybe this is a fairly trivial question but right now I have a SQL query where need to do a 'like' on multiple fields, at present I do:
select *
from tableX
where col1 like '10%'
or col2 like '10%'
or col3 like '10%'
or col4 like '10%'
or col5 like '10%'
or col6 like '10%'
Is there di开发者_如何学Pythonfferent, that is simpler or better, way of writing the SQL?
Thanks
select * from tableX where col1 like '10%'
UNION
select * from tableX where col2 like '10%'
UNION
select * from tableX where col3 like '10%'
...
If you were comparing one col against multiple values, then there are other options, such as
SELECT *
FROM
tableX t1
JOIN
tableFilter TF ON t1.col LIKE TF.FilterValue
By better do you mean faster?
I expect the following could be faster but sql might already optimize it:
select *
from tableX
where substring(col1,0,2) = '10'
or substring(col2,0,2) = '10'
or substring(col3,0,2) = '10'
or substring(col4,0,2) = '10'
or substring(col5,0,2) = '10'
or substring(col6,0,2) = '10'
a lot depends on what you are doing, if you are doing this a lot and each column starts with a two character code then you might want to split that value into it's own column.
精彩评论