武汉市最新防疫信息_网络营销理论与实务_友情链接平台网站_汕头网站设计公司

SELECT 'S0001' AS STU,unnest(string_to_array('G,E,O,V,I,N,D,U,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ',')) AS parts;delete from Answer where AnswerSudentId='S0001';
drop table IF EXISTS TempSubString;
CREATE TABLE TempSubString(
id SERIAL,
studentid varchar(5),
subname VARCHAR(150),
PRIMARY KEY(id) 
);insert into TempSubString(studentid,subname) 
SELECT 'S0001' AS STU,unnest(string_to_array('A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ',')) AS parts;
--  每题的得分 考虑，是否存在记录，存在删除，不存在，直接添加 Geovin Du
--  delete from  Answer where AnswerSudentId=@studentid;
insert into Answer(AnswerSudentId,AnswerQuestionId,AnswerStudentResult,AnswerScore) select studentid,id,subname,f_GetAnswerScore(id,subname) as score from TempSubString ; SELECT * FROM Answer;

sql server 2019:

-- 测试结果
DECLARE @studentid CHAR(5),@result NVARCHAR(1000)
set @studentid='S0001';  -- 学生编号
set @result='A,C,D,B,A,D,C,B,C,D,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,C';
EXEC procImportStudentGrade @studentid,@result;

mysql 9.0

set @studentid='S0001';  #学生编号
set @result='A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D';
insert into temptable(studentid,subname)
SELECT @studentid,SUBSTRING_INDEX(SUBSTRING_INDEX(@result,',',help_topic_id+1),',',-1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH(@result)-LENGTH(REPLACE(@result,',',''))+1;SELECT SUBSTRING_INDEX('A,,B,C,D',',',1);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',2);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',-2);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',-1);SELECT SUBSTRING_INDEX('Ann Smith', ' ', 1);
SELECT LOCATE('.', 'www.dusystem.com');set @skills='MySQL,PostgreSQL,SQLite';
SELECT SUBSTRING_INDEX(@skills, ',', 1) AS skill_1,SUBSTRING_INDEX(SUBSTRING_INDEX(@skills, ',', 2), ',', -1) AS skill_2,SUBSTRING_INDEX(SUBSTRING_INDEX(@skills, ',', 3), ',', -1) AS skill_3;