SQL语句问题，我是新手，不太会用高级的语句,请高手帮忙

Question

Group No.        Name        Level        Grade
 
每组有8-9个人，level有高有低，当Grade为3时，每组只有一个人level最高。
要求打印所有Grade=3时，所有的组里level最高的人。
例 表为：
Group No.        Name        Level        Grade
        1         Tom            1               2
        1         Tod             3               3
        1         Mike            1               2
        1         Jake            2                3
        2                         
        2                         
 
打印结果为：
Group No.        Name        Level        Grade
        1        Tod           3            3
        2                      

Answer 1

with tb as
(
  select GroupNo, max(Level) as level
  from table
  group by GroupNo, Level
)
select GroupNo, Name, Level, Grade
from table
inner join tb on tb.GroupNo = table.GroupNo
and tb.Level= table.Level
where tb.Grade = 3

Answer 2

多谢回复，我就是不会用join
但好像报错：
invalid use of an aggregate function

Answer 3

select * from tb a
 where not eixsts(select 1 from tb where GroupNo=a.GroupNo and Level>a.Level)

Answer 4

我也刚学，写个练习试试
select 
a.groupno,a.name,a.level,a.grade
from tbl1 as a
inner join ( 
select groupno ,max(level) as lev 
from tbl1 
where grade=3 
group by groupno 
)t on t.groupno =a.groupno 
and t.lev=a.level
where a.grade=3

Answer 5

--Create table
CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
--insert
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3
--查询 max(names) 将names 放入聚合函数 无实际意义
SELECT * FROM tabletest
go
SELECT GroupNo,MAX(NAMES) AS NAMES,MAX(Levels) AS TopLevel,Grade 
FROM tabletest 
WHERE grade=3 
GROUP BY GroupNo,Grade
--Delete
DROP TABLE tabletest

Answer 6

GroupNo     NAMES                                              Levels      Grade
----------- -------------------------------------------------- ----------- -----------
1           Tom                                                1           3
1           Tim                                                2           3
2           John                                               1           3
2           Jimmy                                              2           3
3           Tommy                                              3           3
3           Sunny                                              2           3GroupNo     NAMES                                              TopLevel    Grade
----------- -------------------------------------------------- ----------- -----------
1           Tom                                                2           3
2           John                                               2           3
3           Tommy                                              3           3

Answer 7

偷懒出问题 抱歉。。--Create table
CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
--insert
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3
--查询
SELECT * FROM tabletest
go
--with 查询子句
WITH t AS
(SELECT GroupNo,MAX(Levels) AS TopLevel,Grade 
FROM tabletest 
WHERE grade=3 
GROUP BY GroupNo,Grade)
SELECT tt.* FROM t LEFT JOIN tabletest tt ON tt.GroupNo=t.GroupNo AND Levels=TopLevel AND tt.Grade=t.Grade
--Delete
DROP TABLE tabletestGroupNo     NAMES                                              Levels      Grade
----------- -------------------------------------------------- ----------- -----------
1           Tom                                                1           3
1           Tim                                                2           3
2           John                                               1           3
2           Jimmy                                              2           3
3           Tommy                                              3           3
3           Sunny                                              2           3(6 行受影响)GroupNo     NAMES                                              Levels      Grade
----------- -------------------------------------------------- ----------- -----------
1           Tim                                                2           3
2           Jimmy                                              2           3
3           Tommy                                              3           3(3 行受影响)

Answer 8

CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
--insert
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3;WITH c1 as
(
SELECT  GroupNo, MAX(Levels) lev
FROM tabletest
WHERE Grade = 3
GROUP BY GroupNo
)
select c1.*, t.NAMES
from c1
inner join tabletest t on c1.GroupNo = t.GroupNo and c1.lev = t.Levels 
GroupNo     lev         NAMES
----------- ----------- --------------------------------------------------
1           2           Tim
2           2           Jimmy
3           3           Tommy(3 行受影响)借用造数语句

Answer 9

我测试了下9L的代码，结果不对的，请高手继续解答
select *
from tabletest
;
WITH c1 as
(
SELECT  GroupNo, MAX(Level) lev
FROM tabletest
WHERE Grade = 3
GROUP BY GroupNo
)
select c1.* , t.name,t.grade
from c1
inner join tabletest t on c1.GroupNo = t.GroupNo and c1.lev = t.LevelGroupNo     Name                                               Level       Grade
----------- -------------------------------------------------- ----------- -----------
1           tom                                                2           3
1           jade                                               2           2
1           shally                                             3           2
1           ken                                                1           3
1           realya                                             3           2
1           piney                                              3           1
1           erlin                                              3           2
1           susan                                              3           2
2           eric                                               1           2
2           paul                                               2           3
2           tony                                               3           3
2           maggie                                             1           2
2           tiny                                               2           2
2           tracy                                              2           2
2           rose                                               3           1
2           marry                                              3           1
2           gill                                               3           1
3           ann                                                2           2
3           min                                                2           2
3           judy                                               1           3
3           sam                                                3           1
3           vicky                                              1           3
3           goll                                               3           2
3           tim                                                3           1
3           andy                                               2           3(25 row(s) affected)GroupNo     lev         name                                               grade
----------- ----------- -------------------------------------------------- -----------
1           2           tom                                                3
1           2           jade                                               2
2           3           tony                                               3
2           3           rose                                               1
2           3           marry                                              1
2           3           gill                                               1
3           2           andy                                               3
3           2           ann                                                2
3           2           min                                                2(9 row(s) affected)

Answer 10

CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3select distinct a.GroupNo,b.NAMES,b.Levels 
from tabletest a 
cross apply 
(select top(1) NAMES,Levels 
from tabletest b 
where a.GroupNo = b.GroupNo 
order by Levels desc )b
/*GroupNo     NAMES                                              Levels
----------- -------------------------------------------------- -----------
1           Tim                                                2
2           Jimmy                                              2
3           Tommy                                              3(3 行受影响)
*/

Answer 11

--漏了个条件
CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3select distinct a.GroupNo,b.NAMES,b.Levels 
from tabletest a 
cross apply 
(select top(1) NAMES,Levels 
from tabletest b 
where a.GroupNo = b.GroupNo 
order by Levels desc )b WHERE A.Grade=3
/*GroupNo     NAMES                                              Levels
----------- -------------------------------------------------- -----------
1           Tim                                                2
2           Jimmy                                              2
3           Tommy                                              3(3 行受影响)
*/

Answer 12

CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)select 1,'tom',2,3 UNION ALL
select 1,'jade',2,2 UNION ALL
select 1,'shally',3,2 UNION ALL
select 1,'ken',1,3 UNION ALL
select 1,'realya',3,2 UNION ALL
select 1,'piney',3,1 UNION ALL
select 1,'erlin',3,2 UNION ALL
select 1,'susan',3,2 UNION ALL
select 2,'eric',1,2 UNION ALL
select 2,'paul',2,3 UNION ALL
select 2,'tony',3,3 UNION ALL
select 2,'maggie',1,2 UNION ALL
select 2,'tiny',2,2 UNION ALL
select 2,'tracy',2,2 UNION ALL
select 2,'rose',3,1 UNION ALL
select 2,'marry',3,1 UNION ALL
select 2,'gill',3,1 UNION ALL
select 3,'ann',2,2 UNION ALL
select 3,'min',2,2 UNION ALL
select 3,'judy',1,3 UNION ALL
select 3,'sam',3,1 UNION ALL
select 3,'vicky',1,3 UNION ALL
select 3,'goll',3,2 UNION ALL
select 3,'tim',3,1 UNION ALL
select 3,'andy',2,3select distinct a.GroupNo,b.NAMES,b.Levels 
from tabletest a 
cross apply 
(select top(1) NAMES,Levels 
from tabletest b 
where a.GroupNo = b.GroupNo 
order by Levels desc )b WHERE A.Grade=3(25 row(s) affected)
GroupNo     NAMES                                              Levels
----------- -------------------------------------------------- -----------
1           shally                                             3
2           tony                                               3
3           sam                                                3(3 row(s) affected)
-----------------------------------------------------------
-----------------------------------------------------------
我觉得正确的结果应该如下，可是不知道怎么出来
GroupNo     NAMES                                              Levels
----------- -------------------------------------------------- -----------
1           tom                                                2
2           tony                                               3
3           andy                                               2

Answer 13

CREATE TABLE tabletest
( GroupNo INT ,NAMES VARCHAR(50),Levels INT,Grade INT)
INSERT INTO tabletest (GroupNo,NAMES,Levels,Grade)
SELECT 1,'Tom',1,3 UNION ALL
SELECT 1,'Tim',2,3 UNION ALL
SELECT 2,'John',1,3 UNION ALL
SELECT 2,'Jimmy',2,3 UNION ALL
SELECT 3,'Tommy',3,3 UNION ALL
SELECT 3,'Sunny',2,3select distinct a.GroupNo,b.NAMES,b.Levels 
from tabletest a 
cross apply 
(select top(1) NAMES,Levels 
from tabletest b 
where a.GroupNo = b.GroupNo and b.Grade = 3 
order by Levels desc )b WHERE A.Grade=3
/*GroupNo     NAMES                                              Levels
----------- -------------------------------------------------- -----------
1           Tim                                                2
2           Jimmy                                              2
3           Tommy                                              3(3 行受影响)
*/