一些个SQL

中午得睡会儿

已于 2023-04-11 10:52:38 修改

阅读量93

点赞数

文章标签： sql 数据库

于 2023-04-11 10:45:46 首次发布

本文链接：https://blog.csdn.net/qq_41975451/article/details/130078443

版权

文章提供了一个关于数据库操作的存储过程示例，该过程涉及声明变量、游标使用、数据更新以及处理未找到记录的情况。同时，展示了一条复杂的SQL查询，该查询涉及到子查询、集合函数GROUP_CONCAT和FIND_IN_SET在多表JOIN中的应用。

摘要由CSDN通过智能技术生成

存储过程

BEGIN

declare tiems INT DEFAULT 0;

DECLARE done INT DEFAULT 0;
DECLARE scene cursor for SELECT SCENECODE, VERSION, UGID,YHDM,XSMC,ORGNAME FROM T_PORTAL_RPA_SCENE GROUP BY SCENECODE, VERSION;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open scene;

REPEAT

fetch scene into ASCENECODE, AVERSION, AUGID,AYHDM,AXSMC,AORGNAME;

if not done THEN
UPDATE RPADOWNLOADMANAGE SET UGID = AUGID, YHDM=AYHDM, XSMC=AXSMC, ORGNAME=AORGNAME WHERE  ROBOTID=ASCENECODE AND VERSION = AVERSION;
end if;
until done end repeat;

close scene;
commit;
END

SQL

SELECT t2.id, t2.name
FROM (
     SELECT
            @ids as _ids,
            (SELECT @ids := GROUP_CONCAT(id) FROM basis_grid_info WHERE FIND_IN_SET(pid, @ids)) AS cids
     FROM basis_grid_info, (SELECT @ids := '6d58260e84c54ab4b72059bea2e33f92', @l := 0) b
     WHERE @ids IS NOT NULL
    ) t1, basis_grid_info t2
WHERE FIND_IN_SET(t2.id, t1._ids)