I have a table with 6 digit numbers that can range from 0-9 and I would match that against a number in 6 categories
- first number match
- first two number match
- first three number match
- first four number match
- first five number match
- all numbers match
But only the highest category should be selected. An example
Number: 123456
- If one has the number [123]756 then this would fall into category first three number match
- On number 023456 then this would be no match
I created a fiddle for it https://www.db-fiddle.com/f/TZCrFPnJpkw4fyxA5Q6mR/1
What would be an efficient method? The brute force solution would be a double loop I suppose starting with 6 matches, 5 matches, ...
CodePudding user response：
You can do:
select *
from (
select 6 as score, b.* from bids b where ticketNumber like '123456%'
union all select 5, b.* from bids b where ticketNumber like '12345%'
union all select 4, b.* from bids b where ticketNumber like '1234%'
union all select 3, b.* from bids b where ticketNumber like '123%'
union all select 2, b.* from bids b where ticketNumber like '12%'
union all select 1, b.* from bids b where ticketNumber like '1%'
) x
order by score desc
limit 1
Result:
score id roundId address ticketNumber
------ --- -------- -------- ------------
6 1 1 12345 123456
See example at DB Fiddle.
Alternatively you can use a recursive CTE, but that's not available in MySQL 5.7 (as your fiddle implies).
CodePudding user response：
SELECT @number tested_number, 7 - LENGTH(nums.num) common_digits, bids.*
FROM bids
JOIN (SELECT 1 num UNION
SELECT 10 UNION
SELECT 100 UNION
SELECT 1000 UNION
SELECT 10000 UNION
SELECT 100000) nums
WHERE @number DIV nums.num = bids.ticketNumber DIV nums.num
ORDER BY nums.num LIMIT 1;