nju-softeng / dingtalk-app-server Goto Github PK

select ac_record_id FROM paper_detail WHERE paper_id = 6

select * FROM ac_record ac RIGHT JOIN (select ac_record_id FROM paper_detail WHERE paper_id = 6) t on ac.id = t.ac_record_id

update ac_record a, (select ac_record_id FROM paper_detail WHERE paper_id = 6) t set a.create_time = '2021-01-15' WHERE a.id = t.ac_record_id

select ifnull((select id  from tableName where conditions limit 1 ), 0)

 create database test charset=utf8;
 use test;

CREATE TABLE `dc_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dc` double DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `week` int(11) DEFAULT NULL,
  `application_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `auditor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=166104 DEFAULT CHARSET=utf8;

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertdata`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
	DECLARE j INT;
  WHILE (i <= n ) DO
		WHILE (j <= 100) DO
			INSERT into dc_record (application_id, auditor_id, user_id,insert_time, week, dc) VALUES (i, i, j ,now(), 1, FLOOR(RAND() * 100) );
			SET j=j+1;
		END WHILE;
		SET i=i+1;
		SET j = 1;
  END WHILE;
END

select ac_record_id, vote_id FROM vote_detail WHERE ac_record_id is not null


SELECT id from vote WHERE external = 0

SELECT v.id, p.title from paper p LEFT JOIN vote v on p.vote_id = v.id 


SELECT * FROM (select ac_record_id, vote_id FROM vote_detail WHERE ac_record_id is not null
) A INNER JOIN (SELECT v.id, p.title from paper p LEFT JOIN vote v on p.vote_id = v.id ) B on A.vote_id = B.id


UPDATE ac_record a, (SELECT * FROM (select ac_record_id, vote_id FROM vote_detail WHERE ac_record_id is not null
) A INNER JOIN (SELECT v.id, p.title from paper p LEFT JOIN vote v on p.vote_id = v.id ) B on A.vote_id = B.id) tmp set a.reason = CONCAT(tmp.title, a.reason) WHERE a.id = tmp.ac_record_id

SELECT v.id, p.title from external_paper p LEFT JOIN vote v on p.vote_id = v.id 


SELECT * FROM (select ac_record_id, vote_id FROM vote_detail WHERE ac_record_id is not null
) A INNER JOIN (SELECT v.id, p.title from external_paper p LEFT JOIN vote v on p.vote_id = v.id ) B on A.vote_id = B.id


UPDATE ac_record a, (SELECT * FROM (select ac_record_id, vote_id FROM vote_detail WHERE ac_record_id is not null
) A INNER JOIN (SELECT v.id, p.title from external_paper p LEFT JOIN vote v on p.vote_id = v.id ) B on A.vote_id = B.id) tmp set a.reason = CONCAT(tmp.title, a.reason) WHERE a.id = tmp.ac_record_id

SELECT
	u.NAME AS NAME,
	a.ac,
	a.reason, 
	create_time
FROM
	( SELECT * FROM `user` WHERE is_deleted = 0 ) u
	INNER JOIN ( SELECT * FROM ac_record WHERE create_time > "2020-12-31" and create_time < "2021-02-01") a ON a.user_id = u.id 
ORDER BY
	user_id DESC

select * FROM paper_detail WHERE paper_id = 4

select * FROM ac_record ac RIGHT JOIN (select ac_record_id FROM paper_detail WHERE paper_id = 4) t on ac.id = t.ac_record_id

update ac_record a, (select ac_record_id FROM paper_detail WHERE paper_id = 4) t set a.reason = CONCAT('(AC待分配)', a.reason), a.ac = 0  WHERE a.id = t.ac_record_id

SELECT
	ac_record_id,
	vote_id,
	result AS v_res 
FROM
	vote_detail 
WHERE
	ac_record_id IS NOT NULL


SELECT
	v.id,
	p.title,
	p.result AS p_res 
FROM
	paper p
	LEFT JOIN vote v ON p.vote_id = v.id


SELECT
	title,
	ac_record_id,
	user_id,
	case
		when B.p_res = 4 and A.v_res = 1 or B.p_res = 3 and A.v_res = 0 then
			1
		else 
			0
	END
	as res
FROM
	( SELECT ac_record_id, vote_id, user_id, result AS v_res FROM vote_detail WHERE ac_record_id IS NOT NULL ) A
	INNER JOIN (
	SELECT
		v.id,
		p.title,
		p.result AS p_res 
	FROM
		paper p
		LEFT JOIN vote v ON p.vote_id = v.id 
	) B ON A.vote_id = B.id


UPDATE ac_record a,
(
	SELECT
		title,
		ac_record_id,
	CASE
			
			WHEN B.p_res = 4 
			AND A.v_res = 1 
			OR B.p_res = 3 
			AND A.v_res = 0 THEN
				1 ELSE 0 
			END AS res 
		FROM
			( SELECT ac_record_id, vote_id, user_id, result AS v_res FROM vote_detail WHERE ac_record_id IS NOT NULL ) A
			INNER JOIN (
			SELECT
				v.id,
				p.title,
				p.result AS p_res 
			FROM
				paper p
				LEFT JOIN vote v ON p.vote_id = v.id 
			) B ON A.vote_id = B.id 
		) tmp 
		SET a.reason = if(res = 1, CONCAT('投票预测正确：' , tmp.title ), CONCAT('投票预测错误：' , tmp.title ))
WHERE
	a.id = tmp.ac_record_id