SQL语句示例

left join

说明：review_id = 48的学生表a通过roomId左联接分组查询的roomId表b

SELECT
	*
FROM
	biz_review_student AS a
LEFT JOIN (
	SELECT
		room_id
	FROM
		biz_review_student
	WHERE
		review_id = 48
	GROUP BY
		room_id
) b ON a.room_id = b.room_id
WHERE
	review_id = 48;

case语句

SELECT ec. CODE,ec. NAME,
	COUNT(CASE WHEN s. STATUS = 2 THEN 1 ELSE NULL END) AS numA,
	COUNT(CASE WHEN s. STATUS = 1 THEN 1 ELSE NULL END) AS numB,
	COUNT(CASE WHEN s. STATUS = 0 THEN 1 ELSE NULL END) AS numC,
    COUNT(CASE WHEN s. upload_status = 1 THEN 1 ELSE NULL END) AS upload
FROM
	biz_exam_organization ec
LEFT JOIN biz_student s ON ec. CODE = s.site_code
WHERE
	ec.organ_type = 2
AND ec.city_code = '510500'
GROUP BY
	ec. CODE;

right join

SELECT
	*
FROM
	biz_question_option AS a
RIGHT  JOIN (
	SELECT
		id AS idq,content as cq
	FROM
		biz_question
	WHERE
		tenant_id = 12 AND type = 2
) b ON a.question_id = b.idq
WHERE is_selected=1
ORDER BY b.idq ASC;

说明：order by排序字段 ASC(升序)/desc(降序)

inner join

SELECT
	*
FROM
	biz_review_scan_paper AS a
INNER JOIN (
	SELECT
		id
	FROM
		biz_review_student
	WHERE
		review_id = 47
	AND exam_site = '西湖大学'
) b ON a.student_id = b.id
WHERE
	relation_id = 106;

in 子查询

SELECT
*
FROM
	biz_review_student
WHERE
	review_id = 47
AND exam_site = '西湖大学'
AND room_id = '1'
AND id  IN(select student_id from biz_review_scan_paper where relation_id =106);

NOT IN

-- 缺考考点名单
SELECT * from biz_exam_organization where organ_type=2 AND city_code='07' 
AND id NOT IN
(SELECT tenant_id from biz_student where `status`=2 GROUP BY tenant_id);

关联更新：update set语句

UPDATE biz_rule_object_item a
SET a.rule_type = (
    SELECT rule_type
    FROM biz_rule_object b
    WHERE b.id = a.object_id
);
 
update  biz_question_analysis a,biz_question b set a.level=b.ability_level where a.question_id=b.id;

随机密码：FLOOR 、RAND函数

-- 随机生成6位数密码
UPDATE biz_student SET password = FLOOR(RAND() * 900000 + 100000);

关联删除：delete from join语句

-- 关联删除
DELETE a,t from biz_paper a LEFT JOIN biz_paper_question t ON a.id=t.paper_id WHERE a.id<62

表中插入字段：ALTER TABLE ADD语句

ALTER TABLE `biz_student`
    ADD COLUMN `second_code` VARCHAR(50) NULL COMMENT '辅码' AFTER `code`;

通过查询结果直接插入数据

INSERT INTO `biz-exam`.sys_common_config SELECT * FROM `biz-ycks-exam`.sys_common_config WHERE id=143;

清空表：TRUNCATE

TRUNCATE biz_answer_record;

计算时间差：TIMESTAMPDIFF MIN MAX函数

-- 计算两条记录时间差（second为单位）
SELECT TIMESTAMPDIFF(SECOND, MIN(CREATED_DATE), MAX(CREATED_DATE)) AS timediff FROM biz_answer_record;

计算时间差：

-- 开始时间是24号，且作答时间不超过30分钟
SELECT tenant_id,site_code FROM biz_student where DATE_FORMAT(start_time,'%Y-%m-%d')="2024-06-24" AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 30 GROUP BY tenant_id HAVING COUNT(*)>30;