PostGresql with 函数使用心得，多条update语句并接执行

t_student表记录学生每一次考试情况，要求保留历史记录，last_exam =0标识为最新的考试情况
t_subjects表记录每一次考试的详情，试题库地址，审题人批示等信息。

建表,

DROP TABLE t_student;
CREATE TABLE t_student(
	id serial8,
	name VARCHAR(20) NOT NULL DEFAULT '',
	class VARCHAR(20) NOT NULL DEFAULT '',
	course VARCHAR[] NOT NULL DEFAULT '{语文,数学,英语,物理,化学,历史,生物,地理,综合}',
	subjects integer[] NOT NULL DEFAULT '{}',
	gross_score int4 NOT NULL DEFAULT 0,
	last_exam int2 NOT NULL DEFAULT 0,
	add_id BIGINT NOT NULL DEFAULT 0,
	add_time TIMESTAMP NOT NULL DEFAULT now(),
	modify_id BIGINT,
	modify_time TIMESTAMP,
	PRIMARY KEY(id)
);
COMMENT ON COLUMN t_student.name IS '用户名';
COMMENT ON COLUMN t_student.class IS '年级';
COMMENT ON COLUMN t_student.course IS '各科目';
COMMENT ON COLUMN t_student.subjects IS '各科目分数';
COMMENT ON COLUMN t_student.gross_score IS '总分数';
COMMENT ON COLUMN t_student.last_exam IS '最新考试标识，0-是，1-不是';
COMMENT ON COLUMN t_student.add_id IS '增加记录人';
COMMENT ON COLUMN t_student.add_time IS '增加日期';
COMMENT ON COLUMN t_student.modify_id IS '修改记录人';
COMMENT ON COLUMN t_student.modify_time IS '修改日期';

DROP TABLE t_subjects;
CREATE TABLE t_subjects(
	id serial8,
	student_id BIGINT NOT NULL DEFAULT 0,
	subject VARCHAR(20) NOT NULL DEFAULT '',
	score INTEGER NULL,
	address VARCHAR(1000) NOT NULL DEFAULT 'https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=m',
	remark VARCHAR(300) DEFAULT '继续加油哦',
	add_id BIGINT NOT NULL DEFAULT 0,
	add_time TIMESTAMP NOT NULL DEFAULT now(),
	modify_id BIGINT,
	modify_time TIMESTAMP,
	PRIMARY KEY(id)
);
COMMENT ON COLUMN t_subjects.student_id IS '学生表id';
COMMENT ON COLUMN t_subjects.subject IS '科目';
COMMENT ON COLUMN t_subjects.score IS '科目分数';
COMMENT ON COLUMN t_subjects.address IS '试题链接';
COMMENT ON COLUMN t_subjects.remark IS '老师评语';
COMMENT ON COLUMN t_subjects.add_id IS '增加记录人';
COMMENT ON COLUMN t_subjects.add_time IS '增加日期';
COMMENT ON COLUMN t_subjects.modify_id IS '修改记录人';
COMMENT ON COLUMN t_subjects.modify_time IS '修改日期';

插入记录二条，可联级插入二张表数据

WITH student_id AS (
	INSERT INTO t_student ( NAME, CLASS, course, subjects, gross_score )
	VALUES
		( '张三', '一年级', '{语文,数学,英语,物理,化学,历史,生物,地理,综合}', '{87,93,81,79,86,78,92,81,84}', 761 ) RETURNING ID 
	) INSERT INTO t_subjects ( student_id, subject, score, address )
VALUES
	( ( SELECT ID FROM student_id ), '语文', 87, 'https://www.baidu.com/1' ),
	( ( SELECT ID FROM student_id ), '数学', 93, 'https://www.baidu.com/2' ),
	( ( SELECT ID FROM student_id ), '英语', 81, 'https://www.baidu.com/3' ),
	( ( SELECT ID FROM student_id ), '物理', 79, 'https://www.baidu.com/4' ),
	( ( SELECT ID FROM student_id ), '化学', 86, 'https://www.baidu.com/5' ),
	( ( SELECT ID FROM student_id ), '历史', 78, 'https://www.baidu.com/6' ),
	( ( SELECT ID FROM student_id ), '生物', 92, 'https://www.baidu.com/7' ),
	( ( SELECT ID FROM student_id ), '地理', 81, 'https://www.baidu.com/8' ),
	( ( SELECT ID FROM student_id ), '综合', 84, 'https://www.baidu.com/9' );**加粗样式**

WITH student_id AS (
	INSERT INTO t_student ( NAME, CLASS, course, subjects, gross_score )
	VALUES
		(
			'李四',
			'四年级',
			ARRAY [ '语文',
			'数学',
			'英语',
			'物理',
			'化学',
			'历史',
			'生物',
			'地理',
			'综合' ]:: VARCHAR [],
			ARRAY [ 89,
			91,
			85,
			79,
			79,
			78,
			90,
			83,
			87 ]:: INTEGER [],
			761 
		) RETURNING * 
	) INSERT INTO t_subjects ( student_id, subject, score, address )
VALUES
	( ( SELECT ID FROM student_id ), '语文', 89, 'https://www.baidu.com/11' ),
	( ( SELECT ID FROM student_id ), '数学', 91, 'https://www.baidu.com/12' ),
	( ( SELECT ID FROM student_id ), '英语', 85, 'https://www.baidu.com/13' ),
	( ( SELECT ID FROM student_id ), '物理', 79, 'https://www.baidu.com/14' ),
	( ( SELECT ID FROM student_id ), '化学', 79, 'https://www.baidu.com/15' ),
	( ( SELECT ID FROM student_id ), '历史', 90, 'https://www.baidu.com/16' ),
	( ( SELECT ID FROM student_id ), '生物', 90, 'https://www.baidu.com/17' ),
	( ( SELECT ID FROM student_id ), '地理', 83, 'https://www.baidu.com/18' ),
	( ( SELECT ID FROM student_id ), '综合', 87, 'https://www.baidu.com/19' );

老师对学生李四的考试作了修改，有了最新的考试成绩，需要新增t_student记录，详情用原来的，也可更新个别字段 ，正常业务需要===>更新t_student表===>插入t_student表记录===>新增t_subjects，需要执行三条sql语句，用with 函数可以用一条sql执行

WITH old_exam AS (
	UPDATE t_student 
	SET last_exam = 1,
	modify_id = FLOOR ( random( ) * 80000+20 ),
	modify_time = now( ) 
	WHERE
		ID = 1 RETURNING ID 
	),
	new_exam AS (
		INSERT INTO t_student ( NAME, CLASS, course, subjects, gross_score )
	VALUES
		(
			'李四',
			'四年级',
			ARRAY [ '语文', '数学', '英语', '物理', '化学', '历史', '生物', '地理', '综合' ],
			ARRAY [ 89, 91, 85, 79, 79, 78, 90, 83, 87 ]:: INTEGER [],
			761 
		) RETURNING ID 
	) INSERT INTO t_subjects ( student_id, subject, score, address, add_id ) SELECT
	( SELECT ID FROM new_exam ),
	subject,
	score,
	address,
	FLOOR ( random( ) * 80000+20 ) 
FROM
	t_subjects 
WHERE
	student_id = ( SELECT ID FROM old_exam );

李四有了最新的考试成绩，需要新增t_student记录，新增t_subjects记录 ，正常业务需要===>更新t_student表===>插入t_student表记录===>新增t_subjects，需要执行三条sql语句，用with 函数可以用一条sql执行

WITH old_exam AS (
	UPDATE t_student 
	SET last_exam = 1,
	modify_id = FLOOR ( random( ) * 80000+20 ),
	modify_time = now( ) 
	WHERE
		ID = 3 
	),
	new_exam AS (
		INSERT INTO t_student ( NAME, CLASS, course, subjects, gross_score )
	VALUES
		(
			'李四',
			'四年级',
			ARRAY [ '语文', '数学', '英语', '物理', '化学', '历史', '生物', '地理', '综合' ],
			ARRAY [ 91, 88, 87, 79, 76, 81, 88, 86, 85 ]:: INTEGER [],
			761 
		) RETURNING ID 
	) INSERT INTO t_subjects ( student_id, subject, score, address )
VALUES
	( ( SELECT ID FROM new_exam ), '语文', 91, 'https://www.baidu.com/21' ),
	( ( SELECT ID FROM new_exam ), '数学', 88, 'https://www.baidu.com/2' ),
	( ( SELECT ID FROM new_exam ), '英语', 87, 'https://www.baidu.com/23' ),
	( ( SELECT ID FROM new_exam ), '物理', 79, 'https://www.baidu.com/24' ),
	( ( SELECT ID FROM new_exam ), '化学', 76, 'https://www.baidu.com/25' ),
	( ( SELECT ID FROM new_exam ), '历史', 81, 'https://www.baidu.com/26' ),
	( ( SELECT ID FROM new_exam ), '生物', 88, 'https://www.baidu.com/27' ),
	( ( SELECT ID FROM new_exam ), '地理', 86, 'https://www.baidu.com/28' ),
	( ( SELECT ID FROM new_exam ), '综合', 86, 'https://www.baidu.com/29' );

如果需要更新一张表的多条记录，比如t_subjects，
id=1的score改为88
id=3的score改为86
id=4的score改为80
id=6的score改为81

UPDATE t_subjects AS s 
SET score = TEMP.score 
FROM
	( VALUES ( 1, 88 ), ( 3, 86 ), ( 4, 80 ), ( 6, 81 ) ) AS TEMP ( ID, score ) 
WHERE
	s.ID = TEMP.ID;

数组的操作，有array-cat(array,array)数组与数组拼接运算,但并没有 数组与数组云除运算，可用以下语句执行。

UPDATE t_student 
SET subjects = (
	SELECT
		"array_agg" ( TEMP ) 
	FROM
		"unnest" ( ( SELECT subjects FROM t_student WHERE ID = 2 ) :: INTEGER [] ) AS TEMP 
	WHERE
		TEMP NOT IN ( 89, 5, 83 ) 
	) 
WHERE
	"id" = 2;