select * from tb as a where 2>(select count(*) from tb where 单位=a.单位 and 月薪>a.月薪) order by 工号;select top 2 * from tb order by 月薪 desc,工号;select * from tb as a where 月薪 in (select top 2 月薪 from tb where a.单位=b.单位 order by 月薪 desc);select * from(select rowid=row_number() over(partition by 单位 order by 月薪 desc),* from tb) as a where rowid<=2
--不考虑同样的钱 select top 2 * from tb order by 月薪 desc , 工號 --考虑同样的钱,(即第二名钱一样)select * from tb where 月薪 in (select top 2 * from tb order by 月薪) order by 月薪 desc , 工號
select * from table1 a where 2>(select count(*) from table1 where 單位=a.單位 and 月薪>a.月薪) order by 單位,月薪 desc
----------------------------------- Author: liangCK 小梁-- Title : 查每个分组前N条记录-- Date : 2008-11-13 17:19:23--------------------------------- --> 生成测试数据: #TIF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #TCREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)INSERT INTO #TSELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALLSELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALLSELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALLSELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALLSELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALLSELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALLSELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALLSELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALLSELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALLSELECT '010',3,'黄占涛','SQL技术手册','2006-01-01' --SQL查询如下: --按GID分组,查每个分组中Date最新的前2条记录 --1.字段ID唯一时:SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC) --2.如果ID不唯一时:SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date) --SQL Server 2005 使用新方法 --3.使用ROW_NUMBER()进行排位分组SELECT ID,GID,Author,Title,DateFROM( SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),* FROM #T) AS TWHERE rid<=2 --4.使用APPLYSELECT DISTINCT b.*FROM #T AS aCROSS APPLY( SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC) AS b --结果/* ID GID Author Title Date---- ----------- ----------------------------- ------------------------- -----------------------003 1 格罗夫Groff.J.R. SQL完全手册 2009-07-01 00:00:00.000004 1 KalenDelaney SQLServer2005技术内幕存储引擎 2008-08-01 00:00:00.000005 2 Alex.Kriegel.Boris.M.Trukhnov SQL宝典 2007-10-05 00:00:00.000007 2 胡百敬 SQLServer2005数据库开发详解 2008-06-15 00:00:00.000009 3 赵松涛 SQLServer2005系统管理实录 2008-10-01 00:00:00.000010 3 黄占涛 SQL技术手册 2006-01-01 00:00:00.000 (6 行受影响)*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liangCK/archive/2008/11/13/3292540.aspx
下面的一句修改一下.select * from tb where 月薪 in (select top 2 月薪 from tb order by 月薪 desc)ORDER BY 工號 --这种将会返回两个值,但其结果集中可能有很多行(>=2).
select top 2 * from salaryTable group by salary,id
写得匆忙了点,不好意思,更改一下. --不考虑同样的钱 select top 2 * from tb order by 月薪 desc , 工號--考虑同样的钱,(即第二名钱一样)select * from tb where 月薪 in (select top 2 月薪 from tb order by 月薪) order by 月薪 desc , 工號
第二种写法可以加一点: select * from tb where 月薪in(select distinct top(2) 月薪 from tb order by desc) ordery by 工號
where 2>(select count(*) from tb
where 单位=a.单位 and 月薪>a.月薪)
order by 工号;select top 2 * from tb order by 月薪 desc,工号;select * from tb as a
where 月薪 in (select top 2 月薪 from tb
where a.单位=b.单位 order by 月薪 desc);select *
from(select rowid=row_number() over(partition by 单位 order by 月薪 desc),*
from tb) as a
where rowid<=2
select top 2 * from tb order by 月薪 desc , 工號 --考虑同样的钱,(即第二名钱一样)select * from tb where 月薪 in (select top 2 * from tb order by 月薪) order by 月薪 desc , 工號
from table1 a
where 2>(select count(*) from table1 where 單位=a.單位 and 月薪>a.月薪)
order by 單位,月薪 desc
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
大概的表是
create table tb
(
工號 varchar (5) not null
部門 varchar (50) null
單位 varchar (50) null
月薪 float (8) null
)
要的結果是
單位 工號 月薪
01 3000
02 2600
單位 工號 月薪
01 3000
02 2600
下面的一句修改一下.select * from tb where 月薪 in (select top 2 月薪 from tb order by 月薪 desc)ORDER BY 工號 --这种将会返回两个值,但其结果集中可能有很多行(>=2).
--不考虑同样的钱
select top 2 * from tb order by 月薪 desc , 工號--考虑同样的钱,(即第二名钱一样)select * from tb where 月薪 in (select top 2 月薪 from tb order by 月薪) order by 月薪 desc , 工號