sqlsever 编写一个函数,形式参数为姓氏(如“李”)值,使用游标实现:如果该姓的学生为男生,则返回该生的姓名,性别,身高值;如果该姓的学生为女生,则返回姓名,性别,出生日期,家庭住址; 返回该姓氏的所有男女生的平均身高。 提示:函数返回这些数据时,可以使用一个连接起来的字符串包含所有要返回的数据,也可以使用表值函数返回以上所需要的数据。本题可能要使用游标多次遍历所有行,分别实现不同要求的操作。
时间: 2024-03-11 18:46:42 浏览: 45
以下是可以实现该功能的 SQL Server 函数代码:
```sql
CREATE FUNCTION fn_GetStudentInfoByLastName (@lastName NVARCHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT AVG(CASE WHEN s.Sex = '男' THEN s.Height ELSE NULL END) AS MaleAvgHeight,
AVG(CASE WHEN s.Sex = '女' THEN s.Height ELSE NULL END) AS FemaleAvgHeight,
(
SELECT STUFF((
SELECT ';' + CONCAT(s1.Name, ',', s1.Sex, ',', s1.Height) AS MaleInfo
FROM Students s1
WHERE LEFT(s1.Name, 1) = @lastName AND s1.Sex = '男'
FOR XML PATH('')
), 1, 1, '') AS MaleInfo
),
(
SELECT STUFF((
SELECT ';' + CONCAT(s2.Name, ',', s2.Sex, ',', CONVERT(NVARCHAR(10), s2.BirthDate, 120), ',', s2.Address) AS FemaleInfo
FROM Students s2
WHERE LEFT(s2.Name, 1) = @lastName AND s2.Sex = '女'
FOR XML PATH('')
), 1, 1, '') AS FemaleInfo
)
FROM Students s
WHERE LEFT(s.Name, 1) = @lastName
)
```
该函数使用表值函数返回结果,其中参数为姓氏的值。函数返回四个字段:
- MaleAvgHeight:该姓氏男生的平均身高。
- FemaleAvgHeight:该姓氏女生的平均身高。
- MaleInfo:该姓氏男生的姓名、性别和身高值,使用分号分隔,格式为“姓名,性别,身高值;姓名,性别,身高值;...”。
- FemaleInfo:该姓氏女生的姓名、性别、出生日期和家庭住址,使用分号分隔,格式为“姓名,性别,出生日期,家庭住址;姓名,性别,出生日期,家庭住址;...”。
函数使用了两个子查询,分别查询该姓氏的男生信息和女生信息。在子查询中,使用了 `FOR XML PATH('')` 将查询结果连接成一个字符串,然后使用 `STUFF` 函数去掉字符串开头的分号。注意在查询女生信息时,使用了 `CONVERT(NVARCHAR(10), s2.BirthDate, 120)` 将日期转换为字符串格式。