UC知道一个SQL语句查询的问题
来源：百度知道 编辑：UC知道 时间：2024/05/14 04:22:14
1 90 2008-5-1 0:00:00
2 30 2007-3-1 0:00:00
3 89 2008-5-3 0:00:00
4 29 2007-3-4 0:00:00
5 10 2007-3-2 0:00:00
6 423 2008-4-3 0:00:00
7 234 2007-1-2 0:00:00
以上是一个表 A
求一条SQL语句查询出每年的头两条最大的记录，也就是说，查询结果为
id num date
6 423 2008-4-3 0:00:00
1 90 2008-5-1 0:00:00
7 234 2007-1-2 0:00:00
2 30 2007-3-1 0:00:00
谢谢各位的回答，我知道有变通的办法，但是我想知道有没有人能用一条语句解决。
貌似一条语句无法实现，如果有达人解决一定学习下。
刚刚学习了下，自己解决了，并测试成功。
测试数据
create table MyTable(
id int identity(1,1) primary key,
num int,
date int
)
insert into MyTable values(9,1997)
insert into MyTable values(3,1997)
insert into MyTable values(5,1997)
insert into MyTable values(7,1997)
insert into MyTable values(1,1997)
insert into MyTable values(20,1998)
insert into MyTable values(4,1998)
insert into MyTable values(6,1998)
insert into MyTable values(8,1998)
insert into MyTable values(10,1998)
Sql语句：
select id,num,date from MyTable
where num in (
(select max(num) num from MyTable group by date)
union
(select max(num) num from MyTable where num not in
(select max(num) from MyTable group by date)
group by date)
)order by date desc
select top 2 max(num) from table
SELECT top 2 * from A order by num asc
select id num date from A,a wh