[SQL] SQL Join & union 的 Row_number 寫法

  • 639
  • 0
  • SQL
  • 2019-12-12

如標題

搭配牛楠產code模組(程式碼產生器)....主要先有這一段再丟進去處理 ...

(select * from [dbo].[member]  a  
left join memberB b on a.Id = b.refId
where a.alDate < '2019/01/01' and  '2019/10/10' < a.alDate2
union 
select * from [dbo].[member]  a  
left join memberB b on a.Id = b.refId
where  a.alDate2 = '' or a.alDate2 is null )  p

pos  = sql重新產生產生的序號


SELECT * FROM ( 

SELECT * , 
Row_number() OVER(ORDER BY p.Id DESC ) pos 

FROM   
(select * from [dbo].[member]  a  
left join memberB b on a.Id = b.refId
where a.alDate < '2019/01/01' and  '2019/10/10' < a.alDate2
union 
select * from [dbo].[member]  a  
left join memberB b on a.Id = b.refId
where  a.alDate2 = '' or a.alDate2 is null )  p

)  as  sp


WHERE pos BETWEEN 1 AND 5

 

以上文章僅用紀錄資料使用.....