Mysql 字符解析,该如何处理

Mysql 字符解析
call SelectScore('430000,110000,120000')

数据是这样的

id name value
430000 1 1
110000 2 2
120000 3 3
230000 4 4

CREATE PROCEDURE SelectScore(
s_SchoolProvinceID varchar(200)
)
begin
select
*
from t_tdscore where s_StudentProvinceID in(s_SchoolProvinceID );
end;

s_SchoolProvinceID 它要传入的参数in（ '430000','110000','12000'）格式

查询结果为

id name value
430000 1 1
110000 2 2
120000 3 3

求解析
s_SchoolProvinceID 请问这个格式怎么拼接

------解决方案--------------------

SQL code


declare @T table(c1 varchar(1),c2 int)
insert @T
select 'a',10 union all
select 'b',90 union all
select 'c',102 union all
select 'd',121 union all
select 'e',111

declare @s_SchoolProvinceID varchar(10)
set @s_SchoolProvinceID='b,c,e'
select * from @T where charindex(','+c1+',',','+@s_SchoolProvinceID+',')>0

/*
---- -----------
b    90
c    102
e    111
*/

--mysql 中貌似有个函数INSTR 可以代替sql server 中的charindex

------解决方案--------------------

SQL code

drop table if exists t_tdscore;
drop PROCEDURE  if exists SelectScore;
create table t_tdscore 
(
id varchar(10),
name varchar(10),
value INT
);

insert into t_tdscore values('430000','1',1);
insert into t_tdscore values('110000','2',2);
insert into t_tdscore values('120000','3',3);
insert into t_tdscore values('230000','4',4);


DELIMITER $$
CREATE PROCEDURE SelectScore(
  s_SchoolProvinceID varchar(200)
)
begin
select * from t_tdscore where FIND_IN_SET(id,s_SchoolProvinceID);
END$$


 

DELIMITER ;


call SelectScore('430000,110000,120000');

/*

430000    1    1
110000    2    2
120000    3    3
*/

Mysql 字符解析,该如何处理

相关推荐