SQL server -分组依据和替换

Question

我有一张桌子

+--------+-----+
| Gender | AGE |
+--------+-----+
| M      |  10 |
| F      |  29 |
| M      |  30 |
| F      |  40 |
| F      |   5 |
+--------+-----+

我想要有一个输出，其中按性别显示年龄最大的，并添加一列，表明他们是最老的。我将使用什么查询？

示例输出：

+--------+-----+--------+
| Gender | Age |  Tag   |
+--------+-----+--------+
| M      |  30 | Oldest |
| F      |  40 | oldest |
+--------+-----+--------+

谢谢你的帮助。

Answer 1

只是一个简单的Group by

DECLARE @SampleData AS TABLE
(
    Gender varchar(1),
    Age int
)

INSERT INTO @SampleData
(
    Gender,
    Age
)
VALUES
( 'M', 10),
( 'F', 29),
( 'M', 30),
( 'F', 40),
( 'F', 5 )

SELECT  sd.Gender, 
        max(sd.Age) AS Age,
        'Oldest' AS [Tag]
FROM @SampleData sd
GROUP BY sd.Gender

返回

Gender  Age Tag
-----------------
F       40  Oldest
M       30  Oldest