我试图创建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集中显示的值？
由于活动未出现在任何汇总函数中，因此您可以简单地从结果中减去它，并在汇总之前使用子查询获取不同的记录，然后再将其应用于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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句