SQL-过滤出最新的记录

在业务查询中经常会出现【过滤出最新一条记录】的需求，下面写一个用【NOT EXISTS】实现的小例子记录一下：

SELECT
  t1.unit_id,
  IFNULL( COUNT(distinct t.person_phr_code ), 0 ) AS visit_person_num,
  IFNULL( COUNT(distinct CASE WHEN t.avg_sbp < 130 AND t.avg_dbp < 80 THEN t.person_phr_code ELSE NULL END ), 0 ) AS person_num_80_130,
  IFNULL( COUNT(distinct CASE WHEN t.avg_sbp < 140 AND t.avg_dbp < 90 THEN t.person_phr_code ELSE NULL END ), 0 ) AS person_num_90_140
FROM
  yida_historical_visit_info t,
  yida_person_basic_info t1 
WHERE
  t.person_phr_code = t1.person_phr_code
  AND NOT EXISTS (
	 SELECT 1  FROM `yida_historical_visit_info` n 
	 WHERE t.person_phr_code = n.person_phr_code AND t.visit_date < n.visit_date)
GROUP BY 
  t1.unit_id

 

转载于:https://my.oschina.net/MinghanSui/blog/3064758