A B C D E ----------------------------------------- A10340 20 2 1 25 A10344 19 99 7 27 A10345 20 1 3 27 A10345 20 0 7 1根据以A字段为主,根据B,C,D,E次序比较大小,得出唯一A字段的数据纪录 结果: A B C D E ----------------------------------------- A10340 20 2 1 25 A10344 19 99 7 27 A10345 20 1 3 27
select distinct(c1),sl,s2,s3 from tb where s1 in(select max(s1) from tb group by c1)
kersoft 你还没有明白我的意思!!!
Select A,max(right('000'+cast(b as varchar),3)+right('000'+cast(c as varchar),3)......) from Table Group by A结果应该象下面那样吧, A10340 020002001 025 A10344 019099007 027 A10345 020010030027 再稍微处理一下,不知道能不能满足你的要求,
select * into #tab from tab where 1=2 create unique index ind1 on #tab(a) with IGNORE_DUP_KEY insert #tab select * from tab order by a,b desc,c desc,d desc,e desc select * from #tab drop table #tab ------------------------------------------(所影响的行数为 0 行)服务器: 消息 3604,级别 16,状态 1,行 3 已忽略重复的键。 A B C D E ------ ----------- ----------- ----------- ----------- A10340 20 2 1 25 A10344 19 99 7 27 A10345 20 1 3 27(所影响的行数为 3 行)
很简单: select top 1 A from table_name order by B desc, C desc, D desc, E desc
SELECT BCB.* FROM BCB INNER JOIN (SELECT ID, MAX(JCSJ) AS sj FROM BCB GROUP BY ID) bcb2 ON BCB.ID = bcb2.ID AND BCB.JCSJ = bcb2.sj bcb表名
-----------------------------------------
A10340 20 2 1 25
A10344 19 99 7 27
A10345 20 1 3 27
A10345 20 0 7 1根据以A字段为主,根据B,C,D,E次序比较大小,得出唯一A字段的数据纪录
结果:
A B C D E
-----------------------------------------
A10340 20 2 1 25
A10344 19 99 7 27
A10345 20 1 3 27
Group by A结果应该象下面那样吧,
A10340 020002001 025
A10344 019099007 027
A10345 020010030027
再稍微处理一下,不知道能不能满足你的要求,
create unique index ind1 on #tab(a) with IGNORE_DUP_KEY
insert #tab
select * from tab order by a,b desc,c desc,d desc,e desc
select * from #tab
drop table #tab
------------------------------------------(所影响的行数为 0 行)服务器: 消息 3604,级别 16,状态 1,行 3
已忽略重复的键。
A B C D E
------ ----------- ----------- ----------- -----------
A10340 20 2 1 25
A10344 19 99 7 27
A10345 20 1 3 27(所影响的行数为 3 行)
select top 1 A from table_name
order by B desc, C desc, D desc, E desc
FROM BCB INNER JOIN
(SELECT ID, MAX(JCSJ) AS sj
FROM BCB
GROUP BY ID) bcb2 ON BCB.ID = bcb2.ID AND BCB.JCSJ = bcb2.sj
bcb表名