写个查询语句怎么写都不对
打个比方吧 表名为 table1
表中有如下数值
num type val
1 0 1
1 1 2
1 0 4
1 0 5
2 0 4
2 0 7
2 0 1
2 1 4
2 0 2
2 1 1我想取的表中 按num和type来分类按val来降序排序的前2名值 想要的结果如下num type val
1 0 5
1 0 4
1 1 2
2 0 7
2 0 4
2 1 4
2 1 4请问sql语句怎么写。。不甚感激
打个比方吧 表名为 table1
表中有如下数值
num type val
1 0 1
1 1 2
1 0 4
1 0 5
2 0 4
2 0 7
2 0 1
2 1 4
2 0 2
2 1 1我想取的表中 按num和type来分类按val来降序排序的前2名值 想要的结果如下num type val
1 0 5
1 0 4
1 1 2
2 0 7
2 0 4
2 1 4
2 1 4请问sql语句怎么写。。不甚感激
from 表 AS A where val in(
select top 2 val from 表 where num=A.num and type= A.type
order by val desc)order by num,type, val desc
这是我仿照你写的代码 选出的结果只有两条 我的意思是说呢 把所有数据 按num type分类(比如说 1 0是一类 1 1 是一类 ) 对每个分类中的数据按降序排序 选出前两名(只要前两名的数据 ) 表达不清楚的地方还望见谅
select Gpdm,Sc,Issuedate from [210104000] A where Issuedate in(select top 2 Issuedate from [210104000] WHERE Gpdm = A.Gpdm AND Sc = A.Sc order by Issuedate desc)order by Gpdm,Sc,Issuedate desc
go
insert into table1
select 1,0,1 union all
select 1,1,2 union all
select 1,0,4 union all
select 1,0,5 union all
select 2,0,4 union all
select 2,0,7 union all
select 2,0,1 union all
select 2,1,4 union all
select 2,0,2 union all
select 2,1,1 select distinct num,type,val
from table1 AS A where val in(
select top 2 val from table1 where num=A.num and type= A.type
order by val desc) order by num,type, val desc
结果:1 0 5
1 0 4
1 1 2
2 0 7
2 0 4
2 1 4
2 1 1