常用sql整理

$subQuery = function($query)use($patientNo){
     $query -> table('b_health_patient_data')
     -> field('data_no')
     -> where('type','=',9)
     -> where('data_status','=',1)
     -> where('patient_no','=',$patientNo);
};
$list = $healthPatientDataModel
      ->where($maps)
                ->whereOr(function($query)use($patientNo,$subQuery){
              $query->where('type','IN',[5])
       ->where('patient_no', $patientNo)
      ->where('source_data_no', 'NOT IN',$subQuery);})
      ->order(['created_time'=> 'DESC'])
      ->limit(($p - 1) * $size, $size)
      ->select();

UPDATE b_doctor_consult dc
INNER JOIN (
SELECT
c.source_family_id,
f.idcard,
p.patient_no
FROM
b_doctor_consult c
INNER JOIN b_patient_family f ON c.source_family_id = f.id
INNER JOIN b_health_patient p ON f.idcard = p.id_card
WHERE
c.patient_no IS NULL
OR c.patient_no = ''
GROUP BY
c.source_family_id
) tt ON tt.source_family_id = dc.source_family_id
SET dc.patient_no = tt.patient_no;

SELECT
*
FROM
b_patient_doctor tb1,
(
SELECT
group_concat(a.group_name SEPARATOR ',') AS group_names,
b.patient_id
FROM
b_group_doctor a,
b_group_patient b
WHERE
a.group_id = b.group_id
GROUP BY
b.patient_id
) tb2 where tb1.patient_id = tb2.patient_id