Question

我有一个多重请求我想做一个简单的请求

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 160 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
AND NOT EXISTS (
    SELECT * FROM SERVICEPAYANT_CLIENT sp
    WHERE sp.RE_ID = CLIENT_ID AND TYPE_DONNEES = 160)
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 161 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
AND NOT EXISTS (
    SELECT * FROM SERVICEPAYANT_CLIENT sp
    WHERE sp.RE_ID = CLIENT_ID AND TYPE_DONNEES = 161)
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 162 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
AND NOT EXISTS (
    SELECT * FROM SERVICEPAYANT_CLIENT sp
    WHERE sp.RE_ID = CLIENT_ID AND TYPE_DONNEES = 162)
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 163 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
AND NOT EXISTS (
    SELECT * FROM SERVICEPAYANT_CLIENT sp
    WHERE sp.RE_ID = CLIENT_ID AND TYPE_DONNEES = 163)
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

Answer 1

我认为可以简化如下:

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT  DISTINCT 
        CLIENT_ID, 
        t.TYPE_DONNEES
FROM    REFERENTIEL r, 
        CLIENT_APPLICATIF ca,
        (   SELECT  160 AS Type_Donnees FROM Dual UNION ALL
            SELECT  161 AS Type_Donnees FROM Dual UNION ALL
            SELECT  162 AS Type_Donnees FROM Dual UNION ALL
            SELECT  163 AS Type_Donnees FROM Dual 
        ) t
WHERE   r.ID = ca.ID_REFERENTIEL
AND     r.TYPE = 1
AND     NOT EXISTS 
        (   SELECT  1
            FROM    SERVICEPAYANT_CLIENT sp
            WHERE   sp.RE_ID = CLIENT_ID 
            AND     t.TYPE_DONNEES = sp.TYPE_DONNEES
        )

Answer 2

单加一个插件应有助于:

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, T.TYPE_DONNEES
  FROM (SELECT 160 TYPE_DONNEES FROM dual UNION ALL
        SELECT 161 FROM dual UNION ALL
        SELECT 162 FROM dual UNION ALL
        SELECT 163 FROM dual) t,
       REFERENTIEL r,
       CLIENT_APPLICATIF ca
 WHERE r.ID = ca.ID_REFERENTIEL
   AND r.TYPE = 1
   AND NOT EXISTS (SELECT *
                     FROM SERVICEPAYANT_CLIENT sp
                    WHERE sp.RE_ID = CLIENT_ID
                      AND TYPE_DONNEES = T.TYPE_DONNEES)
 GROUP BY T.TYPE_DONNEES, CLIENT_ID
/* HAVING COUNT(*) > 0 (unnecessary) */;

友情链接