建表语句
drop table if exists common_config;
create TABLE`cc_config` (
`id`int(11) unsigned not NULL AUTO_INCREMENT,
`type` tinyint COMMENT '类型 0-零规则 1-1规则 2-2规则',
`key` varchar(20) DEFAULT NULL COMMENT 'key',
`value` text DEFAULT NULL COMMENT 'value',
`ext1` varchar(255) DEFAULT NULL COMMENT '扩展信息1',
`ext2` varchar(255) DEFAULT NULL COMMENT '扩展信息2',
`status` tinyint default 0 COMMENT '状态 3 已删除',
`create_at`datetime not NULL default current_timestamp COMMENT '创建时间',
`update_at`datetime not null default current_timestamp on update current_timestamp COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='配置'
查询截至当前打卡失败次数>=1,并且成功次数=0的数据;
select distinct * from check_records where user_id in (
select t.user_id from check_records t group by t.check_result
having count(if( t.check_result =-1 ,1,null ))>1
and count(if( t.check_result =1 ,1,null))=0);
给表中的字段排序赋值
UPDATE medal m1,
( select id,row_number() over(order by create_at asc) as i from medal) as m2
SET m1.sort = m2.i WHERE m1.id = m2.id;
建立索引
ALTER TABLE info ADD index rank_index (dealer_id, dimension_type);
DROP INDEX rank_index ON info ;
排名参考:https://www.begtut.com/mysql/mysql-dense_rank-function.html
select *, DENSE_RANK() over (ORDER BY m desc) AS irank1
,DENSE_RANK() over (ORDER BY n DESC) AS irank2 from cnt
where id =1;
创建临时表的方案批量插入数据:
<update id="batchUpdateIntegralRewardIssue">
create temporary table temp_table_${batchNo} (
id bigint primary key not null,
non_conformance varchar(250)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into temp_table_${batchNo}
(id, non_conformance) values
<foreach collection="issueDtos" item="item" open="(" close=")" separator="),(">
#{item.id},
#{item.nonConformance}
</foreach>;
update integral_reward_issue as iri inner join temp_table_${batchNo} as t on t.id=iri.id
set iri.issue_condition = 0, issue_condition_calculate_flag = 1, iri.non_conformance = t.non_conformance
where iri.batch_no = #{batchNo};
drop table if exists temp_table_${batchNo};
</update>
一张表根据另一张表的统计数量修改值
update crc left join (
select userId , DATE_FORMAT( time , '%Y-%m') as month , count(1 ) as nums
FROM c where res =1
GROUP by userId, DATE_FORMAT( time , '%Y-%m')) as cr
on crc.userId =cr.userId
set crc.success_times =cr.nums where crc.month =cr.month;
case when用法记录
select <include refid="Base_Column_List2" />,
CASE WHEN (cr.push_result= 1 and cr.check_result =1) THEN '打卡成功'
WHEN (cr.push_result=-1 or cr.check_result =-1) THEN '打卡失败'
WHEN (cr.push_result= 1 and cr.check_result is null) THEN '未打卡' ELSE NULL
END AS is_success_str,
CASE WHEN (cr.check_result=-1 or cr.check_result_detail is not null) THEN cr.check_result_detail
WHEN (cr.push_result= -1 and cr.push_result_detail is not null) THEN cr.push_result_detail ELSE NULL
END AS error_msg
from cr inner join t
on cr.user_id =t.user_id
<where>
<if test="id!= '' and id!= null">
AND t.id= #{id}
</if>
</where>