表A ,字段xh,id,f01,如何得到相同id下，xh最大的和倒数第二大的f01相同的数据？
例如：xh id f01
1 ,001, 15
2 ,001, 10
3 ,001, 10
1 ,002, 10
1 ,003, 5
2 ,003, 10
结果应为001
------解决方案--------------------
with a(xh,id,f01)as(
select 1,'001',15 union
select 2,'001',10 union
select 3,'001',10 union
select 1,'002',10 union
select 1,'003',5 union
select 2,'003',10
),b as(
select num=ROW_NUMBER() over(partition by id order by xh desc),*,
(select COUNT(1) from a where a1.id=id)countt from a a1)
select xh,id,f01 from b b1
where countt>=2 and num in(1,2) and
exists (select 1 from b where id=b1.id+1 and f01=b1.f01)
order by xh
------解决方案--------------------
exists (select 1 from b where id=b1.id+1 and f01=b1.f01)
要改成
exists (select 1 from b where id=b1.id and num=b1.num+1 and f01=b1.f01)
完整的
with a(xh,id,f01)as(
select 1,'a',15 union
select 2,'a',10 union
select 3,'a',10 union
select 1,'b',10 union
select 1,'c',5 union
select 2,'c',10
)
,b as(
select num=ROW_NUMBER() over(partition by id order by xh desc),*,
(select COUNT(1) from a where a1.id=id)countt from a a1
)
select xh,id,f01 from b b1
where countt>=2 and num in(1,2) and
exists (select 1 from b where id=b1.id and num=b1.num+1 and f01=b1.f01)
order by xh
------解决方案--------------------
WITH t (xh,id,f01) AS
(
SELECT 1 ,001, 15 UNION ALL
SELECT 2 ,001, 10 UNION ALL
SELECT 3 ,001, 10 UNION ALL
SELECT 1 ,002, 10 UNION ALL
SELECT 1 ,003, 5 UNION ALL
SELECT 2 ,003, 10
)
SELECT *FROM t WHERE EXISTS ( SELECT id FROM t AS ta WHERE ta.f01=t.f01 AND ta.id=t.id AND ta.xh-1=t.xh)
------解决方案--------------------
create table #test (xh int,id varchar(30),f01 int)
insert into #test
select 1 ,'001', 15 union all
select 2 ,'001', 10 union all
select 3 ,'001', 10 union all
select 1 ,'002', 10 union all
select 1 ,'003', 5 union all
select 2 ,'003', 10
--select * from #test
;with sel as(
select *,row_number() over(partition by id order by xh desc) as rn from #test
)
select a.id from sel a
join sel b on a.id=b.id and a.rn=1 and b.rn=2 and a.f01=b.f01