SELECT eid,ename,departid FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ename ORDER BY departid DESC ) n FROM employee ) e WHERE n = 1
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 A.* FROM #TEMP A WHERE A.DEPTID= (SELECT MAX(DEPTID) FROM #TEMP WHERE ENAME=A.ENAME) ORDER BY EID,ENAME
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。 ----------------------------------------------------------------------------------------------------------->测试数据 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 GOSELECT 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 行受影响)
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。 ----------------------------------------------------------------------------------------------------------->测试数据 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 GOSELECT 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 行受影响) 不好意思我之前没仔细看你的语句,谢谢你的方法,但是我想指出的是,你两个sql语句最后的orderby不一样了啊亲,怪不得之前执行结果不对(我也是改过表名的),不过还是要感谢,你这个好理解多了
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY ename
ORDER BY departid DESC
) n
FROM employee
) e
WHERE n = 1
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 A.* FROM #TEMP A
WHERE A.DEPTID=
(SELECT MAX(DEPTID)
FROM #TEMP
WHERE ENAME=A.ENAME)
ORDER BY EID,ENAME
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。
----------------------------------------------------------------------------------------------------------->测试数据
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
GOSELECT 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 行受影响)
不懂就别乱说,什么得不到你要的结果。你自己不会改下表名,和字段名。
----------------------------------------------------------------------------------------------------------->测试数据
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
GOSELECT 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 行受影响) 不好意思我之前没仔细看你的语句,谢谢你的方法,但是我想指出的是,你两个sql语句最后的orderby不一样了啊亲,怪不得之前执行结果不对(我也是改过表名的),不过还是要感谢,你这个好理解多了
假如有多条 (AA,3) 就有问题了。