分组查询碰到的问题,, ############################ 100% 结贴
表的列数比较多, 我就贴三列出来
Asid State CreateUid
714 未得标1111
715分配完成 660007
716未得标660008
717未得标660007
718未得标660084
719未得标660129
720未得标660007
721未得标660009
722未得标660117
723分配完成 660125
724分配完成 660037
725未得标660010
726未得标666888
727分配完成 660008
728分配完成 660009
729未得标660007
730未得标660008
731未得标660065
732分配完成 660007
现在需要查询的结果是, 按CreateUid 分组,如果只有一条记录在查询范围内,如果有1条记录以上就只查询第二条。
;with ct as (select row_number() over (partition by createUid order by asid) num ,* from AuctionSell)
select * from ct
where num=2 or num=(select max(num) from ct b where createuid=ct.createuid ) AND NUM=1
select * from
(select row_number() over (partition by createUid order by asid) num ,* from AuctionSell) ct
where num=2 or num=(select max(num) from ct b where createuid=ct.createuid ) AND NUM=1
;with ct as
(
select row_number() over(partition by createUid order by asid) row_id
,count(*) over(partition by createUid) row_count,*
from AuctionSell
)
select *
from ct
where row_id=case when row_count>1 then 2 else 1 end