时间戳

首页 > 解决方案 > 访问查询以分组并选择每组两个随机行

sql - 访问查询以分组并选择每组两个随机行

问题描述

目标是尝试获取每个处理程序 ID 的两个随机样本案例。

该项目的数据如下。

ID  Complaint Handler   Handler ID  Reference   Outcome Handler Notes
1   John Doe    h384    R38423  Uphold  Not Applicable
2   Ryan Jones  h632    R38482  Uphold  Not Applicable
3   Chris Smith h238    R84823  Defend  Not Applicable
4   Emily Surry h634    R48384  Reject  Not Applicable
5   Elle Smith  h123    R48823  Uphold  Not Applicable
6   Jane Doe    h324    R48282  Uphold  Not Applicable
7   Joe Bloggs  h538    R83322  Reject  Not Applicable
8   Ryan Jones  h632    R38299  Defend  Not Applicable
9   Chris Smith h238    R83482  Reject  Not Applicable
10  Chris Smith h238    R91823  Reject  Not Applicable
11  Joe Bloggs  h538    R18291  Uphold  Not Applicable

我使用以下查询来选择所有唯一的案例处理程序引用。

SELECT Cases.[Handler ID]
FROM Cases
GROUP BY Cases.[Handler ID];

然后我需要遍历所有这些唯一引用并执行以下查询

SELECT TOP 2 * 
FROM Cases
WHERE Cases.[Handler ID] = 'XXXXXX'
ORDER BY Rnd(ID)

结果的一个例子是

ID  Complaint Handler   Handler ID  Reference   Outcome Handler Notes
1   John Doe    h384    R38423  Uphold  Not Applicable
2   Ryan Jones  h632    R38482  Uphold  Not Applicable
3   Chris Smith h238    R84823  Defend  Not Applicable
4   Emily Surry h634    R48384  Reject  Not Applicable
5   Elle Smith  h123    R48823  Uphold  Not Applicable
6   Jane Doe    h324    R48282  Uphold  Not Applicable
7   Joe Bloggs  h538    R83322  Reject  Not Applicable
8   Ryan Jones  h632    R38299  Defend  Not Applicable
10  Chris Smith h238    R91823  Reject  Not Applicable
11  Joe Bloggs  h538    R18291  Uphold  Not Applicable

结果示例：第 9 行随机删除，因为有 3 个 Chris Smith。

没有其他行受到影响，因为有 2 个或更少的结果。

标签： sqldatabasems-access

解决方案

我认为这将在 MS Access 中起作用：

select c.*
from cases as c
where c.id in (select top 2 c2.id
               from cases as c2
               where c2.handler = c.handler
               order by rnd(-Timer() * c2.id)
              );

推荐阅读