php - 如何在 Laravel 中执行准备好的语句

Question

我已经准备好了 SQL 准备好的语句，当我在 Workbench 中运行它时，它们会给出正确的结果。现在我想在 laravel 中执行它。

SQL 是:

SET SESSION group_concat_max_len = 1000000;

SET @SQL = NULL;
SELECT
    GROUP_CONCAT( DISTINCT CONCAT( 'MAX(IF( sp.PartnerID= ''', cep.PartnerID, ''', "Yes", "No")) AS ', REPLACE ( PartnerName, ' ', '' ) ) ) INTO @SQL 
FROM
    stp_partner sp,
    customer_eligible_partner cep,
    personal_loans pl,
    stp_partner_sub_product_mapping spm 
WHERE
    sp.PartnerID = cep.PartnerID 
    AND pl.PLAppId = cep.AppId 
    AND spm.PartnerID = sp.PartnerID 
    AND spm.SubProdCode = 1001;

SET @SQL = CONCAT( 'SELECT cep.AppId
    , cep.ProdCode
    , cep.CreatedDate, ', @SQL, ' 
    FROM customer_eligible_partner cep, stp_partner AS sp WHERE cep.PartnerID = sp.PartnerID AND cep.ProdCode = "1001" 
GROUP BY cep.AppId' );
PREPARE stmt 
FROM
    @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

您可以像下面这样执行查询

$sql =
        <<<EOF
SET SESSION group_concat_max_len = 1000000;

SET @SQL = NULL;
SELECT
    GROUP_CONCAT( DISTINCT CONCAT( 'MAX(IF( sp.PartnerID= ''', cep.PartnerID, ''', "Yes", "No")) AS ', REPLACE ( PartnerName, ' ', '' ) ) ) INTO @SQL 
FROM
    stp_partner sp,
    customer_eligible_partner cep,
    personal_loans pl,
    stp_partner_sub_product_mapping spm 
WHERE
    sp.PartnerID = cep.PartnerID 
    AND pl.PLAppId = cep.AppId 
    AND spm.PartnerID = sp.PartnerID 
    AND spm.SubProdCode = 1001;

SET @SQL = CONCAT( 'SELECT cep.AppId
    , cep.ProdCode
    , cep.CreatedDate, ', @SQL, ' 
    FROM customer_eligible_partner cep, stp_partner AS sp WHERE cep.PartnerID = sp.PartnerID AND cep.ProdCode = "1001" 
GROUP BY cep.AppId' );
PREPARE stmt 
FROM
    @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
EOF;
\DB::select($sql);