select * from 表 tem where 编号 in (select top 2 编号 from 表 where 类别=tem.类别 order by 值 desc)
--测试表 declare @t table(编号 int,类别 varchar(10),值 int) insert into @t select 1,'a',33 union all select 2,'a',34 union all select 3,'a',35 union all select 4,'a',36 union all select 5,'b',40 union all select 6,'b',49 union all select 7,'c',12 union all select 8,'c',13 union all select 9,'c',20 union all select 10,'c',26--取数据 select * from @t a where 编号 in(select top 2 编号 from @t where 类别=a.类别)/*--测试结果编号 类别 值 ----------- ---------- ----------- 1 a 33 2 a 34 5 b 40 6 b 49 7 c 12 8 c 13(所影响的行数为 6 行) --*/
select t2.编号,t1.类别,t1.值1 from ( select max(值) as 值1,类别 from tablename group by 类别 ) t1, tablename t2 where t2.编号=t2.编号 union select t2.编号,t1.类别,t1.值1 from ( select max(值) as 值1,类别 from tablename where 编号 not in (select max(值),类别 from tablename group by 类别) group by 类别 ) t3, tablename t4 where t2.编号=t2.编号
楼主是要类别值最大的2个.select * from 表 tem where 编号 in (select top 2 编号 from 表 where 类别=tem.类别 order by 值 desc)才是
--呵呵只看了前面的,要求返回前两个,最大的两个:select * from 表 a where 编号 in(select top 2 编号 where 类别=a.类别 order by 编号 desc)
--测试表 declare @t table(编号 int,类别 varchar(10),值 int) insert into @t select 1,'a',33 union all select 2,'a',34 union all select 3,'a',35 union all select 4,'a',36 union all select 5,'b',40 union all select 6,'b',49 union all select 7,'c',12 union all select 8,'c',13 union all select 9,'c',20 union all select 10,'c',26--取数据 select * from @t a where 编号 in(select top 2 编号 from @t where 类别=a.类别 order by 编号 desc)/*--测试结果 编号 类别 值 ----------- ---------- ----------- 3 a 35 4 a 36 5 b 40 6 b 49 9 c 20 10 c 26(所影响的行数为 6 行) --*/
看楼上的各位这么快有这么多答案就 就觉得不难。:) 也来一个: select * from 表 tem where (select count(*) from 表 t where t.类别=表.类别 and t.值>=表.值)<=2 order by 类别,值
复制粘贴老出错:) 看楼上的各位这么快有这么多答案就 就觉得不难。:) 也来一个: select * from 表 where (select count(*) from 表 t where t.类别=表.类别 and t.值>=表.值)<=2 order by 类别,值
declare @t table(编号 int,类别 varchar(10),值 int)
insert into @t
select 1,'a',33
union all select 2,'a',34
union all select 3,'a',35
union all select 4,'a',36
union all select 5,'b',40
union all select 6,'b',49
union all select 7,'c',12
union all select 8,'c',13
union all select 9,'c',20
union all select 10,'c',26--取数据
select * from @t a where 编号 in(select top 2 编号 from @t where 类别=a.类别)/*--测试结果编号 类别 值
----------- ---------- -----------
1 a 33
2 a 34
5 b 40
6 b 49
7 c 12
8 c 13(所影响的行数为 6 行)
--*/
(
select max(值) as 值1,类别 from tablename group by 类别
) t1,
tablename t2
where t2.编号=t2.编号
union
select t2.编号,t1.类别,t1.值1 from
(
select max(值) as 值1,类别 from tablename
where
编号 not in (select max(值),类别 from tablename group by 类别)
group by 类别
) t3,
tablename t4
where t2.编号=t2.编号
declare @t table(编号 int,类别 varchar(10),值 int)
insert into @t
select 1,'a',33
union all select 2,'a',34
union all select 3,'a',35
union all select 4,'a',36
union all select 5,'b',40
union all select 6,'b',49
union all select 7,'c',12
union all select 8,'c',13
union all select 9,'c',20
union all select 10,'c',26--取数据
select * from @t a where 编号 in(select top 2 编号 from @t where 类别=a.类别 order by 编号 desc)/*--测试结果
编号 类别 值
----------- ---------- -----------
3 a 35
4 a 36
5 b 40
6 b 49
9 c 20
10 c 26(所影响的行数为 6 行)
--*/
也来一个:
select * from 表 tem where (select count(*) from 表 t where t.类别=表.类别 and t.值>=表.值)<=2 order by 类别,值
看楼上的各位这么快有这么多答案就 就觉得不难。:)
也来一个:
select * from 表 where (select count(*) from 表 t where t.类别=表.类别 and t.值>=表.值)<=2 order by 类别,值
select * from 表 tem where 编号 in (select top 2 编号 from 表 where 类别=tem.类别 order by 值 desc)
可以满足楼主的要求。
谁能告诉我tem有什么作用??我第一次看见这样的语句。