34,590
社区成员
发帖
与我相关
我的任务
分享
--构建示例数据 drop table Sailors
Create TABLE Sailors (sid int,same varchar(50),rating int,age numeric(32,2))
INSERT INTO Sailors select '22', 'Dustin', '7', '45.0'
union all select '29', 'Brutus', '1', '33.0'
union all select '31', 'Lubber', '8', '55.5'
union all select '32', 'Andy', '8', '25.5'
union all select '58', 'Rusty', '10', '35.0'
union all select '64', 'Horatio', '7', '35.0'
union all select '71', 'Zorba', '10', '16.0'
union all select '74', 'Horatio', '9', '35.0'
union all select '85', 'Art', '3', '25.5'
union all select '95', 'Bob', '3', '63.5'
--方案
--1.对于至少有两个水手的等级级别,求出水手们的平均年龄
select rating,AVG(age) avgage from Sailors group by rating having COUNT(same) >= 2
/*rating avgage
----------- ---------------------------------------
3 44.500000
7 40.000000
8 40.500000
10 25.500000
(4 行受影响)
*/
--2.对于至少有两个水手的等级,找出到了投票年龄(至少有18岁)的所有水手的平均年龄
select rating,AVG(age) avgage from Sailors
where rating in(select rating from Sailors group by rating having COUNT(same) >= 2)
and age >= 18 group by rating
/*
rating avgage
----------- ---------------------------------------
3 44.500000
7 40.000000
8 40.500000
10 35.000000
(4 行受影响)
*/
--3.找出在所有的等级中水手的平均年龄是最低的那些等级
select TOP(1) rating,AVG(age) avgage from Sailors group by rating ORDER BY AVG(age)
/*rating avgage
----------- ---------------------------------------
10 25.500000
(1 行受影响)
*/
--12\
SELECT rating,
Avg(age) avgage
FROM sailors
GROUP BY rating
HAVING Count(same) >= 2
--13\
SELECT rating,
Avg(age) avgage
FROM sailors
WHERE age >= 18
AND rating IN (SELECT rating
FROM sailors
GROUP BY rating
HAVING Count(same) >= 2)
GROUP BY rating
--14\
select TOP(1) rating,AVG(age) avgage from Sailors group by rating ORDER BY AVG(age) asc