求出每个职称中工资最高、最低教师的姓名、性别、年龄、工资,并给出各职称的平 均工资。职称有助教、讲师(包括工程师)、副教授(包括高级工程师)、教授(包括研究员)。sqlserver
时间: 2023-10-20 11:32:22 浏览: 154
好的,以下是 SQL Server 的查询语句:
```
-- 最高工资
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '助教'
ORDER BY
t.Salary DESC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '讲师'
ORDER BY
t.Salary DESC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '副教授'
ORDER BY
t.Salary DESC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '教授'
ORDER BY
t.Salary DESC
-- 最低工资
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '助教'
ORDER BY
t.Salary ASC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '讲师'
ORDER BY
t.Salary ASC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '副教授'
ORDER BY
t.Salary ASC
UNION ALL
SELECT TOP 1
t.Name, t.Sex, t.Age, t.Salary, t.Title
FROM
Teacher t
WHERE
t.Title = '教授'
ORDER BY
t.Salary ASC
-- 平均工资
UNION ALL
SELECT
NULL, NULL, NULL, AVG(t.Salary), t.Title
FROM
Teacher t
GROUP BY
t.Title
```
其中,假设教师信息存储在名为 `Teacher` 的表中,包含以下字段:`Name`(姓名)、`Sex`(性别)、`Age`(年龄)、`Salary`(工资)和 `Title`(职称)。