मैं एक दृश्य कैसे बना सकता हूं जो नीचे दिए गए SQL के समान परिणाम उत्पन्न करता है। व्यू को इस तरह से किसी के द्वारा प्रयोग करने योग्य होना चाहिए:

SELECT * FROM my_view WHERE merch_id = 'some_value';

वर्तमान SQL, UNION के दोनों ओर WHERE पैरामीटर को स्थानापन्न करने के लिए PHP का उपयोग करता है, लेकिन भाषा कोई मायने नहीं रखती।

यहाँ आधार एसक्यूएल है:

SELECT
    mine.cust_id,
    cust_meta.cust_name,
    mine.bank_id,
    sum(mine.invoices) AS invoices,
    sum(mine.payments) AS payments
FROM (
        SELECT
            cust_id,
            bank_id,
            count(*) AS invoices,
            NULL AS payments
        FROM
            s_bill_meta.t_invoice
        WHERE
            merch_id = '$var'
        GROUP BY
            cust_id,
            bank_id

    UNION

        SELECT
            cust_id,
            bank_id,
            NULL AS invoices,
            count(*) AS payments
        FROM
            s_credit_meta.t_check
        WHERE
            merch_id = '$var'
        GROUP BY
            cust_id,
            bank_id
    ) AS mine
LEFT JOIN
    s_user.t_cust AS cust_meta
        ON mine.cust_id = cust_meta.cust_id
GROUP BY
    mine.cust_id,
    cust_meta.cust_name,
    mine.bank_id
ORDER BY
    mine.cust_id,
    mine.bank_id
1
CXJ 17 जून 2019, 20:19

2 जवाब

सबसे बढ़िया उत्तर

आप एग्रीगेशन कॉलम में merch_id शामिल करेंगे:

SELECT mine.cust_id, cust_meta.cust_name, mine.bank_id, mine.merch_id,
       sum(mine.invoices) AS invoices,
       sum(mine.payments) AS payments
FROM ((SELECT cust_id, bank_id, merch_id, count(*) AS invoices, NULL AS payments
       FROMs_bill_meta.t_invoice
       GROUP BY cust_id, bank_id, merch_id 
      ) UNION ALL
      (SELECT cust_id, bank_id, merch_id, NULL AS invoices, count(*) AS payments
       FROM s_credit_meta.t_check
       GROUP BY cust_id, bank_id, merch_id
      )
    ) mine LEFT JOIN
    s_user.t_cust cust_meta
    ON mine.cust_id = cust_meta.cust_id
GROUP BY mine.cust_id, cust_meta.cust_name, mine.bank_id, mine.merch_id
ORDER BY mine.cust_id, mine.bank_id;
2
Gordon Linoff 17 जून 2019, 20:23

आप फ़ंक्शन का उपयोग कर सकते हैं:

CREATE FUNCTION myview(text) RETURNS TABLE
(
cust_id INT,
...
)
 AS '
    SELECT cust_id, ...
    FROM ...
    WHERE  merch_id = $1
    GROUP BY ...
' LANGUAGE SQL;

और कॉल करें:

SELECT * FROM myview('some_value') AS t1;
2
Lukasz Szozda 17 जून 2019, 20:25