表a
YL WY CNT
10 10 0
20 10 0
30 10 0
20 10 0
8 10 0
20 6 1
30 7 1
40 8 1
30 20 1
10 5 1
.. .. ..
.. .. ..
我要找到相同的cnt下,最大的YL及其WY,CNT够成的表
也就是结果为:
YL WY CNT
30 10 0
40 8 1
.. .. ..
YL WY CNT
10 10 0
20 10 0
30 10 0
20 10 0
8 10 0
20 6 1
30 7 1
40 8 1
30 20 1
10 5 1
.. .. ..
.. .. ..
我要找到相同的cnt下,最大的YL及其WY,CNT够成的表
也就是结果为:
YL WY CNT
30 10 0
40 8 1
.. .. ..
from a b
where not existst(select 1 from a and a.cnt = b.cnt and a.al > b.al)
INSERT @TB
SELECT 10, 10, 0 UNION ALL
SELECT 20, 10, 0 UNION ALL
SELECT 30, 10, 0 UNION ALL
SELECT 20, 10, 0 UNION ALL
SELECT 8, 10, 0 UNION ALL
SELECT 20, 6, 1 UNION ALL
SELECT 30, 7, 1 UNION ALL
SELECT 40, 8, 1 UNION ALL
SELECT 30, 20, 1 UNION ALL
SELECT 10, 5, 1SELECT MAX(YL) AS YL,MAX(WY) AS WY,CNT FROM @TB
GROUP BY CNT
/*
YL WY CNT
----------- ----------- -----------
30 10 0
40 20 1
*/
*
from
a t
where
not exists(select 1 from a where cnt=t.cnt and yl>t.yl)
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (YL int,WY int,CNT int)
insert into #a
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1select a.* from #a as a
inner join (select max(yl) as yl,cnt from #a group by cnt) as b on a.cnt=b.cnt and a.yl=b.yl
order by a.Yl
/*
YL WY CNT
----------- ----------- -----------
30 10 0
40 8 1*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([YL] int,[WY] int,[CNT] int)
insert [a]
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1
---查询---
select
*
from
a t
where
not exists(select 1 from a where cnt=t.cnt and yl>t.yl)
---结果---
YL WY CNT
----------- ----------- -----------
30 10 0
40 8 1(所影响的行数为 2 行)
SELECT * FROM A,
(SELECT MAX(YL)YL,CNT FROM A GROUP BY CNT) T
WHERE A.YL=T.YL AND T.CNT =A.CNT
create table [a]([YL] int,[WY] int,[CNT] int)
insert [a]
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1SELECT A.* FROM A,
(SELECT MAX(YL)YL,CNT FROM A GROUP BY CNT) T
WHERE A.YL=T.YL AND T.CNT =A.CNT
ORDER BY T.YL
YL WY CNT
----------- ----------- -----------
30 10 0
40 8 1(所影响的行数为 2 行)
if object_id('tempdb.dbo.TA') is not null drop table TA
create table TA (YL int,WY int,CNT int)
insert into TA
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1SELECT * FROM TA T WHERE NOT EXISTS(SELECT 1 FROM TA WHERE CNT=T.CNT AND YL>T.YL)/*
YL WY CNT
----------- ----------- -----------
30 10 0
40 20 1
*/
if object_id('tempdb.dbo.TA') is not null drop table TA
create table TA (YL int,WY int,CNT int)
insert into TA
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1SELECT * FROM TA T WHERE NOT EXISTS(SELECT 1 FROM TA WHERE CNT=T.CNT AND YL>T.YL)/*
YL WY CNT
----------- ----------- -----------
30 10 0
40 8 1*/
insert into TA
select 10,10,0 union all
select 20,10,0 union all
select 30,10,0 union all
select 20,10,0 union all
select 8,10,0 union all
select 20,6,1 union all
select 30,7,1 union all
select 40,8,1 union all
select 30,20,1 union all
select 10,5,1 SELECT * FROM TA T WHERE NOT EXISTS(SELECT 1 FROM TA WHERE CNT=T.CNT AND YL>T.YL)
drop table TA
/*
YL WY CNT
----------- ----------- -----------
30 10 0
40 8 1 */
select
yl,wy,cnt
from
表a A
where
not existst(select 1 from 表a where a.cnt = cnt and a.al > b.al)
INSERT @TB
SELECT 10, 10, 0 UNION ALL
SELECT 20, 10, 0 UNION ALL
SELECT 30, 10, 0 UNION ALL
SELECT 20, 10, 0 UNION ALL
SELECT 8, 10, 0 UNION ALL
SELECT 20, 6, 1 UNION ALL
SELECT 30, 7, 1 UNION ALL
SELECT 40, 8, 1 UNION ALL
SELECT 30, 20, 1 UNION ALL
SELECT 10, 5, 1SELECT MAX(YL) AS YL,MAX(WY) AS WY,CNT FROM @TB
GROUP BY CNT
/*
这个就行了。