mysql选择检查彩票组合

Question

I spent the last night trying different mysql queries to simply check lottery combination using mysql/php but still not able to make it work fine! Google gives many attempts by users, especially here in stackoverflow, nothing found that can help all the way..

I have a table with all previous winner numbers, i simply want to check 5 main numbers and 2 extra numbers to point out any winning happened earlier for the given numbers

table name: win_archive (9 columns) table structure:

 id | main_b1 | main_b2 | main_b3 | main_b4 | main_b5 | extra_b1 | extra_b2 | win_date
 1 .   22 | 10  |  5 | 59 | . 61 . |   1 |  9 . | 2/1/2011
 2 .   43 | 23  |  11 | 76 | . 25 . |   13 |  9 . | 30/3/2015
 3. ...

Tried to use mysql IN clause

SELECT id, 
   main_b1, 
   main_b2, 
   main_b3, 
   main_b4, 
   main_b5, 
   extra_b1, 
   extra_b2, 
   win_date 
   WHERE  main_b1 IN ( 21, 44, 55, 22, 11, 1, 4 ) 

didn't work if there is more than 1 number then i can't check them, also we have the extra balls are different set than main balls, which means there maybe the same number in the combination but- like one in main balls set and one within the extra 2...

expecting to give 5+2 numbers to a php then by this mysql query to have something like:

Case A: Matching 2 main balls: 3 wins found in archive

• date: line was: xx-xx-xx-xx-xx-y-y

• date: line was: xx-xx-xx-xx-xx-y-y

• date: line was: xx-xx-xx-xx-xx-y-y

Case B: Matching 2 main balls and 1 extra balls: 1 win found in archive

• date: line was: xx-xx-xx-xx-xx-y-y

Case C: Matching 3 main balls and 2 extra balls: 1 win found

• date: line was: xx-xx-xx-xx-xx-y-y

Is it really complicated? there are hundreds of sites online can check the lottery ticket combinations, thought the check/query will be all over..

Any hints will be highly appreciated...

Answer 1

This is a sample query that I think will achieve what you want. You just need to change the values in the IN expressions according to the result you want to search for:

SELECT 
   main_b1, main_b2, main_b3, main_b4, main_b5, extra_b1, extra_b2,
   ((main_b1 IN (22, 23, 10, 11, 76)) +
    (main_b2 IN (22, 23, 10, 11, 76)) +
    (main_b3 IN (22, 23, 10, 11, 76)) +
    (main_b4 IN (22, 23, 10, 11, 76)) +
    (main_b5 IN (22, 23, 10, 11, 76))) AS main,
   ((extra_b1 IN (5, 9)) +
    (extra_b2 IN (5, 9))) AS extra,
    win_date
FROM win_archives
HAVING main >= 3 OR main = 2 AND extra > 0
ORDER BY main DESC, extra DESC, win_date DESC

I created a bit more sample data in this SQLFiddle:

INSERT INTO win_archives
    (`id`, `main_b1`, `main_b2`, `main_b3`, `main_b4`, `main_b5`, `extra_b1`, `extra_b2`, `win_date`)
VALUES
    (1, 22, 10, 5, 59, 61, 1, 9, '2011-01-02'),
    (2, 43, 23, 11, 76, 25, 13, 9, '2015-03-30'),
    (3, 22, 10, 5, 76, 61, 1, 4, '2014-06-02'),
    (4, 43, 9, 11, 76, 25, 5, 9, '2012-08-07'),
    (5, 22, 10, 5, 59, 61, 5, 12, '2016-12-02'),
    (6, 22, 23, 11, 76, 10, 5, 6, '2017-07-19'),
    (7, 22, 10, 5, 59, 61, 1, 9, '2018-09-02'),
    (8, 43, 23, 11, 76, 22, 13, 8, '2005-04-11')
;

With this sample data the output is:

main_b1 main_b2 main_b3 main_b4 main_b5 extra_b1 extra_b2 main  extra   win_date
22      23      11      76      10      5        6        5     1       2017-07-19
43      23      11      76      22      13       8        4     0       2005-04-11
43      23      11      76      25      13       9        3     1       2015-03-30
22      10      5       76      61      1        4        3     0       2014-06-02
43      9       11      76      25      5        9        2     2       2012-08-07
22      10      5       59      61      1        9        2     1       2018-09-02
22      10      5       59      61      5        12       2     1       2016-12-02
22      10      5       59      61      1        9        2     1       2011-01-02