mysql - 选择值子组

Question

给定两个表 'Draws' (id, date) AND 'Results' (id, draw_id, number) 其中每个 Draw 有 x 个结果，我怎样才能得到开奖结果中有数字子组？

Draws
-----------------
Id | Date
-----------------
1  | 2015-01-20
2  | 2015-01-22
-----------------

Results
--------------------
Id  | Draw | Number
--------------------
1   | 1    | 13
2   | 1    | 15
3   | 1    | 22
4   | 1    | 36
5   | 1    | 45
6   | 2    | 11
7   | 2    | 15
8   | 2    | 22

如何获得结果中包含 (15,22,45) 的抽奖？ (在示例中绘​​制 #1)

Answer 1

Fred 抓得好，这将返回正确的结果:

SELECT
    d.*
FROM
    Draw AS d
INNER JOIN Results AS r1 ON r1.Draw = d.ID AND r1.Number = 15
INNER JOIN Results AS r2 ON r2.Draw = d.ID AND r2.Number = 22
INNER JOIN Results AS r3 ON r3.Draw = d.ID AND r3.Number = 45
GROUP BY
    d.id;

Fred 的答案会起作用，但它很脆弱，因为它只有在您寻找 3 个结果时才起作用。我的回答是经过深思熟虑的，但是您必须以编程方式构建查询以确定需要多少连接才能获得所需的结果，例如:

15 -> 1 join
15, 22 -> 2 joins
15, 22, 45 -> 3 joins
....