选择值的子组

Question

给定两个表格"平局"(id, date)和"结果"(id, draw_id, number)，其中每个抽奖都有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

弗雷德的好收获，这将返回正确的结果：

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;

弗雷德的答案会起作用，但 tbh 它很脆弱，因为它只有在您正在寻找 3 个结果时才有效。我的回答更加慎重，但是您必须以编程方式构造查询，以确定需要多少连接才能获得所需的结果，例如：

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

Answer 2

根据您的问题，您不需要加入或任何东西...

select Draw from 
Results r
where r.Number in (15,22,45);

Answer 3

这是另一种可能性（没有重复）

select * from Draws d where exists (select * from Results r where r.Draw=d.Id and Number in (1, 2, 3...) );

Answer 4

你只需要使用 JOIN 来获取这个结果

SELECT * FROM Draw, Results 
WHERE Draw.ID = Results.DrawID 
AND Results.Number IN (15,22,45);

结果：

您将获得结果 ID（2， 3， 5， 7， 8）

Answer 5

SELECT Draw FROM Results
Where Number IN (15,22,45)
Group By Draw
Having Count(*) > 2

只会给你所有 3 的抽奖

SELECT d.Date, r.Draw FROM #result r Join #Draws d ON d.id = r.Draw
Where Number IN (15,22,45)
Group By d.Date, r.Draw
Having Count(*) > 2

如果您希望结果中的日期。