Best way to store list of numbers and to retrieve them
What is the best way to store a list of random numbers (like lotto/bingo numbers) and retrieve them? I'd like to store on a Datab开发者_如何学运维ase a number of rows, where each row contains 5-10 numbers ranging from 0 to 90. I will store a big number of those rows. What I'd like to be able is to retrieve the rows that have at least X number in common to a newly generated row.
Example:
[3,4,33,67,85,99]
[55,56,77,89,98,99]
[3,4,23,47,85,91]
Those are on the DB
I will generate this:
[1,2,11,45,47,88]
and now I want to get the rows that have at least 1 number in common with this one.
The easiest (and dumbest?) way is to make 6 select and check for similar results.
I thought to store numbers with a large binary string like
000000000000000000000100000000010010110000000000000000000000000
with 99 numbers where each number represent a number from 1 to 99, so if I have 1 at the 44th position, it means that I have 44 on that row. This method is probably shifting the difficult tasks to the Db but it's again not very smart.
Any suggestion?
You should create a table like so:
TicketId Number
1 3
1 4
1 33
1 67
1 85
1 99
2 55
2 56
2 77
etc...
Then your query, at least for X = 1, becomes:
SELECT DISTINCT TicketId FROM Ticket WHERE Number IN (1, 2, 11, 45, 47, 88)
The advantage of this is that you can use an index instead of a full table scan.
For X greater than one, you could do the following:
SELECT TicketId, COUNT(*) AS cnt
FROM Ticket WHERE Number IN (1, 2, 11, 45, 47, 88)
GROUP BY TicketId
HAVING COUNT(*) >= 3
Again this will be able to use the index.
精彩评论