记录下 我耗费了2天 从0到0.000000001的过程记录,基本内容就不写了,就记录一些重点,时间耗哪了
过程说明: 传入关键字,查询对应的id后,再查询其他表数据。 正常SQL都是 in id 手写那完全没问题(真的对头,最后的方式是用拼接),或者子查询(因为需要重复使用多次结果集,子查询大可不必了),可是存储函数 它不能传递数组或者是在使用中存储数据。一下子没想到方式,查询了很多
先总结:越简单的方式 往往在最关键的时候 最有效?。拼接动态SQL
本来有一大串想写的,突然这个拼接SQL,好简单啊啊啊,整个人都不好了
一、FIND_IN_SET
由GROUP_CONCAT 生成后的是字符串,它不能直接用来in,但拼接SQL是可以编译生效的。
SELECT GROUP_CONCAT(id) into @zd_id FROM com_base_kehu WHERE FIND_IN_SET(khdm, in_khdm);
后面如果都是用 FIND_IN_SET 这个的话,效率慢了2倍多,2秒…等不及。
二、使用游标+循环
这个结果值,也就是GROUP_CONCAT,这里瞎捣腾。。学习学习这个游标+循环
-- 一系列操作,获取id
DECLARE current INT;
-- 拼接id
DECLARE ids VARCHAR(100) DEFAULT('');
-- 用于控制循环是否结束
DECLARE done INT DEFAULT FALSE;
-- 定义游标
DECLARE id_cursor CURSOR FOR SELECT id FROM com_base_kehu WHERE FIND_IN_SET(khdm, in_khdm);
-- 游标找不到后,设置标识符 退出循环
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/* 打开游标 */
OPEN id_cursor;
FETCH id_cursor INTO current;
REPEAT
-- SELECT current;
SET ids = CONCAT(ids, ",", current);
FETCH id_cursor INTO current;
UNTIL done END REPEAT;
SELECT RIGHT(ids,LENGTH(ids)-1) INTO ids;
-- SELECT ids;
CLOSE id_cursor;
打开游标先取一个值,循环体再循环游标值的次数。如果这里 都只在循环体里取,好像循环次数会+1次,不明白。
然后,就是拼接,编译,执行
SET @prepare_sql = CONCAT("SELECT A.org_id 组织ID, A.zd_id 终端ID, C.khmc 终端名称, A.djbh 单据编号, FROM_UNIXTIME(A.rq) 日期, SUM(B.je) 结算金额
FROM ipos_qtlsd A
LEFT JOIN ipos_qtlsdjs B
ON A.id = B.pid
LEFT JOIN com_base_kehu C
ON A.zd_id = C.id
WHERE A.zf = 0
AND A.zd_id in (", ids, ")
AND FROM_UNIXTIME(A.yyrq) >= '", in_start_rq, " 00:00:00'
AND '", in_end_rq, " 23:59:59' >= FROM_UNIXTIME(A.yyrq)
AND B.js_id not in (3,4,5,6)
GROUP BY A.djbh);
-- 预编译
PREPARE stmt FROM @prepare_sql;
-- 执行
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
贴个总的,传入代码跟日期,返回所有记录
CREATE DEFINER=`baison`@`%` PROCEDURE `test2`(IN `in_khdm` VARCHAR(100), IN `in_start_rq` varchar(20), IN `in_end_rq` varchar(20))
BEGIN
#Routine body goes here...
DECLARE current INT;
DECLARE ids VARCHAR(100) DEFAULT('');
-- 用于控制循环是否结束
DECLARE done INT DEFAULT FALSE;
-- 定义游标
DECLARE id_cursor CURSOR FOR SELECT id FROM com_base_kehu WHERE FIND_IN_SET(khdm, in_khdm);
-- 游标找不到后,设置标识符 退出循环
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/* 打开游标 */
OPEN id_cursor;
FETCH id_cursor INTO current;
-- SET ids = current;
REPEAT
-- SELECT current;
-- SET current = CONCAT("'", current, "'");
SET ids = CONCAT(ids, ",", current);
FETCH id_cursor INTO current;
UNTIL done END REPEAT;
SELECT RIGHT(ids,LENGTH(ids)-1) INTO ids;
-- SELECT ids;
CLOSE id_cursor;
SET @in_sql = CONCAT("SELECT A.org_id 组织ID, A.zd_id 终端ID, C.khmc 终端名称, A.djbh 单据编号, FROM_UNIXTIME(A.rq) 日期, SUM(B.je) 结算金额
FROM ipos_qtlsd A
LEFT JOIN ipos_qtlsdjs B
ON A.id = B.pid
LEFT JOIN com_base_kehu C
ON A.zd_id = C.id
WHERE A.zf=0
AND A.zd_id in (", ids, ")
AND FROM_UNIXTIME(A.yyrq) >= '", in_start_rq, " 00:00:00'
AND '", in_end_rq, " 23:59:59' >= FROM_UNIXTIME(A.yyrq)
AND B.js_id not in (3,4,5,6)
GROUP BY A.djbh;");
SELECT @in_sql;
PREPARE stmt FROM @in_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END