MySQL-一段SQL代码

① 疫苗接种筛选

-- 查三针
SELECT 
a.id id,a.`name` '姓名',a.tnum '编号',a.id_card '身份证号码',a.hk_address '户口地址',a.zh_address '暂住地址',a.phone '手机号码',a.ptype '人员类型',a.jwh '居委会',
GROUP_CONCAT(b.kind ORDER BY b.kind DESC) jzym,GROUP_CONCAT(b.v_time ORDER BY b.v_time DESC) '接种时间',
b.num '受种者编号',b.bar_code '条码编号',b.birthdate '出身日期'
FROM target08 AS a LEFT JOIN resource_v2 AS b ON a.`name`=b.`name` And LEFT(a.id_card,4) = LEFT(b.id_card,4) AND RIGHT(a.id_card,2) = RIGHT(b.id_card,2) GROUP BY a.id_card,a.`name` HAVING jzym LIKE '%新型冠状疫苗③%' ORDER BY a.id;
 
-- 查二针
-- SELECT a.`name` '姓名',GROUP_CONCAT(b.kind) jzym,GROUP_CONCAT(b.v_time) '接种时间',a.*,b.*
SELECT 
a.id id,a.`name` '姓名',a.tnum '编号',a.id_card '身份证号码',a.hk_address '户口地址',a.zh_address '暂住地址',a.phone '手机号码',a.ptype '人员类型',a.jwh '居委会',
GROUP_CONCAT(b.kind ORDER BY b.kind DESC) jzym,GROUP_CONCAT(b.v_time ORDER BY b.v_time DESC) '接种时间',
b.num '受种者编号',b.bar_code '条码编号',b.birthdate '出身日期'
FROM target08 AS a LEFT JOIN resource_v2 AS b ON a.`name`=b.`name` And LEFT(a.id_card,4) = LEFT(b.id_card,4) AND RIGHT(a.id_card,2) = RIGHT(b.id_card,2) GROUP BY a.id_card,a.`name` HAVING jzym LIKE '%新型冠状疫苗②%' and NOT INSTR(jzym,'③') ORDER BY a.id;
 
-- 查一针
-- SELECT a.`name` '姓名',GROUP_CONCAT(b.kind) jzym,GROUP_CONCAT(b.v_time) '接种时间',a.*,b.*
SELECT 
a.id id,a.`name` '姓名',a.tnum '编号',a.id_card '身份证号码',a.hk_address '户口地址',a.zh_address '暂住地址',a.phone '手机号码',a.ptype '人员类型',a.jwh '居委会',
GROUP_CONCAT(b.kind ORDER BY b.kind DESC) jzym,GROUP_CONCAT(b.v_time ORDER BY b.v_time DESC) '接种时间',
b.num '受种者编号',b.bar_code '条码编号',b.birthdate '出身日期'
FROM target08 AS a LEFT JOIN resource_v2 AS b ON a.`name`=b.`name` And LEFT(a.id_card,4) = LEFT(b.id_card,4) AND RIGHT(a.id_card,2) = RIGHT(b.id_card,2) GROUP BY a.id_card,a.`name` HAVING jzym not LIKE '%新型冠状疫苗②%' and jzym NOT LIKE '%③%' ORDER BY a.id;
 
-- 查没打针
-- SELECT a.`name` '姓名',GROUP_CONCAT(b.kind) jzym,GROUP_CONCAT(b.v_time) '接种时间',a.*,b.*
SELECT 
a.id id,a.`name` '姓名',a.tnum '编号',a.id_card '身份证号码',a.hk_address '户口地址',a.zh_address '暂住地址',a.phone '手机号码',a.ptype '人员类型',a.jwh '居委会',
GROUP_CONCAT(b.kind ORDER BY b.kind DESC) jzym,GROUP_CONCAT(b.v_time ORDER BY b.v_time DESC) '接种时间',
b.num '受种者编号',b.bar_code '条码编号',b.birthdate '出身日期'
FROM target08 AS a LEFT JOIN resource_v2 AS b ON a.`name`=b.`name` And LEFT(a.id_card,4) = LEFT(b.id_card,4) AND RIGHT(a.id_card,2) = RIGHT(b.id_card,2) GROUP BY a.id_card,a.`name` HAVING ISNULL(jzym) ORDER BY a.id;
 
-- 总和
-- SELECT a.`name` '姓名',GROUP_CONCAT(b.kind) jzym,GROUP_CONCAT(b.v_time) '接种时间',a.*,b.*
SELECT 
a.id id,a.`name` '姓名',a.tnum '编号',a.id_card '身份证号码',a.hk_address '户口地址',a.zh_address '暂住地址',a.phone '手机号码',a.ptype '人员类型',a.jwh '居委会',
GROUP_CONCAT(b.kind ORDER BY b.kind DESC) jzym,GROUP_CONCAT(b.v_time ORDER BY b.v_time DESC) '接种时间',
b.num '受种者编号',b.bar_code '条码编号',b.birthdate '出身日期'
FROM target08 AS a LEFT JOIN resource_v2 AS b ON a.`name`=b.`name` And LEFT(a.id_card,4) = LEFT(b.id_card,4) AND RIGHT(a.id_card,2) = RIGHT(b.id_card,2) GROUP BY a.id_card,a.`name` ORDER BY a.id;

② 字符串截取+替换

SELECT SUBSTRING_INDEX(replace(replace('[你好#世界]','[',''),']',''), '#', 1) as publisher;
 
SELECT SUBSTRING_INDEX('[你好#世界]', '#', 1) as publisher;