在 SQL 中选择不在 Group By 中的列 [英] Select a Column in SQL not in Group By

问题描述

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

选择MAX(cpe.createdon)作为MaxDate，cpe.fmgcms_cpeclaimid来自 Filteredfmgcms_claimpaymentestimate cpe其中 cpe.createdon <'报告开始日期'按 cpe.fmgcms_cpeclaimid 分组

这是我想要的结果:

选择MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid, cpe.fmgcms_claimid来自 Filteredfmgcms_claimpaymentestimate cpe其中 cpe.createdon <'报告开始日期'按 cpe.fmgcms_cpeclaimid 分组

解决方案

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

• 用作 group by 条件之一的表达式，或 ...
• 聚合函数，或...
• 文字值

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

<块引用>

我想找到包含最近的单个声明行我的索赔表中每个组内的创建日期

给定

创建表dbo.some_claims_table(claim_id int 不为空，group_id int 不为空，date_created 日期时间不为空，约束 some_table_PK 主键( claim_id )，约束 some_table_AK01 唯一( group_id ， claim_id )，约束 some_Table_AK02 唯一( group_id ， date_created )，)

首先要确定每个组的最近创建日期:

选择 group_id ，date_created = max( date_created )来自 dbo.claims_table按 group_id 分组

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

选择 *来自 dbo.claims_table t加入(选择 group_id ，date_created = max( date_created )来自 dbo.claims_table按 group_id 分组) x 上的 x.group_id = t.group_id和 x.date_created = t.date_created

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

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

解决方案

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

• 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

Given

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

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

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

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