在SQL中选择一个列，不在分组依据中 [英] Select a Column in SQL not in Group By

问题描述

我一直在尝试查找有关如何选择SQL的分组依据"语句中未包含的非聚合列的信息，但到目前为止，我所发现的所有内容似乎都无法回答我的问题.我有一张要三列的表格.一个是创建日期，一个是将记录按特定的Claim ID分组的ID，最后一个是PK.我想找到在每个索赔ID组中具有最大创建日期的记录.我正在选择MAX(创建日期)和索赔ID(cpe.fmgcms_cpeclaimid)，然后按索赔ID进行分组.但是我需要这些记录(cpe.fmgcms_claimid)中的PK，如果尝试将其添加到我的select子句中，则会收到错误消息.而且我无法将其添加到我的group by子句中，因为那样一来它就会脱离我的预期分组.有人知道任何解决方法吗?这是我的代码示例:

I have been trying to find some info on how to select a non-aggregate column that is not contained in the Group By statement in SQL, but nothing I've found so far seems to answer my question. I have a table with three columns that I want from it. One is a create date, one is a ID that groups the records by a particular Claim ID, and the final is the PK. I want to find the record that has the max creation date in each group of claim IDs. I am selecting the MAX(creation date), and Claim ID (cpe.fmgcms_cpeclaimid), and grouping by the Claim ID. But I need the PK from these records (cpe.fmgcms_claimid), and if I try to add it to my select clause, I get an error. And I can't add it to my group by clause because then it will throw off my intended grouping. Does anyone know any workarounds for this? Here is a sample of my code:

Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid 
from Filteredfmgcms_claimpaymentestimate cpe
where cpe.createdon < 'reportstartdate'
group by cpe.fmgcms_cpeclaimid

这是我想要得到的结果:

This is the result I'd like to get:

Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid, cpe.fmgcms_claimid 
from Filteredfmgcms_claimpaymentestimate cpe
where cpe.createdon < 'reportstartdate'
group by cpe.fmgcms_cpeclaimid

推荐答案

带有group by子句的select查询的结果集中的列必须为:

The columns in the result set of a select query with group by clause must be:

• 用作group by准则之一的表达式，或...
• 集合函数，或...
• 文字值

• an expression used as one of the group by criteria , or ...
• an aggregate function , or ...
• a literal value

因此，您无法在单个简单查询中完成您想做的事情.首先要做的是以清晰的方式陈述您的问题陈述，例如:

So, you can't do what you want to do in a single, simple query. The first thing to do is state your problem statement in a clear way, something like:

我想查找包含最新的单个索赔行 我的索赔表中每个组中的创建日期

I want to find the individual claim row bearing the most recent creation date within each group in my claims table

给予

create table dbo.some_claims_table
(
  claim_id     int      not null ,
  group_id     int      not null ,
  date_created datetime not null ,

  constraint some_table_PK primary key ( claim_id                ) ,
  constraint some_table_AK01 unique    ( group_id , claim_id     ) ,
  constraint some_Table_AK02 unique    ( group_id , date_created ) ,

)

首先要做的是确定每个组的最新创建日期:

The first thing to do is identify the most recent creation date for each group:

select group_id ,
       date_created = max( date_created )
from dbo.claims_table
group by group_id

这为您提供了需要的选择标准(每组1行，两列:group_id和highwater创建日期)，以满足需求的第一部分(从每个组中选择单个行.必须是您最终的select查询中的虚拟表:

That gives you the selection criteria you need (1 row per group, with 2 columns: group_id and the highwater created date) to fullfill the 1st part of the requirement (selecting the individual row from each group. That needs to be a virtual table in your final select query:

select *
from dbo.claims_table t
join ( select group_id ,
       date_created = max( date_created )
       from dbo.claims_table
       group by group_id
      ) x on x.group_id     = t.group_id
         and x.date_created = t.date_created

如果表不是group_id(AK02)中的date_created唯一的表，则可以为给定的组获得重复的行.

If the table is not unique by date_created within group_id (AK02), you you can get duplicate rows for a given group.

这篇关于在SQL中选择一个列，不在分组依据中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！