Access查询-分组依据和最大值

Question

我已经在这上面工作了几个小时了，就是想不出来。

如何按照enco_id对此查询进行排序，并且只显示每个clpr_id的最大值。例如：

​

​

​

​

下面是我的MSAccess Sql代码

SELECT dbo_Client.med_rec_no, dbo_Encounter.episode_number, dbo_client_program.enco_id, dbo_client_program.clpr_id, dbo_client_program.prle_id, dbo_PROGRAM_LEVEL.prle_name, dbo_Client.fname, dbo_Client.lname, dbo_DISCHARGE_STATUS.dist_name, DS2.dist_name
FROM (((((dbo_Client INNER JOIN dbo_Encounter ON dbo_Client.client_id = dbo_Encounter.client_id) INNER JOIN dbo_Episode_info ON dbo_Encounter.enco_id = dbo_Episode_info.enco_id) INNER JOIN dbo_DISCHARGE_STATUS ON dbo_Episode_info.dist_id = dbo_DISCHARGE_STATUS.dist_id) INNER JOIN dbo_client_program ON dbo_Encounter.enco_id = dbo_client_program.enco_id) INNER JOIN dbo_DISCHARGE_STATUS AS DS2 ON dbo_client_program.dist_id = DS2.dist_id) INNER JOIN dbo_PROGRAM_LEVEL ON dbo_client_program.prle_id = dbo_PROGRAM_LEVEL.prle_id;

下面是我的查询

​

​

编辑：

这是我现在正在尝试使用的代码，但它仍然不能工作

SELECT dbo_Client.client_id, dbo_Client.med_rec_no, dbo_Encounter.episode_number, dbo_Encounter.start_date, dbo_Encounter.end_date, dbo_client_program.clpr_id, dbo_client_program.enco_id, dbo_PROGRAM_LEVEL.prle_name, dbo_Client.fname, dbo_Client.lname, dbo_DISCHARGE_STATUS.dist_name, DS2.dist_name
FROM (((((dbo_Client INNER JOIN dbo_Encounter ON dbo_Client.client_id = dbo_Encounter.client_id) INNER JOIN dbo_Episode_info ON dbo_Encounter.enco_id = dbo_Episode_info.enco_id) INNER JOIN dbo_DISCHARGE_STATUS ON dbo_Episode_info.dist_id = dbo_DISCHARGE_STATUS.dist_id) INNER JOIN dbo_client_program ON dbo_Encounter.enco_id = dbo_client_program.enco_id) INNER JOIN dbo_DISCHARGE_STATUS AS DS2 ON dbo_client_program.dist_id = DS2.dist_id) INNER JOIN dbo_PROGRAM_LEVEL ON dbo_client_program.prle_id = dbo_PROGRAM_LEVEL.prle_id
WHERE dbo_client_program.clpr_id IN
(SELECT TOP 1 clpr_id FROM dbo_client_program as New
WHERE New.clpr_id = dbo_client_program.clpr_id
ORDER by dbo_client_program.clpr_id DESC) AND (dbo_DISCHARGE_STATUS.dist_name <> DS2.dist_name) AND dbo_Encounter.start_date > #1/1/2020# AND dbo_Encounter.end_date > #1/1/2020#
ORDER BY  dbo_Encounter.episode_number;  

Answer 1

由于您仍然了解聚合查询，因此我的建议是：

1-创建按所有字段和最大值(Clpr_id)分组的查询。将其另存为"Query1“2-基于"Query1”创建另一个查询并按"enco_id“排序