我在过程中创建了一个动态查询.但是,需要连接多个字符串.在这一点上我使用了CONCAT()
函数.但结果总是返回0.
MyQuery:
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSearchCounts`(IN fDate varchar(20), IN tDate varchar(20), IN byName varchar(255), IN byInn varchar(255), IN byTrademark varchar(255), IN byDist varchar(255), IN byDf varchar(255), IN byDFG varchar(255), IN byDt varchar(255), IN byDTG varchar(255), IN byCompany varchar(255), IN byMf varchar(255), IN byCountry varchar(255), IN isActive tinyint(1), IN isDeleted tinyint(1))
BEGIN
DECLARE qWhere varchar(255);
DECLARE lJoin varchar(255);
DECLARE gBy varchar(255);
set qWhere = '';
SET lJoin = '';
set gBy = ' dr.drug_id';
IF (byName != '') THEN
set qWhere := CONCAT(' AND dr.drug_id IN(', byName, ')');
END IF;
IF (byInn != '') THEN
SET qWhere = CONCAT(@qWhere, ' AND d.di_id IN (', byInn, ')');
SET gBy = CONCAT(gBy, ', d.di_id');
END IF;
IF (byTrademark != '') THEN
SET qWhere = CONCAT(qWhere, ' AND d.trademark_id IN (', byTrademark, ')');
set gBy = CONCAT(gBy, ', d.trademark_id');
END IF;
IF (byDist != '') THEN
set qWhere = CONCAT(qWhere, ' AND dr.m40d_id IN (', byDist, ')');
set gBy = CONCAT(gBy, ', dr.m40d_id');
END IF;
IF (byDf != '') THEN
set qWhere = CONCAT(qWhere, ' AND d.df_id IN (', byDf, ')');
set gBy = CONCAT(gBy, ', d.df_id');
END IF;
IF (byDFG != '') THEN
set qWhere = CONCAT(qWhere, ' AND d.dfg_id IN (', byDFG, ')');
set gBy = CONCAT(gBy, ', d.dfg_id');
END IF;
IF (byDt != '') THEN
set qWhere = CONCAT(qWhere, ' AND d.dt_id IN (', byDt, ')');
set gBy = CONCAT(gBy, ', d.dt_id');
END IF;
IF (byDTG != '') THEN
set qWhere = CONCAT(qWhere, ' AND d.dtg_id IN (', byDTG, ')');
set gBy = CONCAT(gBy, ', d.dtg_id');
END IF;
IF (byMf != '') THEN
set qWhere = CONCAT(qWhere, ' AND dr.mf_id IN (', byMf, ')');
set gBy = CONCAT(gBy, ', dr.mf_id');
END IF;
IF (byCompany != '') THEN
set qWhere = CONCAT(qWhere, ' AND dr.sc_id IN (', byCompany, ')');
set gBy = CONCAT(gBy, ', dr.sc_id');
END IF;
IF (byCountry != '') THEN
SET lJoin = ' LEFT JOIN manufacturers as mf ON dr.mf_id = mf.id ';
set qWhere = CONCAT(qWhere, ' AND mf.country_id IN (', byDist, ')');
set gBy = CONCAT(gBy, ', mf.country_id');
END IF;
SET @q = CONCAT('SELECT COUNT(resData) AS counts FROM (SELECT dr.drug_id AS resData FROM drug_reports dr
LEFT JOIN drugs as d ON dr.drug_id = d.id ', lJoin,
' WHERE dr.mode_40_date BETWEEN STR_TO_DATE("', fDate, '", "%m.%d.%Y") AND STR_TO_DATE("', tDate, '", "%m.%d.%Y") AND dr.is_active = ', isActive, ' AND dr.is_deleted = ', isDeleted, qWhere,' GROUP BY ', gBy, ' ) AS dd');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
如果我使用SELECT @q;
,则返回:
"SELECT COUNT(resData) AS counts FROM (SELECT dr.drug_id AS resData FROM drug_reports dr
LEFT JOIN drugs as d ON dr.drug_id = d.id LEFT JOIN manufacturers as mf ON dr.mf_id = mf.id WHERE dr.mode_40_date BETWEEN STR_TO_DATE(""01.01.2018"", ""%m.%d.%Y"") AND STR_TO_DATE(""01.01.2023"", ""%m.%d.%Y"") AND dr.is_active = 1 AND dr.is_deleted = 0 AND dr.drug_id IN(4468,4469) AND d.di_id IN ('') AND d.trademark_id IN ('') AND dr.m40d_id IN ('') AND d.df_id IN ('') AND d.dfg_id IN ('') AND d.dt_id IN ('') AND d.dtg_id IN ('') AND dr.mf_id IN ('') AND dr.sc_id IN ('') AND mf.country_id IN ('') GROUP BY dr.drug_id, d.di_id, d.trademark_id, dr.m40d_id, d.df_id, d.dfg_id, d.dt_id, d.dtg_id, dr.mf_id, dr.sc_id, mf.country_id ) AS dd"
它始终返回0.我还使用了@var.然而,在这种情况下,它只起作用一次.在其余时间,它返回0. 如何将多个字符串连接成一个变量?