常用sql片段(持续更新)

前言

在日常的工作中会遇到一些难写的sql,写出来之后也容易忘记，这篇博客中记录一下一些容易忘记的sql语句及存储过程

Mysql

# 带连接的update
update waiter w left join hotel_service hs on w.hotel_id = hs.hotel_id and service_id =1
left join service_process_node spn on  spn.process_id = hs.process_id
set w.role_id = spn.node_upgrade_handlers ->'$[0]'
where w.role_id = 99999；

# 生成insert语句
select concat('INSERT INTO bw_reven_tool.waiter_floor_mapping ( hotel_id, building_no, floor_no, waiter_id) VALUES (',
         hr.hotel_id,',',
              '\'', hr.building_no, '\',',
              '\'', hr.floor_no, '\',',
              '\'', w.id , '\')'
         )
from (select hotel_id, building_no, floor_no from hotel_room group by hotel_id, building_no, floor_no) hr
       left join waiter w on w.hotel_id = hr.hotel_id
where w.id = 83;


# 循环插入数据
CREATE DEFINER=`mytoor`@`%` FUNCTION `JcTestData`() RETURNS int(11)
BEGIN                                
    DECLARE num INT DEFAULT 2000000;         
    DECLARE i INT DEFAULT 0; 
    
    WHILE i < num DO             
    INSERT INTO test_order(`platform_sn`,`third_sn`,`type`,`create_time`) 
         VALUES(CONCAT('SN',i),UUID(),1,now());
        SET i =  i + 1;
    END WHILE;        
    RETURN i;
END

Postgresql

#sql片段
do
$random_virtual_user$
    DECLARE
        V_STORE_ID    varchar(36);
        USER_MOBILE   varchar(11);
        V_TEMP_PHONE  varchar(36);
        V_USER_ID     varchar(36);
        IS_SUPER_USER bool;
        V_NEW_ID      bigint;
    BEGIN
        USER_MOBILE := '15900237021';
        select is_super into IS_SUPER_USER from user_info where mobile = USER_MOBILE;

        if IS_SUPER_USER then

        else
            -- 查询当前用户的id
            select user_id into V_USER_ID from user_info where mobile = USER_MOBILE;
            -- 把当前用户的信息更新一下
            update user_info set super_group=V_USER_ID, is_super= true where mobile = USER_MOBILE;


            FOR V_STORE_ID in (select id from temp_store_id)
                LOOP

                    select phone into V_TEMP_PHONE from temp_user_phone where in_use = false limit 1;
                    update temp_user_phone set in_use= true where phone = V_TEMP_PHONE;

                    V_NEW_ID = cast(V_TEMP_PHONE AS bigint);
                    raise notice '%',V_TEMP_PHONE;
                    raise notice '%',V_NEW_ID;


                    insert into user_info(id, avatar_url, last_device_token, mobile, nick_name,
                                          password, store_id, user_id, user_name, created_at,
                                          department_str, is_store_root, salt, status,
                                          is_super, super_group)
                    select V_NEW_ID,
                           avatar_url,
                           last_device_token,
                           (V_TEMP_PHONE)                 AS mobile,
                           nick_name,
                           password,
                           V_STORE_ID,
                           concat('YJ_CUS', V_TEMP_PHONE) AS user_id,
                           user_name,
                           now(),
                           '@@@'                          AS department_str,
                           (false)                        AS is_store_root,
                           salt,
                           status,
                           true                           as is_super,
                           V_USER_ID                      as super_group
                    from user_info
                    where mobile = USER_MOBILE;


                END LOOP;
        end if;
    END;
$random_virtual_user$;