mysql动态传参实例

SET @salesman_name ='陈奕迅';
-- SELECT * FROM `ls_customer` WHERE relename =@salesman_name;
-- SELECT id FROM `ls_customer` WHERE relename =@salesman_name;
-- SELECT * FROM`ls_salesman` where customer_id='100697'
SELECT* FROM  `ls_salesman_customer`  WHERE salesman_id =(SELECT id FROM`ls_salesman` WHERE customer_id=(SELECT id FROM `ls_customer` WHERE relename =@salesman_name)) AND del_flag=0;


SET @customer_name ='脑袋疼';
SET @salesman_name ='陈奕迅';

SELECT* FROM  `ls_salesman_customer`  WHERE salesman_id =(SELECT id FROM`ls_salesman` WHERE customer_id=(SELECT id FROM `ls_customer` WHERE relename =@salesman_name)) AND del_flag=0;

SET @SQL = CONCAT_WS('SELECT* FROM  `ls_salesman_customer`  WHERE salesman_id =(SELECT id FROM`ls_salesman` WHERE 
customer_id=(SELECT id FROM `ls_customer` WHERE relename =',@salesman_name,' ','and mobile=',@phone,' ))','AND del_flag=0;');
PREPARE test FROM @SQL;
EXECUTE test;
DEALLOCATE PREPARE test;
#查看业务员私海用户;
#查看业务员私海用户;
SET @salesman_name ='蔡华飞业务员';   -- 这里不可以注入
SET @phone='';
SET @test=CONCAT('concat(relename,mobile)','like "%',IF(@salesman_name ='',@phone,@salesman_name),'%"');
SET @SQL = CONCAT('SELECT* FROM  `ls_salesman_customer`  WHERE salesman_id in (SELECT id FROM`ls_salesman` WHERE 
customer_id in (SELECT id FROM `ls_customer` WHERE', ' ',@test,' ))','AND del_flag=0');  -- 这里可以注入
SELECT @SQL;
PREPARE test FROM @SQL;
EXECUTE test;
DEALLOCATE PREPARE test;
#查看客户名称电话查看业务员;
SET @phone='13500000552';
SET @name='弟弟哦';
SET @customer_2 = CONCAT('concat(customer_name,phone)','like','"%',CASE WHEN @name='' THEN @phone
WHEN @name!='' THEN @name END ,'%"');
SET @customer = CONCAT('concat(customer_name,phone)','like','"%',IF(@name='',@phone,@name),'%"');
SELECT @customer;
SET @sql = CONCAT('SELECT * FROM
`ls_customer` WHERE id IN(
SELECT customer_id FROM  `ls_salesman` WHERE id IN(
SELECT salesman_id FROM  `ls_salesman_customer` WHERE customer_id IN(
SELECT customer_id FROM
`customer_license_info` WHERE',' ',@customer,')))');
SELECT @sql;
PREPARE query_salesman FROM @sql;
EXECUTE query_salesman;
DEALLOCATE PREPARE query_salesman;


#查看客户关联业务员关系
SELECT* FROM  `ls_salesman_customer` WHERE customer_id=(SELECT customer_id FROM`customer_license_info` WHERE customer_name=@customer_name);
SELECT* FROM  `ls_salesman_customer` WHERE customer_id=(SELECT customer_id FROM`customer_license_info` WHERE customer_name='解忧杂货店');


#拜访记录排序
SET @salesman_name ='陈奕迅';

SELECT * FROM
`sm_visit` WHERE visit_person_id=(SELECT id FROM `ls_customer` WHERE relename =@salesman_name) ORDER BY visit_end_time DESC