sql - Check pairs of lottery numbers from archive
问题描述
I'm trying to find best possible solution to check pairs concerning to my national lottery. They draw six numbers. I have a database with all the previous drawings. The question is what is the most efficient table structure and the way of checking lottery number pairs'?
This is how my table looks:
year (float)
date (nvarchar)
draw (float)
no1 (smallint)
no2 (smallint)
no3 (smallint)
no4 (smallint)
no5 (smallint)
no6 (smallint)
I use this query to find a pair of numbers:
SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '36' IN (no1,no2,no3,no4,no5,no6)
I got the result 10 (the pair was draw out 10 times).
If I want to check the next pair, for example 1 and 37, I need to use the query again but with other numbers
SELECT COUNT(*)
FROM archive
WHERE '1' IN (no1,no2,no3,no4,no5,no6) AND '37' IN (no1,no2,no3,no4,no5,no6)
I got the result 13 (the pair was draw out 13 times)
Is there a SQL query (or some better solution) that can automatically check pairs in the number range from 1 to 90; example:
Pair 1 and 2
Pair 1 and 3
Pair 1 and 4
.
.
all the way up to pair 89 and 90?
解决方案
在这里稍微阅读一下,但 Tally 似乎是这里的最佳选择:
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP (90) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2)
SELECT COUNT(*) AS Draws,
T1.I AS Number1,
T2.I AS Number2
FROM dbo.Archive S
CROSS JOIN Tally T1
JOIN Tally T2 ON T1.I < T2.I
WHERE T1.I IN (no1,no2,no3,no4,no5,no6)
AND T2.I IN (no1,no2,no3,no4,no5,no6)
GROUP BY T1.I, T2.I;
推荐阅读
- ios - ios中自定义相机的画中画效果
- html - Angular 5在表格中呈现没有父模板的模板内容
- aurelia - 在打字稿中使用纯 javascript 隐藏引导模式
- tabs - Dynamics crm - 根据选项卡的 xrm 事件
- jquery - addClass to li smoothscroll this.hash
- angular - 监听网络变化 - Ionic 2
- angular - PrimeNg 表,问题编辑复选框
- javascript - 如何从另一个组件定位组件内的引用?
- javascript - Bootstrap 4 ajax 加载模式
- angular - 在 IE 的小键盘上按下 ag-grid 中的带句点符号