MySQL排序SQL漏洞，必看！！！踩坑无数次了

参数排序方法，只能作用于临时表，不能作用于join的结果。不然报错！！！   

正确用法如下：

   set @p = null; set @r = 0;

    select record_id,doctor_name,doctor_guid, rank,create_date
    from
    ( 
               SELECT
                  s.*,
                  if(@p=s.record_id,@r:=@r+1,@r:=1) as rank,
                 @p:=s.record_id
              from
            (
                   select a.record_id,a.doctor_name,a.doctor_guid,create_date,a.id
                   from hmcdss2.mt_patient_record_doctor a
                   join rp_update_gc_patient_info b on a.record_id=b.record_id
                   where doctor_type = 4 and source_type=1
            )s
             order by s.record_id,s.id desc
   ) t
  where rank = 1

错误用法：

   set @p = null; set @r = 0;

   select
   record_id, doctor_name, doctor_guid
   from

   (

    select

            a.record_id,a.doctor_name,a.doctor_guid,
            if(@p=a.record_id,@r:=@r+1,@r:=1) as rank,
           @p:=a.record_id
     from hmcdss2.mt_patient_record_doctor a
     join rp_update_gc_patient_info b on a.record_id=b.record_id
    where doctor_type = 2
    order by a.record_id,create_date desc
   ) t

   where rank = 1