select tt.name as subjectName,path,count(*) as countNum from ?(select **distinct t.table_id_**, t.SUBJECT_ID_, s.name_ as name,path_,SUBSTRING_INDEX(path_,'###',1) as path from model_table t ?join hdgp_standard_subject s on t.SUBJECT_ID_ = s.id_ where t.IS_DELETE_ = 0 and !isnull(t.SUBJECT_ID_) and t.VERSION_TYPE_ = 'formal' order by s.path_ ?) tt group by tt.path ;

select tt.name as subjectName,path,count(*) as countNum from ?(select t.SUBJECT_ID_, s.name_ as name,path_,SUBSTRING_INDEX(path_,'###',1) as path from model_table t ?join hdgp_standard_subject s on t.SUBJECT_ID_ = s.id_ where t.IS_DELETE_ = 0 and !isnull(t.SUBJECT_ID_) and t.VERSION_TYPE_ = 'formal' **group by t.table_id_** order by s.path_ ?) tt group by tt.path ;

SELECT
? ? ss.NAME_ AS subjectName,
? ? count(*) AS countNum
FROM
? ? model_table t
? ? ? ? JOIN hdgp_standard_subject s ON t.SUBJECT_ID_ = s.id_
? ? ? ? JOIN hdgp_standard_subject ss ON SUBSTRING_INDEX( s.path_, '###', 1 ) = ss.CODE_
WHERE
? ? t.IS_DELETE_ = 0
? AND ! isnull( t.SUBJECT_ID_ )
? AND t.VERSION_TYPE_ = 'formal'
GROUP BY
? ? ss.code_

-- rank 第n次重復(fù)， last_patient表中某一重復(fù)字段名稱
SELECT
 t.id,t.patient_id,t.patient_name
FROM (
	select 
		id,patient_id,patient_name,
	  if(@last_patient = a.patient_id,@rank := @rank+1,@rank := 1) AS "rank", -- 判斷 當(dāng)前patient_id與@last_patient是否相等，不相等rank為1，相等時(shí)rank加1，
		@last_patient := a.patient_id
	FROM lis_data_collection a,(SELECT @rank:=0,@last_patient:="") r -- 聲明兩個(gè)變量@rnak及@last_patient，并初始化
	ORDER BY a.id asc
) t 
where rank= 2
ORDER BY id ASC