时间戳

首页 > 解决方案 > MS Access 如何使用 Query 或 VBA 获取 4 位序列的所有组合的计数

ms-access-2016 - MS Access 如何使用 Query 或 VBA 获取 4 位序列的所有组合的计数

问题描述

我正在尝试使用查询来计算表中单行中所有可能的 4 位数字组合的计数？我使用了一个很好的 3 位序列查询。SQL是这样的 

SELECT
    [parent].Results
  , COUNT([parent].Results) AS [Total Results]
FROM
    (SELECT DISTINCT Results FROM [Pick 3]
    ) parent
  , [Pick 3] child
WHERE
       child.Results = LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1)
    OR child.Results = LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1)
    OR child.Results = Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) & RIGHT(parent.Results, 1)
    OR child.Results = Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) & LEFT(parent.Results, 1)
    OR child.Results = RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1)
    OR child.Results = RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1)
GROUP BY
    parent.Results

我尝试将此方法与 4 位序列一起使用，这就是我想出的。

SELECT
    [parent].Results
  , COUNT([parent].Results) AS [Total Results]
FROM
    (SELECT DISTINCT Results FROM [Pick 4]
    ) parent
  , [Pick 4] child
WHERE
       child.Results = LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) & RIGHT(parent.Results, 1) 1234
    OR child.Results = LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) 1243
    OR child.Results = LEFT(parent.Results, 1) & Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) 1324
    OR child.Results = LEFT(parent.Results, 1) & Mid(parent.Results, 3, 1) & RIGHT(parent.Results, 1) & Mid (parent.Results, 2, 1) 1342
    OR child.Results = LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) 1423
    OR child.Results = LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) 1432
    OR child.Results = Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) & Mid (parent.Results, 3, 1) & RIGHT(parent.Results, 1) 2134
    OR child.Results = Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) 2143
    OR child.Results = Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) 2314
    OR child.Results = Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) & RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) 2341
    OR child.Results = Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 3, 1) 2413
    OR child.Results = Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) 2431
    OR child.Results = Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) & Mid (parent.Results, 2, 1) & RIGHT(parent.Results, 1) 3124
    OR child.Results = Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) 3142
    OR child.Results = Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) & RIGHT(parent.Results, 1) 3214
    OR child.Results = Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) & RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) 3241
    OR child.Results = Mid(parent.Results, 3, 1) & RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) 3412
    OR child.Results = Mid(parent.Results, 3, 1) & RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) 3421
    OR child.Results = RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) 4123
    OR child.Results = RIGHT(parent.Results, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) 4132
    OR child.Results = RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 3, 1) 4213
    OR child.Results = RIGHT(parent.Results, 1) & Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) 4231
    OR child.Results = RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) & LEFT(parent.Results, 1) & Mid(parent.Results, 2, 1) 4312
    OR child.Results = RIGHT(parent.Results, 1) & Mid(parent.Results, 3, 1) & Mid(parent.Results, 2, 1) & LEFT(parent.Results, 1) 4321
GROUP BY
    parent.Results
;

这很接近但出了点问题我没有得到表中每个 4 位数字的所有组合。

标签： ms-access-2016

解决方案

SQL DISTINCT 关键字将在一行中显示每个唯一条目。group by 将允许计数向您显示其中有多少。

SELECT DISTINCT Res, COUNT(Res) as Hits
FROM ResultsTable
GROUP BY Res

这能满足你的需要吗？

推荐阅读