SQL：
SELECT
*
FROM
(
SELECT
(
(
SELECT
COUNT(DISTINCT id)
FROM
eng_cf_item
WHERE
cfid = a.id
) - (
SELECT
COUNT(DISTINCT cfm1.detail_id)
FROM
eng_cf_message cfm1
WHERE
cfm1.cf_id = a.id
AND cfm1.TYPE = '溶媒'
AND cfm1.message = '溶媒'
)
) drug_count,
(
SELECT
COUNT(DISTINCT cfi.drug_name)
FROM
eng_cf_item cfi
LEFT JOIN his_product p ON p.id = cfi.drug
WHERE
cfi.cfid = a.id
AND p.ddd > 0
AND p.flag1 > 0
) anti_count,
(
SELECT
COUNT(DISTINCT drug_name_bak)
FROM
eng_cf_item
WHERE
cfid = a.id
) drug_count1,
(
SELECT
COUNT(0)
FROM
eng_cf_item i,
his_product p
WHERE
cfid = a.id
AND i.drug = p.id
AND p.flag4 > '1'
) j,
(
SELECT
GROUP_CONCAT(
TYPE,
':',
message,
' ' SEPARATOR ' '
)
FROM
eng_cf_dp_message
WHERE
pres_no = a.pres_no
AND dp_id = p.id
) dp_message,
a.id,
a.patient_no,
a.pres_no,
a.pres_date_time,
a.department,
a.age,
a.weight,
a.diagnose,
a.total_amount,
a.doc_name,
a.pharm_audt_name,
a.pharm_chk_name,
a.flag1,
a.flag2,
p. STATUS,
a. NAME,
a.pres_date
FROM
eng_cf a,
eng_cf_dp p
WHERE
1 = 1
AND p.pres_no = a.pres_no
AND pres_date_time >= ? EcfrqBegin
AND pres_date_time <= CONCAT(? EcfrqEnd, 'z')
AND a.department IN ? ItreeDepartment
AND doc_name = ? EdocName
AND (p.flag != '0' OR p.flag IS NULL)
AND a.patient_no =? EpatientNo clause3
AND flag2 =? Eflag2
AND flag3 = ? Eflag3
ORDER BY
random,
pres_date DESC
) z
ORDER BY
pres_date_time ? EsortOrder
lukaidi
commented
6 years ago
SQL： SELECT * FROM ( SELECT ( ( SELECT COUNT(DISTINCT id) FROM eng_cf_item WHERE cfid = a.id ) - ( SELECT COUNT(DISTINCT cfm1.detail_id) FROM eng_cf_message cfm1 WHERE cfm1.cf_id = a.id AND cfm1.TYPE = '溶媒' AND cfm1.message = '溶媒' ) ) drug_count, ( SELECT COUNT(DISTINCT cfi.drug_name) FROM eng_cf_item cfi LEFT JOIN his_product p ON p.id = cfi.drug WHERE cfi.cfid = a.id AND p.ddd > 0 AND p.flag1 > 0 ) anti_count, ( SELECT COUNT(DISTINCT drug_name_bak) FROM eng_cf_item WHERE cfid = a.id ) drug_count1, ( SELECT COUNT(0) FROM eng_cf_item i, his_product p WHERE cfid = a.id AND i.drug = p.id AND p.flag4 > '1' ) j, ( SELECT GROUP_CONCAT( TYPE, ':', message, '
' SEPARATOR ' ' ) FROM eng_cf_dp_message WHERE pres_no = a.pres_no AND dp_id = p.id ) dp_message, a.id, a.patient_no, a.pres_no, a.pres_date_time, a.department, a.age, a.weight, a.diagnose, a.total_amount, a.doc_name, a.pharm_audt_name, a.pharm_chk_name, a.flag1, a.flag2, p. STATUS, a. NAME, a.pres_date FROM eng_cf a, eng_cf_dp p WHERE 1 = 1 AND p.pres_no = a.pres_no AND pres_date_time >= ? EcfrqBegin AND pres_date_time <= CONCAT(? EcfrqEnd, 'z') AND a.department IN ? ItreeDepartment AND doc_name = ? EdocName AND (p.flag != '0' OR p.flag IS NULL) AND a.patient_no =? EpatientNo clause3 AND flag2 =? Eflag2 AND flag3 = ? Eflag3 ORDER BY random, pres_date DESC ) z ORDER BY pres_date_time ? EsortOrder