几个函数的使用:
1、GROUP_CONCAT 可以将一个字段的多条数据合并成一条,并用;分隔符分开。可以配合distinct 对合并的数据进行去重。大多数情况要和group_by配合使用。
2、SUBSTRING('字段名','起点索引','字符截取长度'),通过这个函数可以截取查询到的字段数据中的某个子字符串。substring_index('字段名','起点') 起点为正数则是从左往右截至最后的字符,起点为负数则是从后往前查询字符传。
3、存储过程:
DROP PROCEDURE IF EXISTS sp_stationAppLoginControl;
CREATE DEFINER="cnhtc"@"%" PROCEDURE "sp_stationAppLoginControl"()
BEGIN
-- 定义变量
DECLARE flag INT DEFAULT 0;
DECLARE stationCode varchar(32);
-- 查询符合条件的结果用于遍历
DECLARE info CURSOR FOR (SELECT s.SERVICE_STATION_CODE FROM tm_dealer_station as s LEFT JOIN tm_dealer_station_info i on s.SERVICE_STATION_CODE = i.SERVICE_STATION_CODE WHERE i.BUSINESS_SCOPE in (45031001,45031002) group by s.service_station_code);
DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;
-- 开始遍历查询的结果
OPEN info;
fetch info INTO stationCode;
WHILE flag<>1 DO
SET @station_code = stationCode;
if((SELECT count(*) FROM `cnhtc`.`tm_login_user_app` WHERE DEALER_CODE = stationCode) = 0) THEN
INSERT INTO `cnhtc`.`tm_login_user_app`(`USER_CODE`, `DEALER_CODE`, `NEW_VERSION`, `OLD_VERSION`) VALUES (NULL, @station_code, 10041001, 10041001);
END IF;
FETCH info INTO stationCode;
END WHILE;
CLOSE info;
END;
CALL sp_stationAppLoginControl();
CREATE DEFINER="cnhtc"@"%" PROCEDURE "sp_stationAppLoginControl"()
BEGIN
DECLARE flag INT DEFAULT 0;
DECLARE stationCode varchar(32);
DECLARE info CURSOR FOR (SELECT s.SERVICE_STATION_CODE FROM tm_dealer_station as s LEFT JOIN tm_dealer_station_info i on s.SERVICE_STATION_CODE = i.SERVICE_STATION_CODE WHERE i.BUSINESS_SCOPE in (45031001,45031002) group by s.service_station_code);
DECLARE CONTINUE HANDLER FOR NOT found SET flag = 1;
OPEN info;
fetch info INTO stationCode;
-- 判断是否存在符合条件的数据,如果有不执行,如果没有才插入
WHILE flag<>1 DO
SET @station_code = stationCode;
if((SELECT count(*) FROM `cnhtc`.`tm_login_user_app` WHERE DEALER_CODE = stationCode) = 0) THEN
INSERT INTO `cnhtc`.`tm_login_user_app`(`USER_CODE`, `DEALER_CODE`, `NEW_VERSION`, `OLD_VERSION`) VALUES (NULL, @station_code, 10041001, 10041001);
END IF;
FETCH info INTO stationCode;
END WHILE;
CLOSE info;
END