使用SQL搜索bingo

huangapple go评论171阅读模式
英文:

Searching for bingo with SQL

问题

我有一个包含50000个Bingo牌的MySQL数据库表格。

表格结构如下:

plade_line_id  plade_id  number1  number2  number3  number4  number5
1              1         62       82       52       85       73
2              1         26       24       67       81       99
3              1         5        69       17       52       84
4              2         62       82       52       85       73
5              2         26       24       67       81       99
6              2         5        69       17       52       84

我的想法是将抽取的数字添加到一个数组中。然后以某种方式获取当满足以下条件时的plade_id(每个条件都需要一个语句):

  1. 相同牌上的1行
  2. 相同牌上的2行
  3. 相同牌上的3行

这个牌是5x3的,只有在上述条件下才能获胜。在一张牌上,每个数字只能出现一次。数字范围在1-99之间。

我只需要2或3个plade_id,所以结果应该类似于:

plade_id
6267
21347
43295

我不太确定如何处理这个问题,所以任何帮助将不胜感激。
谢谢!

英文:

I have a mysql database with a table with 50000 bingo plates.

The table structure:

plade_line_id  plade_id  number1  number2  number3  number4  number5
1              1         62       82       52       85       73
2              1         26       24       67       81       99
3              1         5        69       17       52       84
4              2         62       82       52       85       73
5              2         26       24       67       81       99
6              2         5        69       17       52       84

My idea is to add numbers to an array as they are drawn.
And then somehow get the plade_id when there is(A statement for each?):

  1. 1 Line on same plate
  2. 2 Lines on same plate
  3. 3 Lines on same plate

The plate is 5x3 and you can only win on the above conditions.
On one plate, a single number can only be there one time.
And the numbers are between 1-99.

I only need like 2 or 3 plade_id's, so the result should be something like:

plade_id
6267    
21347
43295

I am not really sure how to go about this, so any help will be appreciated.
Thanks!

答案1

得分: 1

一个天真的方法是简单地堆叠IN子句:
在不修改表的情况下,我们可以检查number1、number2等是否都在所抽取的数字中。

;with Plades as
(
select 1 plade_line_id, 1 plade_id, 62 number1, 82 number2, 52 number3, 85 number4, 1 number5 	 union
select 2, 1, 26, 24, 67, 81, 2 	 union
select 3, 1, 5, 69, 17, 52, 3 	 union
select 4, 2, 62, 82, 52, 85, 4 	 union
select 5, 2, 26, 24, 67, 81, 5 	 union
select 6, 2, 5, 69, 17, 52, 6 	 
),
Drawn as (
Select 62 Number	union
Select 82 	 union
Select 52 	 union
Select 85 	 union
Select 4 	 union
Select 81 	 
)
select * from Plades
WHERE 
	  number1 IN (SELECT Number FROM Drawn)
  AND number2 IN (SELECT Number FROM Drawn)
  AND number3 IN (SELECT Number FROM Drawn)
  AND number4 IN (SELECT Number FROM Drawn)
  AND number5 IN (SELECT Number FROM Drawn)

如果允许修改表格,也许在有序的{number1到number6}上创建哈希可能会有用。

但是最好的方法是使用一个较小的集合:当前正在游戏中的宾果卡。

原因很简单,第一个获胜者预计在第42-43轮出现。
每次抽取一个数字,您都会在以下范围内进行搜索:
50000张宾果牌 x 每张宾果牌上的行数

现在,查找1行、2行等牌已经变得微不足道。
例如,对于2行:

select plade_id, COUNT(*) as nbLines from Plades
WHERE 
	  number1 IN (SELECT Number FROM Drawned)
  AND number2 IN (SELECT Number FROM Drawned)
  AND number3 IN (SELECT Number FROM Drawned)
  AND number4 IN (SELECT Number FROM Drawned)
  AND number5 IN (SELECT Number FROM Drawned)
GROUP BY plade_id
HAVING COUNT(*) = 2
英文:

A Naïve approach, will be to simply stack IN clause:
Without modification of the table, we can check if the number1, number2 etc are all in the numbers drawn.

;with Plades as
(
select 1 plade_line_id, 1 plade_id, 62 number1, 82 number2, 52 number3, 85 number4, 1 number5 	 union
select 2, 1, 26, 24, 67, 81, 2 	 union
select 3, 1, 5, 69, 17, 52, 3 	 union
select 4, 2, 62, 82, 52, 85, 4 	 union
select 5, 2, 26, 24, 67, 81, 5 	 union
select 6, 2, 5, 69, 17, 52, 6 	 
),
Drawn as (
Select 62 Number	union
Select 82 	 union
Select 52 	 union
Select 85 	 union
Select 4 	 union
Select 81 	 
)
select * from Plades
WHERE 
	  number1 IN (SELECT Number FROM Drawn)
  AND number2 IN (SELECT Number FROM Drawn)
  AND number3 IN (SELECT Number FROM Drawn)
  AND number4 IN (SELECT Number FROM Drawn)
  AND number5 IN (SELECT Number FROM Drawn)

If you allow modification of your table, perhaps a hash on the ordered { number1 to number6} could be usefull.

But the best thing will be to work a smaller set: the bingo card that are currently beeing play in the room.

The reason is simple, the first winner is expected at turn 42-43.
Each number drawn, you will search into :
50000 bingo plates x Number of lines on a bingo plates

Finding the 1 line, 2 lines.. plades is now trivial.
EG for 2 lines :

select plade_id, COUNT(*) as nbLines from Plades
WHERE 
	  number1 IN (SELECT Number FROM Drawned)
  AND number2 IN (SELECT Number FROM Drawned)
  AND number3 IN (SELECT Number FROM Drawned)
  AND number4 IN (SELECT Number FROM Drawned)
  AND number5 IN (SELECT Number FROM Drawned)
GROUP BY plade_id
HAVING COUNT(*) = 2

huangapple
  • 本文由 发表于 2020年4月7日 06:02:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/61069666.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定