求一复杂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
------解决方案--------------------
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
[解决办法]
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 )
结果:
998
712
612
578
350
180
[解决办法]
忘记加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
[解决办法]
好难啊
[解决办法]
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 行）
*/
[解决办法]
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
