复杂一点的sql计算

CarlosXu14

已于 2023-02-20 20:17:41 修改

阅读量257

点赞数

分类专栏： mysql 文章标签： sql 数据库 mysql

于 2023-02-01 09:24:27 首次发布

本文链接：https://blog.csdn.net/qq_43459184/article/details/128826552

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

建表语句

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;

https://blog.csdn.net/liqing0013/article/details/96291116#:~:text=%E5%A4%9A%E8%A1%A8%E4%B9%8B%E9%97%B4%E5%AD%97%E6%AE%B5%E6%9B%B4%E6%96%B0%201%20%E6%8A%8A%20manager%20%E8%A1%A8%20id%20%3D%201,1%202%203%204%205%206%207%208

建立索引

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>