MYSQL把查询到数据新建成一张表

create table VisitRemind as (SELECT a.sourceType,a.sourceId,a.gender,a.nowAddress,a.diseaseType,a.dataBirth,a.VISIT_DATE,a.patientId,a.tel,a.`name`,a.dutyUserUuid,a.sourceTable,a.mbType,b.nextDate from VIEWRESIDENTHIST a 
            LEFT JOIN ( SELECT sourceType, patientId , MAX(NEXT_DATE) nextDate FROM VIEWRESIDENTHIST GROUP BY sourceType, patientId)  b 
            on a.sourceType = b.sourceType and  a.patientId = b.patientId and a.NEXT_DATE = b.nextDate where b.nextDate is not null) 

1.create table 表名 as sql语句;

2.更新版本

create table VisitRemind as(            
SELECT
 a.sourceType,
 a.sourceId,
 a.gender,
 a.nowAddress,
 a.diseaseType,
 a.dataBirth,
 a.VISIT_DATE,
 a.patientId,
 a.tel,
 a.`name`,
 a.dutyUserUuid,
 a.sourceTable,
 a.mbType,
 a.INPUT_USER_ID,
 a.DUTY_USER_ID,
 a.NEXT_DATE 
FROM VIEWRESIDENTHIST a 
where 1=1
 and (sourceType, patientId,NEXT_DATE) in ( SELECT sourceType, patientId, MAX( NEXT_DATE ) FROM VIEWRESIDENTHIST GROUP BY sourceType, patientId ))