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

2024-05-20 • 问答

我已经准备了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;

您可以按照以下方式执行查询

HTTP 500 error

您可以使用 SELECT

DB::select("your query");

您可以像下面那样传递参数（示例）

SELECT *  FROM `table` WHERE `id` > '$your_param'  ASC LIMIT 1

将leftjoin用于多个表。

然后使用\DB::raw到该子查询。

$sp_sql = \DB::table('stp_partner AS sp')
  ->leftjoin('customer_eligible_partner AS cep','sp.PartnerID','=','cep.PartnerID')
  ->leftjoin('personal_loans AS pl','pl.PLAppId','cep.AppId')
  ->leftjoin('stp_partner_sub_product_mapping AS spm','stp_partner_sub_product_mapping','spm')
  ->where('spm.SubProdCode',1001)
  ->selectRaw("GROUP_CONCAT( 
                 DISTINCT CONCAT( 
                   'MAX(IF( sp.PartnerID= ''',cep.PartnerID,''',\"Yes\",\"No\")) AS ',REPLACE ( PartnerName,' ','' ) 
               )");


\DB::table('customer_eligible_partner AS cep')
  ->leftjoin('stp_partner AS sp','cep.PartnerID','sp.PartnerID')
  ->where('cep.ProdCode',1001)
  ->groupBy('cep_AppId')
  ->select('cep.AppId','cep.ProdCode','cep.CreatedDate',\DB::raw($sp_sql))
  ->get();

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

q5070212 回答：如何在Laravel中执行准备好的语句

大家都在问