sql - 如何在一个 MS SQL 查询中获得不同的平均值和总和值？

Question

我正在尝试在 MS SQL Server 2012 中创建一个查询，它为我提供了一个 count、average 和一些 sum 不同记录的值在数据库表中。我会尽力解释我的情况和我的愿望。如果仍有不清楚的地方或需要一些额外的信息，请告诉我。

下表 TEMP 有 10 条记录:

表格

╔════════╦═════════════╦════════╦═══════════╗
║ Number ║ DateOfBirth ║ Gender ║ Activity  ║
╠════════╬═════════════╬════════╬═══════════╣
║ 191806 ║ 1940-08-31  ║ F      ║ AMADMIN   ║
║ 196484 ║ 1940-09-23  ║ F      ║ AMHOST    ║
║ 199480 ║ 1949-10-16  ║ F      ║ AMTRAINER ║
║ 201089 ║ 1947-04-08  ║ M      ║ AMTRAINER ║
║ 204528 ║ 1950-05-02  ║ F      ║ AMHOST    ║
║ 226356 ║ 1966-04-12  ║ M      ║ AMADMIN   ║
║ 226356 ║ 1966-04-12  ║ M      ║ AMHOST    ║
║ 377599 ║ 1985-05-15  ║ F      ║ AMADMIN   ║
║ 377599 ║ 1985-05-15  ║ F      ║ AMHOST    ║
║ 395809 ║ 1980-03-03  ║ F      ║ AMADMIN   ║
╚════════╩═════════════╩════════╩═══════════╝

现在，考虑运行以下查询:

SQL

  SELECT COUNT([Number]) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
    SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
  FROM [TEMP]
  WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')

此查询将给我以下结果:

结果

╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║     10 ║ 57,3       ║    3 ║      7 ║
╚════════╩════════════╩══════╩════════╝

到目前为止一切顺利!但现在是棘手的部分。我真正想要的是表中所有 distinct 记录的结果。这意味着计算所有人的平均年龄和男性/女性人数减去两个“双重”人(具有 Number 226356 和 377599)。所以我需要一个产生以下结果的查询:

想要的结果

╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║      8 ║ 56,9       ║    2 ║      6 ║
╚════════╩════════════╩══════╩════════╝

我知道如何为一段查询获取 distinct 记录，如下所示:

SQL

  SELECT COUNT(DISTINCT([Number])) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
    SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
  FROM [TEMP]
  WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')

但这会产生:

结果

╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║      8 ║ 57,3       ║    3 ║      7 ║
╚════════╩════════════╩══════╩════════╝

现在 Number 计数正常，但 AverageAge、Male 和 Female 值不正确。

我的问题是，如果可以开始这样的查询，我该如何调整我的查询以检索WANTED RESULT 集中显示的值？

Answer 1

由于事件没有出现在任何聚合函数中，您可以简单地将其从结果中扣除，并在聚合之前使用子查询获取不同的记录，然后还应用 COUNT(DISTINCT CASE.. 你的男性/女性人数:

SELECT  COUNT(DISTINCT [Number]) AS Number, 
        ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
        COUNT(DISTINCT CASE WHEN [Gender] = 'M' THEN [Number] END) AS Male,
        COUNT(DISTINCT CASE WHEN [Gender] = 'F' THEN [Number] END) AS Female
FROM    (   SELECT  DISTINCT Number, DateOfBirth, Gender
            FROM    [sw_test].[dbo].[TEMP]
            WHERE   [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
        ) AS t;

Example on SQL Fiddle