求一复杂Sql语句？

[因为想念才会寂寞]

Sql语句：
if exists (select * from sysobjects where id = OBJECT_ID('[clerk]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [clerk]
CREATE TABLE [clerk] ( [id] [int] IDENTITY (1, 1) NOT NULL , [tk_cl_id] [int] NULL , [clerk_id] [int] NULL , [cj] [int] NULL )

SET IDENTITY_INSERT [clerk] ON

INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 1 , 1 , 1 , 80 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 2 , 1 , 1 , 54 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 3 , 1 , 2 , 50 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 4 , 1 , 4 , 20 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 5 , 1 , 4 , 78 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 6 , 2 , 1 , 12 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 7 , 2 , 3 , 12 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 8 , 4 , 3 , 29 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 9 , 4 , 3 , 98 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 10 , 4 , 3 , 70 )

SET IDENTITY_INSERT [clerk] OFF

实现为：将相同tk_cl_id,clerk_id记录，则取Cj最高的。
使之结果为：
id cj
1 80
6 12
3 50
7 12
9 98
5 78

实现了部分：select max(cj) from clerk
group by tk_cl_id,clerk_id
不知道往下如何进行。。
请教！thanks1

[Limpire]

select id,cj from clerk a where cj = (select max(cj) from clerk where tk_cl_id=a.tk_cl_id and clerk_id=a.clerk_id) order by id

[Limpire]

--方法2
select a.id,a.cj
from clerk a join
(select tk_cl_id,clerk_id,cj=max(cj) from clerk group by tk_cl_id,clerk_id) b
on a.tk_cl_id=b.tk_cl_id and a.clerk_id=b.clerk_id and a.cj=b.cj
order by a.id

[duanzhi1984]

select id,cj from clerk where exists(
select *from (SELECT tk_cl_id,clerk_id ,max(cj) cj FROM clerk group by tk_cl_id,clerk_id ) as A
WHERE A.tk_cl_id=clerk.tk_cl_id AND A.clerk_id=clerk.clerk_id and A.cj=clerk.cj )

结果:
9 98
7 12
6 12
5 78
3 50
1 80

[duanzhi1984]

忘记加ORDER BY id

select id,cj from clerk where exists(
select *from (SELECT tk_cl_id,clerk_id ,max(cj) cj FROM clerk group by tk_cl_id,clerk_id ) as A
WHERE A.tk_cl_id=clerk.tk_cl_id AND A.clerk_id=clerk.clerk_id and A.cj=clerk.cj )
order by id

[yylccy]

好难啊

[因为想念才会寂寞]

长知识了。。。thanks

[dawugui]

select a.* from clerk a,
(select tk_cl_id,clerk_id,max(cj) cj from clerk group by tk_cl_id,clerk_id) b
where a.tk_cl_id = b.tk_cl_id and a.clerk_id = b.clerk_id and a.cj = b.cj
order by a.tk_cl_id,a.clerk_id
drop table clerk

/*
id tk_cl_id clerk_id cj
----------- ----------- ----------- -----------
1 1 1 80
3 1 2 50
5 1 4 78
6 2 1 12
7 2 3 12
9 4 3 98

（所影响的行数为 6 行）
*/

[dawugui]

CREATE TABLE [clerk] ( [id] [int] IDENTITY (1, 1) NOT NULL , [tk_cl_id] [int] NULL , [clerk_id] [int] NULL , [cj] [int] NULL )
SET IDENTITY_INSERT [clerk] ON
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 1 , 1 , 1 , 80 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 2 , 1 , 1 , 54 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 3 , 1 , 2 , 50 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 4 , 1 , 4 , 20 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 5 , 1 , 4 , 78 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 6 , 2 , 1 , 12 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 7 , 2 , 3 , 12 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 8 , 4 , 3 , 29 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 9 , 4 , 3 , 98 )
INSERT [clerk] ( [id] , [tk_cl_id] , [clerk_id] , [cj] ) VALUES ( 10 , 4 , 3 , 70 )
SET IDENTITY_INSERT [clerk] OFF

--方法一
select a.* from clerk a,
(select tk_cl_id,clerk_id,max(cj) cj from clerk group by tk_cl_id,clerk_id) b
where a.tk_cl_id = b.tk_cl_id and a.clerk_id = b.clerk_id and a.cj = b.cj
order by a.tk_cl_id,a.clerk_id
/*
id tk_cl_id clerk_id cj
----------- ----------- ----------- -----------
1 1 1 80
3 1 2 50
5 1 4 78
6 2 1 12
7 2 3 12
9 4 3 98
（所影响的行数为 6 行）
*/
--方法二
select a.* from clerk a where cj = (select max(cj) cj from clerk where tk_cl_id = a.tk_cl_id and clerk_id = a.clerk_id) order by a.tk_cl_id,a.clerk_id
/*
id tk_cl_id clerk_id cj
----------- ----------- ----------- -----------
1 1 1 80
3 1 2 50
5 1 4 78
6 2 1 12
7 2 3 12
9 4 3 98
（所影响的行数为 6 行）
*/
drop table clerk