SELECT ep.,e.winPercentage,e.profitPercentage FROM [HD_CMS].[dbo].[E_CMS_Expert_Program] as ep ,[HD_CMS].[dbo].[E_CMS_Expert] as e where e.userId=ep.createUserId
方案一：使用子查询
select ep.,e.winPercentage,e.profitPercentage from ( select * from [HD_CMS].[dbo].[E_CMS_Expert_Program] e where id=( select top 1 id from [HD_CMS].[dbo].[E_CMS_Expert_Program] p where p.createUserId=e.createUserId and bonusStatus=0 order by p.createTime desc ) ) as ep, [HD_CMS].[dbo].[E_CMS_Expert] as e where e.userId=ep.createUserId
方案二：使用ROW_NUMBER() Over(Partition by createUserId order by createTime desc)
select ep.*,e.winPercentage,e.profitPercentage from ( select * from( select *,ROW_NUMBER() Over(Partition by createUserId order by createTime desc) as Rn from [HD_CMS].[dbo].[E_CMS_Expert_Program] where bonusStatus=0 ) w where w.Rn=1 ) as ep, [HD_CMS].[dbo].[E_CMS_Expert] as e where e.userId=ep.createUserId
