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
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 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
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
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 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 行)
*/
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