改进按性别查询结果

debugcn 发表于 Dev

艾维亚斯

我有下面的查询为我的所有农民获取我表中的所有年龄范围，但我需要按性别类型分类的数据。

WITH AgeData as
(
  SELECT [sex],
         [dob],
         DATEDIFF(YEAR, [dob], GETDATE()) AS [AGE]
  FROM dbo.Farmers
),
GroupAge AS
(
  SELECT [sex],
         [dob],      
         CASE
             WHEN AGE < 24 THEN 'Under 24'
             WHEN AGE BETWEEN 25 AND 30 THEN '25 - 30'
             WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN AGE > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [AgeGrpCount],[Age Groups]
FROM GroupAge
GROUP BY [Age Groups];

如果我将此添加到代码中，我会得到我的男性性别类型：

WITH AgeData as
(
  SELECT [sex],
         [dob],
         DATEDIFF(YEAR, [dob], GETDATE()) AS [AGE]
  FROM dbo.Farmers
),
GroupAge AS
(
  SELECT [sex],
         [dob],      
         CASE
             WHEN AGE < 24 THEN 'Under 24'
             WHEN AGE BETWEEN 25 AND 30 THEN '25 - 30'
             WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN AGE > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [MaleAgeGrpCount],[Age Groups]
FROM GroupAge
Where sex = 1
GROUP BY [Age Groups];

但我想要一个 [MaleAgeGrpCount] 和一个 [FemaleAgeGrpCount] Colum 而不是其中性别 = 1 是男性，性别 = 2 是女性。

蒂姆·比格莱森

使用条件聚合：

SELECT
    [Age Groups],
    COUNT(CASE WHEN sex = 1 THEN 1 END) AS [MaleAgeGrpCount],
    COUNT(CASE WHEN sex = 2 THEN 1 END) AS [FemaleAgeGrpCount]
FROM GroupAge
GROUP BY [Age Groups];

这里的想法是使用单独的CASE表达式来计算按年龄组聚合的男性和女性记录。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。