pgsql常用复杂语句编写

查询距离现在30天未登陆的用户

SELECT login_flag from sys_user where date_part('day', now()::timestamp - login_date::timestamp)>30  and 
login_date is not null 

按照某列分组排序,分组排序取第一条

WITH usr as (SELECT qry_user_id ,qry_req_tm  from XXX1 
								UNION ALL SELECT qry_user_id,qry_req_tm from XXX2 )
	, target as (select * from (
	SELECT  qry_user_id,qry_req_tm,row_number() over(partition by qry_user_id order by qry_req_tm desc) rn FROM usr 
	where qry_req_tm is not null) t
	where t.rn =1)
  select * from XXX3 A where 
  EXISTS
	(
	SELECT 1 from target B  
	WHERE 
	B.qry_user_id=A.user_id 
	and date_part('day', now()::timestamp - B.qry_req_tm::timestamp)>30 )