sql小结------持续性更新

例子一：select begin_time,end_time,num,amount from report.t_channel_ofl_week t where begin_time in (''2021-12-06','2021-12-13','2021-12-20','2021-12-27') order by begin_time desc ,amount desc limit 0, 10

1、order by time desc；根据时间 倒叙排序

2、amount desc limit 0, 10，取金额前10条数据

例子二：select distinct t.full_name,t.agent_id,t.remark,t.customer_id,t.customer_type,md_date,op_date,t.customer_status,t.belong_agent,u.name as md_user,a.agent_name,b.merchno,t.cust_flag from t_customers t  left join T_ADMUSERS u on t.MD_USERID=u.USER_ID  left join t_saleusers l ON t.sales_id=l.autoid  left join t_agents a on t.belong_agent=a.agent_id  left join t_mer_busi b on t.customer_id = b.customer_id   where customer_status =  "1" and   t.belong_agent in ('10021101','30074403','00014403') and 1=1 and t.customer_type in ('M000','P000')and t.op_date between "0000-00-00 00:00:00" and "2022-01-10 23:59:59"  order by md_date DESC limit 0, 10

关注点：

1、distinct 去重

2、left join on 多表连接查询，上述例子中5表连接查询

例子三：

SELECT count(mt.matterid) as cnt FROM
           T_MATTER mt where mt.dealuserid is null and mt.MATTERID in
           (SELECT MATTERID from T_MATTER_ROLE tmr where tmr.ROLEID in (1,4,8,15) and tmr.agent_id = '00014403') and mt.status = 1 AND mt.mid != '1234563'

例子四：

SELECT  
     count(DISTINCT iss.village_id)
FROM
    issues_data iss
    LEFT JOIN village_info vill ON iss.village_id = vill.ID

遇到问题一定要记得多检查输入法。。。。

#9999目录
cd /data/tftpay/xproduct/
cp -r new-retail/lib new-retail/lib_bak

cp -r /data/tftpay/xdemo/new-retail  /data/tftpay/xproduct/new-retail 
cp -r /data/tftpay/xproduct/new-retail/lib_bak  /data/tftpay/xproduct/new-retail/lib

20221027新增sql类型

SELECT  * FROM   shop_score_change_detail so  WHERE so.village_id=2210 and  so.change_type = 1 and so.status = 0    and so.order_id NOT IN(
select sco.order_id from shop_order_info ord left JOIN shop_score_change_detail sco on ord.order_id = sco.order_id where ord.trade_status = 2 and sco.village_id = 2210 and sco.change_type = 1 and sco.status = 0 );

UPDATE issues_data SET score_detail_id = 53189 WHERE score_detail_id = 53829

mysql使用for循环加数据

  delimiter //                            #定义标识符为双斜杠
    drop procedure if exists test;          #如果存在test存储过程则删除
    create procedure test()                 #创建无参存储过程,名称为test
    begin
        declare i int;                      #申明变量
        set i = 1;                          #变量赋值
        while i < 101 do                     #结束循环的条件: 当i大于10时跳出while循环
            INSERT INTO `work_notice` ( `resident_id`, `title`, `issued_unit`, `object_id`, `content`, `read_num`, `data_month`, `is_superior`, `enable`, `create_time`, `create_by`, `update_time`, `update_by`, `type_id`, `is_top`, `person_type`, `is_confirm`) VALUES (575775, i, '怀中社区', 2338, '[{\"content\":\"\\u660e\\u5929\\u7701\\u6587\\u660e\\u529e\\u9886\\u5bfc\\u6765\\u6211\\u6751\\u9a8c\\u6536\\u5168\\u56fd\\u6587\\u660e\\u6751\\u9547\\u7684\\u5efa\\u8bbe\\u6210\\u679c\\u3002\\u5168\\u4f53\\u6751\\u6c11\\u4e8e\\uff0c\\u660e\\u5929\\u65e9\\u4e0a\\u516d\\u70b9\\u5230\\u4e5d\\u70b9\\u8fdb\\u884c\\u5168\\u6751\\u5927\\u626b\\u9664\\uff0c10\\u70b9\\u540e\\u8fdb\\u884c\\u68c0\\u67e5\\uff0c\\u6cb9\\u6eaa\\u6865\\u7247\\u533a\\u7531\\u6768\\u745e\\u73cd\\uff0c\\u5f20\\u81ea\\u4e91\\u8d1f\\u8d23\\uff0c\\u6668\\u5149\\u7247\\u533a\\u7531\\u5eb7\\u5229\\u534e\\u3001\\u590f\\u7389\\u5a07\\u8d1f\\u8d23\\uff0c\\u5982\\u6253\\u626b\\u4e0d\\u5f7b\\u5e95\\u7684\\uff0c\\u4e09\\u5929\\u5e7f\\u64ad\\u64ad\\u62a5\\uff0c\\u5e76\\u8bb0\\u5f55\\u5230\\u6587\\u660e\\u6237\\u4e3b\\u6863\\u6848\\u888b\\u4e2d\\u8fdb\\u884c\\u6263\\u5206\\u3002\",\"type\":\"text\"}]', 31, '2021-10', 0, 0, '2021-10-13 19:52:56', '575775', '2022-08-13 19:52:56', NULL, NULL, 0, 0, 0);
            set i = i + 1;                  #循环一次,i加一
        end while;                          #结束while循环
    end
    //                                      #结束定义语句
    call test();                            #调用存储过程