代码之家 › 专栏 › 技术社区 › Chris Athanasiadis

选择值的子组

in-clause conditional join mysql

1

Chris Athanasiadis · 技术社区 · 9 年前

给定两张表“Draws” (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中)

5 回复 | 直到 9 年前

1

Mike Purcell 9 年前

弗雷德捕捉得很好,这将返回正确的结果:

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;

弗雷德的答案会奏效,但它很脆弱,因为只有当你寻找3个结果时,它才会奏效。我的回答更加深思熟虑,但是您必须以编程方式构造查询,以确定需要多少联接才能获得所需的结果,例如:

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

2

0

FirebladeDan 9 年前

根据你的问题,你不需要加入或任何东西。。。

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

3

0

marian0 9 年前

这是另一种可能性(没有重复)

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

4

0

devpro 9 年前

您只需使用JOIN即可获得此结果

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

结果:

您将获得结果ID (2, 3, 5, 7, 8)

5

0

Fred 9 年前

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

如果你想要结果中的日期。