mysql - 从 SQL 到 HQL 连接和删除

Question

让这个 SQL 查询像一个魅力一样工作，想将其转换为 HQL，但有一些困难。

SQL 查询:

DELETE med1
FROM MEDECINES med1, MEDECINES med2
WHERE (
    REPLACE(med1.`MEDECINE_ID`, ' ', '') < REPLACE(med2.`MEDECINE_ID`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_DCI`, ' ', '') = REPLACE(med2.`MEDECINE_DCI`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_MARK`, ' ', '') = REPLACE(med2.`MEDECINE_MARK`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_FORM`, ' ', '') = REPLACE(med2.`MEDECINE_FORM`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_DOSAGE`, ' ', '') = REPLACE(med2.`MEDECINE_DOSAGE`, ' ', '') AND 
    med1.`MEDECINE_REDEEMABILITY` = med2.`MEDECINE_REDEEMABILITY`
);

HQL(到目前为止我能写的):

DELETE FROM Medecine med1
        WHERE EXISTS (
           FROM Medecine med2
           WHERE(
                med1.medecineId < med2.medecineId AND
                REPLACE(med1.medecineDci, ' ', '') = REPLACE(med2.medecineDci, ' ', '') AND
                REPLACE(med1.medecineMark, ' ', '') = REPLACE(med2.medecineMark, ' ', '') AND
                REPLACE(med1.medecineForm, ' ', '') = REPLACE(med2.medecineForm, ' ', '') AND
                REPLACE(med1.medecineDosage, ' ', '') = REPLACE(med2.medecineDosage, ' ', '') AND
                med1.medecineRedeemability = med2.medecineRedeemability
            )
        )

Answer 1

使用EXISTS

DELETE FROM medecines med1
WHERE  EXISTS (SELECT 1
               FROM   medecines med2
               WHERE  REPLACE(med1.medecine_id, ' ', '') <
                      REPLACE(med2.medecine_id, ' ', '')
                      AND REPLACE(med1.medecine_dci, ' ', '') =
                          REPLACE(med2.medecine_dci, ' ', '')
                      AND REPLACE(med1.medecine_mark, ' ', '') =
                          REPLACE(med2.medecine_mark, ' ', '')
                      AND REPLACE(med1.medecine_form, ' ', '') =
                          REPLACE(med2.medecine_form, ' ', '')
                      AND REPLACE(med1.medecine_dosage, ' ', '') =
                          REPLACE(med2.medecine_dosage, ' ', '')
                      AND med1.medecine_redeemability =
                          med2.medecine_redeemability );