SQL 在结果中从 GROUP BY 中排除字段，但在 WHERE 中使用 [英] SQL Exclude Field from GROUP BY in results but use in WHERE

问题描述

非常简单的表格:

CREATE TABLE [dbo].[Recognitions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Submitter_CH_id] [int] NULL,
[Submitter_Last_Name] [varchar](50) NULL,
[Submit_Date] [datetime] NULL,

Submitter_CH_id Submitter_Last_Name Submit_Date
50              Prokupek             2014-04-01 00:00:00.000
50              Prokupek             2014-04-07 00:00:00.000
50              Prokupek             2014-04-01 00:00:00.000
50              Prokupek             2014-04-07 00:00:00.000
215             Conklin              2014-04-07 00:00:00.000
215             Conklin              2014-04-07 00:00:00.000
130             Catron               2014-04-07 00:00:00.000
136             Jardee               2014-04-07 00:00:00.000
247             Emken                2014-04-07 00:00:00.000

我需要做的是获取按 recipient_ch_id 分组的特定日期范围内所有提交的计数.我的应用允许用户输入日期范围，因此它需要成为我的应用的查询结果的一部分才能使用它.

What I need to do is get a count of all the submissions made with in a certain date range grouped by recipient_ch_id. My app allows the user to enter the date range, so it needs to be part of the query results for my app to use it.

我需要按 Submitter_CH_id 对结果进行分组.所以是这样的:

I need the results to be grouped by Submitter_CH_id. So something like this:

SELECT TOP (100) PERCENT Submitter_CH_id, Submitter_First_Name, Submitter_Last_Name,     Submitter_Email, Submitter_Department, 
                  Submit_Date AS [Last Submit], COUNT(Submitter_CH_id) AS [Total Submit]
FROM         dbo.Recognitions
GROUP BY Submitter_First_Name, Submitter_Last_Name, Submitter_Email,     Submitter_Department, Submitter_CH_id, Submit_Date
ORDER BY Submitter_CH_id

我想要的是以下内容:

Submitter_CH_ID Submitter_Last_Name Total Submissions
50              Prokupek             4
215             Conklin              2
130             Catron               1

...但是因为我还必须在我的 GROUP BY 中包含 Submit_Date 结果而不是显示每个唯一日期的每个 ID 的计数(当然它必须)，所以我得到了这样的信息:

... but because I also have to include Submit_Date in my GROUP BY the results instead show the count per ID per unique date (which it has to of course), so I get something like this:

Submitter_CH_ID Submitter_Last_Name Total Submissions
50              Prokupek                 2
50              Prokupek                 2
215             Conklin              1
215             Conklin              1
130             Catron               1

有什么想法吗?这是 MS SQL 2008.非常感谢.

Any thoughts? This is MS SQL 2008. Thanks very much.

推荐答案

使用子查询....像这样:

use a sub query.... like this:

select Submitter_CH_ID, Submitter_Last_Name, count(ID) AS [Total Submissions]
from (
    select ID, Submitter_CH_ID, Submitter_Last_Name
    from  dbo.Recognitions
    where date >= @start_date and date <= @end_date
) T
GROUP BY Submitter_CH_ID, Submitter_Last_Name

是子查询！

这篇关于SQL 在结果中从 GROUP BY 中排除字段，但在 WHERE 中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！