mimic数据库提取（Navicat）

1、根据英文查询对应编码，~* 可以忽略字符串长度进行匹配

select icd9_code,long_title from mimiciii.d_icd_diagnoses where long_title ~* 'myocardial'

 也可以用LIKE

//long_title中以Tub开头的数据
select * from mimiciii.d_icd_diagnoses where long_title like 'Tub%'  
//long_title中含有pne的数据
select * from mimiciii.d_icd_diagnoses where long_title like '%pne%'
//long_title中第二个位置和第三个位置是ul的数据
select * from mimiciii.d_icd_diagnoses where long_title like '_ul%'
//long_title中以P开头的字符长度大于3的数据
select * from mimiciii.d_icd_diagnoses where long_title like 'P_%_%'
//long_title中以A结尾的数据
select * from mimiciii.d_icd_diagnoses where long_title like '%A'
//long_title中a在第二个位置上且以A结尾的数据
select * from mimiciii.d_icd_diagnoses where long_title like '_a%A'
//long_title中以B开头，以s结尾且长度是10的数据
select * from mimiciii.d_icd_diagnoses where long_title like 'B________s'

 2、查询条件有多个

同一列数据有多个限制条件：

select * from mimiciii.diagnoses_icd where icd9_code in('486','40301','7100')

 不同列数据有多个限制条件（且）：

select * from mimiciii.admissions where subject_id = '22' and hadm_id = '165315'

 3、给表和字段取别名，增强可读性

select subject_id,dob as death_time from mimiciii.patients  //as也可以省略

 4、连接多个表

mimic一般只会用到左连，将患者的诊断信息表与其他表相连

left join 会返回左表所有内容以及根据左表筛选出来的右表的内容

select t1.subject_id,t1.hadm_id,t1.icd9_code,t2.gender,t2.death_time
from mimiciii.diagnoses_icd as t1
left join mimiciii.patients as t2
on t1.subject_id = t2.subject_id
where t1.icd9_code in('486','40301','7100')

5、筛选再筛选，根据icd9_code合并两个表得到得某种病患者的subject_id，hadm_id

WITH d_icd_diagnoses_view AS ( 
	SELECT icd9_code, long_title 
	FROM d_icd_diagnoses 
	WHERE long_title ~* 'Acute myocardial infarction' 
)
SELECT t1.subject_id,t1.hadm_id,t1.icd9_code,t2.long_title 
FROM diagnoses_icd AS t1
INNER JOIN d_icd_diagnoses_view AS t2 
ON t1.icd9_code = t2.icd9_code 
GROUP BY t1.subject_id,t1.hadm_id,t1.icd9_code,t2.long_title

6、删除重复的数据

select DISTINCT label from label_view