例子一: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(); #调用存储过程