22,199
社区成员
发帖
与我相关
我的任务
分享
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。
-------------------------------------------------------------------------------------------------------
---->测试数据
IF OBJECT_ID('TEMPDB.DBO.#TEMP')IS NOT NULL
DROP TABLE #TEMP
------------------------------------------------------------------------------------------------------------
CREATE TABLE #TEMP
(EID INT IDENTITY PRIMARY KEY,
ENAME VARCHAR(10),
DEPARTID INT
)
INSERT INTO #TEMP
SELECT 'AA',1 UNION ALL
SELECT 'BB',1 UNION ALL
SELECT 'CC',1 UNION ALL
SELECT 'DD',2 UNION ALL
SELECT 'EE',3 UNION ALL
SELECT 'FF',2 UNION ALL
SELECT 'AA',3 UNION ALL
SELECT 'AA',2
GO
SELECT A.* FROM #TEMP A
WHERE A.DEPARTID=
(SELECT MAX(DEPARTID)
FROM #TEMP
WHERE ENAME=A.ENAME)
ORDER BY ENAME
结果
EID ENAME DEPARTID
----------- ---------- -----------
7 AA 3
2 BB 1
3 CC 1
4 DD 2
5 EE 3
6 FF 2
(6 行受影响)
这种写法太多了
SELECT A.* FROM #TEMP A
WHERE A.DEPTID=
(SELECT MAX(DEPTID)
FROM #TEMP
WHERE ENAME=A.ENAME)
ORDER BY EID,ENAME
select eid,ename,departid from (
select *,row_number() over(partition by ename order by departid desc ) as rId from employee
) as a where rId=1
SELECT eid,ename,departid
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY ename
ORDER BY departid DESC
) n
FROM employee
) e
WHERE n = 1
我只是提供方法.
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。
-------------------------------------------------------------------------------------------------------
---->测试数据
IF OBJECT_ID('TEMPDB.DBO.#TEMP')IS NOT NULL
DROP TABLE #TEMP
------------------------------------------------------------------------------------------------------------
CREATE TABLE #TEMP
(EID INT IDENTITY PRIMARY KEY,
ENAME VARCHAR(10),
DEPARTID INT
)
INSERT INTO #TEMP
SELECT 'AA',1 UNION ALL
SELECT 'BB',1 UNION ALL
SELECT 'CC',1 UNION ALL
SELECT 'DD',2 UNION ALL
SELECT 'EE',3 UNION ALL
SELECT 'FF',2 UNION ALL
SELECT 'AA',3 UNION ALL
SELECT 'AA',2
GO
SELECT A.* FROM #TEMP A
WHERE A.DEPARTID=
(SELECT MAX(DEPARTID)
FROM #TEMP
WHERE ENAME=A.ENAME)
ORDER BY ENAME
结果
EID ENAME DEPARTID
----------- ---------- -----------
7 AA 3
2 BB 1
3 CC 1
4 DD 2
5 EE 3
6 FF 2
(6 行受影响)
[/quote] 不好意思我之前没仔细看你的语句,谢谢你的方法,但是我想指出的是,你两个sql语句最后的orderby不一样了啊亲,怪不得之前执行结果不对(我也是改过表名的),不过还是要感谢,你这个好理解多了