求助，被两道sql题难到了

[mt351943828]

1.根据更新的时间取最近发布的三个版本号，统计每个版本的下载次数



2.统计该应用最近三个月，每个月的登陆次数


数据库是oracle

[卖水果的net]

数据长什么样？ 结果又要什么样？ 都说一下。

[jinhx]

select a.id,sum(a.download) from mytb a
right join (select top 3 id from mytb order by CREATE_TIME desc)) b on b.id=a.id
group by a.id

[唐诗三百首]

请提供表结构, 测试数据, 处理逻辑和希望结果.

[秦拿希]

WITH LatThreeVersion as(
SELECT top 3 ROW_NUMBER() over (order by create_time desc) num,*
from tableA)
select tableA.ID,tableA.DOWNLOAD 
from tableA 
inner join LatThreeVersion on tableA.ID=LatThreeVersion.ID

--获取最新三个月份
with LatThreeMonth as (
SELECT CONVERT(varchar(6),GETDATE(),112) mon
UNION
select CONVERT(varchar(6),DATEADD(mm,-1,GETDATE()),112)  mon  
UNION
select CONVERT(varchar(6),DATEADD(mm,-2,GETDATE()),112) mon) 
--获取最近三个月份每个月的登录次数（loginTable虚拟）
select count(id),mon 
from ( select loginTable.id, CONVERT(varchar(6),loginTable.loginTime,112) as mon
	     from loginTable
	inner join LatThreeMonth on LatThreeMonth.mon = CONVERT(varchar(6),loginTable.loginTime,112)
)
group by mon

[砸死牛顿的苹果]

select distinct top 3 version,max(create_date)cd,count(1)ct
from tb
group by version
order by cd desc