This SQL recipe is about searching for combinations. Let's say you have a table of winning lottery numbers. An example of such a table is given below.
root@localhost:test>
SELECT *
FROM `winners`
ORDER BY `drawingDate` LIMIT 10;
+-------------+--------+--------+--------+--------+--------+-----+
| drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+-------------+--------+--------+--------+--------+--------+-----+
| 1997-11-05 | 2 | 19 | 35 | 24 | 28 | 26 |
| 1997-11-08 | 40 | 17 | 21 | 7 | 49 | 37 |
| 1997-11-12 | 41 | 14 | 13 | 29 | 16 | 1 |
| 1997-11-15 | 32 | 7 | 45 | 21 | 26 | 8 |
| 1997-11-19 | 19 | 10 | 48 | 3 | 21 | 22 |
| 1997-11-22 | 22 | 31 | 3 | 7 | 14 | 2 |
| 1997-11-26 | 15 | 46 | 34 | 23 | 40 | 35 |
| 1997-11-29 | 11 | 27 | 13 | 2 | 31 | 23 |
| 1997-12-03 | 18 | 9 | 14 | 47 | 42 | 32 |
| 1997-12-06 | 15 | 26 | 28 | 8 | 43 | 36 |
+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.00 sec)
I want to find how many matches a given combination of numbers has in this table. But let's start by searching for a single number, using the following query:
root@localhost:test>
SELECT *
FROM `winners`
WHERE `white1`=42 OR
`white2`=42 OR
`white3`=42 OR
`white4`=42 OR
`white5`=42 OR
`red`=42
ORDER BY `drawingDate` LIMIT 10;
+-------------+--------+--------+--------+--------+--------+-----+
| drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+-------------+--------+--------+--------+--------+--------+-----+
| 1997-12-03 | 18 | 9 | 14 | 47 | 42 | 32 |
| 1998-03-07 | 32 | 36 | 42 | 30 | 21 | 33 |
| 1998-03-14 | 15 | 47 | 35 | 34 | 42 | 15 |
| 1998-03-25 | 42 | 44 | 35 | 23 | 5 | 29 |
| 1998-06-06 | 24 | 42 | 26 | 16 | 28 | 24 |
| 1998-07-04 | 39 | 25 | 38 | 15 | 30 | 42 |
| 1998-08-05 | 42 | 35 | 19 | 12 | 3 | 21 |
| 1998-08-08 | 45 | 42 | 5 | 9 | 33 | 28 |
| 1998-08-12 | 10 | 42 | 33 | 2 | 45 | 2 |
| 1998-08-26 | 6 | 32 | 46 | 23 | 42 | 8 |
+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.00 sec)
If we want to search for a combination of numbers, let's say (2,3,5,7,11) and a "powerball" of 42, then something like the following would work.
root@localhost:test>
SELECT *
FROM `winners`
WHERE
`white1` IN (2,3,5,7,11) OR
`white2` IN (2,3,5,7,11) OR
`white3` IN (2,3,5,7,11) OR
`white4` IN (2,3,5,7,11) OR
`white5` IN (2,3,5,7,11) OR
`red`=42
ORDER BY `drawingDate` LIMIT 10;
+-------------+--------+--------+--------+--------+--------+-----+
| drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+-------------+--------+--------+--------+--------+--------+-----+
| 1997-11-05 | 2 | 19 | 35 | 24 | 28 | 26 |
| 1997-11-08 | 40 | 17 | 21 | 7 | 49 | 37 |
| 1997-11-15 | 32 | 7 | 45 | 21 | 26 | 8 |
| 1997-11-19 | 19 | 10 | 48 | 3 | 21 | 22 |
| 1997-11-22 | 22 | 31 | 3 | 7 | 14 | 2 |
| 1997-11-29 | 11 | 27 | 13 | 2 | 31 | 23 |
| 1997-12-13 | 44 | 41 | 46 | 35 | 5 | 27 |
| 1997-12-17 | 3 | 5 | 45 | 18 | 13 | 20 |
| 1997-12-20 | 23 | 11 | 12 | 17 | 10 | 7 |
| 1997-12-24 | 28 | 16 | 35 | 34 | 3 | 14 |
+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.00 sec)
To find the number of matches for each drawing, we will modify the query to use the 'UNION' operator. The above query is equivalent to the following:
root@localhost:test>
SELECT * FROM `winners` WHERE `white1` IN (2,3,5,7,11)
UNION
SELECT * FROM `winners` WHERE `white2` IN (2,3,5,7,11)
UNION
SELECT * FROM `winners` WHERE `white3` IN (2,3,5,7,11)
UNION
SELECT * FROM `winners` WHERE `white4` IN (2,3,5,7,11)
UNION
SELECT * FROM `winners` WHERE `white5` IN (2,3,5,7,11)
UNION
SELECT * FROM `winners` WHERE `red`=42
ORDER BY `drawingDate` LIMIT 10;
+-------------+--------+--------+--------+--------+--------+-----+
| drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+-------------+--------+--------+--------+--------+--------+-----+
| 1997-11-05 | 2 | 19 | 35 | 24 | 28 | 26 |
| 1997-11-08 | 40 | 17 | 21 | 7 | 49 | 37 |
| 1997-11-15 | 32 | 7 | 45 | 21 | 26 | 8 |
| 1997-11-19 | 19 | 10 | 48 | 3 | 21 | 22 |
| 1997-11-22 | 22 | 31 | 3 | 7 | 14 | 2 |
| 1997-11-29 | 11 | 27 | 13 | 2 | 31 | 23 |
| 1997-12-13 | 44 | 41 | 46 | 35 | 5 | 27 |
| 1997-12-17 | 3 | 5 | 45 | 18 | 13 | 20 |
| 1997-12-20 | 23 | 11 | 12 | 17 | 10 | 7 |
| 1997-12-24 | 28 | 16 | 35 | 34 | 3 | 14 |
+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.00 sec)
Next comes the magic, where we will use 'UNION ALL' instead of 'UNION' in order to display duplicates, and then wrap the entire query with an outer SELECT statement that counts groups by drawingDate.
root@localhost:test>
SELECT COUNT(*), `a`.*
FROM
(SELECT * FROM `winners` WHERE `white1` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white2` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white3` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white4` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white5` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `red`=42) AS `a`
GROUP BY `a`.`drawingDate` ORDER BY `a`.`drawingDate` LIMIT 10;
+----------+-------------+--------+--------+--------+--------+--------+-----+
| COUNT(*) | drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+----------+-------------+--------+--------+--------+--------+--------+-----+
| 1 | 1997-11-05 | 2 | 19 | 35 | 24 | 28 | 26 |
| 1 | 1997-11-08 | 40 | 17 | 21 | 7 | 49 | 37 |
| 1 | 1997-11-15 | 32 | 7 | 45 | 21 | 26 | 8 |
| 1 | 1997-11-19 | 19 | 10 | 48 | 3 | 21 | 22 |
| 2 | 1997-11-22 | 22 | 31 | 3 | 7 | 14 | 2 |
| 2 | 1997-11-29 | 11 | 27 | 13 | 2 | 31 | 23 |
| 1 | 1997-12-13 | 44 | 41 | 46 | 35 | 5 | 27 |
| 2 | 1997-12-17 | 3 | 5 | 45 | 18 | 13 | 20 |
| 1 | 1997-12-20 | 23 | 11 | 12 | 17 | 10 | 7 |
| 1 | 1997-12-24 | 28 | 16 | 35 | 34 | 3 | 14 |
+----------+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.06 sec)
Finally, we can add the count to the order-by clause.
root@localhost:test>
SELECT COUNT(*) AS `count`, `a`.*
FROM
(SELECT * FROM `winners` WHERE `white1` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white2` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white3` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white4` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `white5` IN (2,3,5,7,11)
UNION ALL
SELECT * FROM `winners` WHERE `red`=42) AS `a`
GROUP BY `a`.`drawingDate` ORDER BY `count` DESC, `a`.`drawingDate` LIMIT 10;
+-------+-------------+--------+--------+--------+--------+--------+-----+
| count | drawingDate | white1 | white2 | white3 | white4 | white5 | red |
+-------+-------------+--------+--------+--------+--------+--------+-----+
| 3 | 1998-09-26 | 25 | 24 | 5 | 3 | 2 | 8 |
| 3 | 2002-06-19 | 4 | 7 | 3 | 11 | 1 | 23 |
| 3 | 2003-02-01 | 11 | 2 | 39 | 21 | 16 | 42 |
| 3 | 2003-02-05 | 2 | 7 | 48 | 9 | 5 | 32 |
| 3 | 2004-12-22 | 7 | 11 | 31 | 24 | 2 | 18 |
| 3 | 2005-05-21 | 7 | 30 | 5 | 10 | 2 | 23 |
| 2 | 1997-11-22 | 22 | 31 | 3 | 7 | 14 | 2 |
| 2 | 1997-11-29 | 11 | 27 | 13 | 2 | 31 | 23 |
| 2 | 1997-12-17 | 3 | 5 | 45 | 18 | 13 | 20 |
| 2 | 1998-01-31 | 13 | 36 | 48 | 7 | 11 | 18 |
+-------+-------------+--------+--------+--------+--------+--------+-----+
10 rows in set (0.01 sec)
No comments:
Post a Comment