本文探讨了三种更新用户积分的方法，包括内连接、子查询和存在bug的分组更新。同时，提供了月度和时段统计数据查询示例，以及低版本MySQL的ROW_NUMBER()函数应用和固定月份查询。最后展示了如何整合多个表并进行年度统计。
批量重置排序字段
UPDATE spn_team_shop s
JOIN (
SELECT id,
@row_number := @row_number + 1 AS new_order_num
FROM (
SELECT id
FROM spn_team_shop
WHERE order_num = 7611 and team_user_id = '1581196199099097088'
ORDER BY id -- 确保按你希望的顺序排序
) subquery,
(SELECT @row_number := 7610) r -- 初始化变量，注意这里从7610开始是因为我们要在下一行加1
) t ON s.id = t.id
SET s.order_num = t.new_order_num
WHERE s.order_num = 7611 and team_user_id = '1581196199099097088';
通过一个集合的数据，更新另一个数据集中的字段
第一种方式：
update trail_walk_member b
inner join (
select a.user_id, a.removePoint
from (
select user_id, sum(get_point) as removePoint
from trail_task_history
where task_content <> 'use-static' and get_point > 0 and DATE_FORMAT(create_time, '%Y-%m-%d') = '2021-10-16'
group by user_id
) a
) c
on b.id = c.user_id
set b.personal_points = b.personal_points - c.removePoint
第二种方式：
update trail_walk_member b, (
select user_id, sum(get_point) as removePoint
from trail_task_history
where task_content <> 'use-static' and get_point > 0 and DATE_FORMAT(create_time, '%Y-%m-%d') = '2021-10-16'
group by user_id
) a
set b.personal_points = b.personal_points - a.removePoint
where a.user_id = b.id
第三种方式（该方式目前存在一个bug，还不知道哪里原因，就是第一次执行的时候，受影响行数和后面每一次执行的受影响行数不一致，不知道啥情况，建议使用上面第一和第二种方式）：
update trail_walk_member b
set b.personal_points = b.personal_points - (
select a.removePoint
from (
select user_id, sum(get_point) as removePoint
from trail_task_history
where task_content <> 'use-static' and get_point > 0 and DATE_FORMAT(create_time, '%Y-%m-%d') = '2021-10-10'
group by user_id
) a
where b.id = a.user_id
)
统计每一个月的数量
select sum(case month(create_time) when '1' then enter_num else 0 end) as '1月',
sum(case month(create_time) when '2' then enter_num else 0 end) as '2月',
sum(case month(create_time) when '3' then enter_num else 0 end) as '3月',
sum(case month(create_time) when '4' then enter_num else 0 end) as '4月',
sum(case month(create_time) when '5' then enter_num else 0 end) as '5月',
sum(case month(create_time) when '6' then enter_num else 0 end) as '6月',
sum(case month(create_time) when '7' then enter_num else 0 end) as '7月',
sum(case month(create_time) when '8' then enter_num else 0 end) as '8月',
sum(case month(create_time) when '9' then enter_num else 0 end) as '9月',
sum(case month(create_time) when '10' then enter_num else 0 end) as '10月',
sum(case month(create_time) when '11' then enter_num else 0 end) as '11月',
sum(case month(create_time) when '12' then enter_num else 0 end) as '12月'
from sport_pgym.stadium_pass_data_log
where create_time is not null and year(create_time) = year(now())
group by year(create_time)
建议使用下面这种
SELECT substring_index(substring_index('1,2,3,4,5,6,7,8,9,10,11,12',',', b.help_topic_id + 1), ',', -1) month
FROM mysql.help_topic b
where b.help_topic_id < (LENGTH('1,2,3,4,5,6,7,8,9,10,11,12') - LENGTH(REPLACE('1,2,3,4,5,6,7,8,9,10,11,12', ',', '')) + 1);
统计一天中每一个时间段的数量
select ifnull(sum(c.one), 0) as '0-2点',
ifnull(sum(c.two), 0) as '2-4点',
ifnull(sum(c.three), 0) as '4-6点',
ifnull(sum(c.four), 0) as '6-8点',
ifnull(sum(c.five), 0) as '8-10点',
ifnull(sum(c.six), 0) as '10-12点',
ifnull(sum(c.seven), 0) as '12-14点',
ifnull(sum(c.eight), 0) as '14-16点',
ifnull(sum(c.nine), 0) as '16-18点',
ifnull(sum(c.ten), 0) as '18-20点',
ifnull(sum(c.eleven), 0) as '20-22点',
ifnull(sum(c.twelve), 0) as '22-24点'
from (
select sum(if((a.sd > 0 and a.sd < 2), enter_num, 0)) one,
sum(if(((a.sd >= 2 and a.sd < 4)), enter_num, 0)) two,
sum(if((a.sd >= 4 and a.sd < 6), enter_num, 0)) three,
sum(if((a.sd >= 6 and a.sd < 8), enter_num, 0)) four,
sum(if((a.sd >= 8 and a.sd < 10), enter_num, 0)) five,
sum(if((a.sd >= 10 and a.sd < 12), enter_num, 0)) six,
sum(if((a.sd >= 12 and a.sd < 14), enter_num, 0)) seven,
sum(if((a.sd >= 14 and a.sd < 16), enter_num, 0)) eight,
sum(if((a.sd >= 16 and a.sd < 18), enter_num, 0)) nine,
sum(if((a.sd >= 18 and a.sd < 20), enter_num, 0)) ten,
sum(if((a.sd >= 20 and a.sd < 22), enter_num, 0)) eleven,
sum(if((a.sd >= 22 and a.sd < 24), enter_num, 0)) twelve
from (
select cast(date_format(create_time, '%H') as unsigned) as sd, enter_num
from sport_pgym.stadium_pass_data_log
where create_time is not null and date_format(create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
order by create_time
) a
group by a.sd
)c
获取表的结构
select "club_order_refound" as 名称, "NO" as 是否允许为空, "表" 类型, "table" as 类型描述, (
SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='club' and TABLE_NAME = 'club_order_refound'
) as 描述 from dual
union all
select column_name, is_nullable, "字段" 类型, column_type, column_comment
from information_schema.columns
where table_schema = 'club' and table_name = 'club_order_refound'
union all
select "", "", "", "", "" from dual
union all
select "", "", "", "", "" from dual;
低版本mysql实现ROW_NUMBER() OVER(PARTITION BY XXX）（分区，排序）功能查询
eg:当uid相同的时候，获取时间post_time最大的一条数据
原始数据：
上sql：
select a.id, a.uid, a.post_time, a.content
from(
select if(@temp_uid = b.uid, @rank := @rank + 1, @rank := 1) as rank, @temp_uid := b.uid as v_temp_uid, b.uid, b.post_time, b.id
from (
select *
from tablename
ORDER BY uid, post_time desc
) b, (select @temp_uid := null, @rank := 0) rt
) a
where
-- 取值最大的的数据
a.rank = 1
ORDER BY a.uid, a.post_time desc
结果：
注释：高版本请忽略当前写法，当前写法只适用于没有OVER函数的低版本mysql
查询固定的几月
select '1月' as month union all select '2月' union all select '3月' union all select '4月' union all select '5月' union all select '6月'
union all select '7月' union all select '8月' union all select '9月' union all select '10月' union all select '11月' union all select '12月'
统计每个用户的连续运动天数
第一步：插入测试数据
create table tmp_login (
user_id int(11) ,
login_date datetime
);
insert into tmp_login values(2,'2020-05-29 11:12:12');
insert into tmp_login values(2,'2020-05-29 15:12:12');
insert into tmp_login values(2,'2020-05-30 11:12:12');
insert into tmp_login values(2,'2020-05-31 11:12:12');
insert into tmp_login values(2,'2020-06-01 11:12:12');
insert into tmp_login values(2,'2020-06-02 11:12:12');
insert into tmp_login values(2,'2020-06-03 11:12:12');
insert into tmp_login values(2,'2020-06-04 11:12:12');
insert into tmp_login values(2,'2020-06-05 11:12:12');
insert into tmp_login values(2,'2020-06-06 11:12:12');
insert into tmp_login values(2,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-01 11:12:12');
insert into tmp_login values(7,'2020-06-02 11:12:12');
insert into tmp_login values(7,'2020-06-03 11:12:12');
insert into tmp_login values(7,'2020-06-05 11:12:12');
insert into tmp_login values(7,'2020-06-06 11:12:12');
insert into tmp_login values(7,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-08 11:12:12');
第二步： 统计每个用户的连续运动天数(注意mysql版本)
select user_id, count(*) as num_days
from (
select *, date_sub(days, interval rn day) as results
from(
select user_id, days, row_number() over(partition by user_id order by days) as rn
from (
select distinct user_id, date(login_date) as days from tmp_login
) t1
) t2
) t3
group by user_id , results;
结果：
MySQL自定义实现row_number() over(partition by) 分组排序功能
要求：统计每个用户最大的连续运动天数情况
原始数据：
select b.user_id, (select ifnull(real_name, nick_name) as name from user c where c.id = b.user_id) as real_name,
b.continueday,
b.sport_time as sport_min_date,
-- 取出来当前组中最小的运动时间，加上连续运动的天数，就是最大的运动时间
date_add(b.sport_time, interval b.continueday - 1 day) as sport_max_date
from (
-- 变量描述：@last_time 上一次运动时间，a.date_day当前运动时间，@total_day连续运动天数
-- 原理：每一行运动时间与上一次的时间进行比较，相差为1，则为连续运动，这时候，将@total_day变量加1，否则表示为新的分组，从1开始
select @total_day:=if(datediff(@last_time, a.date_day) = 1, @total_day + 1, 1) as continueday, @last_time:= date_day as sport_time, a.user_id
from (
-- @last_time，@total_day得提前申明，不然第一次执行没有这个变量，数据回有问题
select id, date_day, user_id, (select @last_time:=0),(select @total_day:=0)
from user_sport_record
where date_day is not null
order by user_id, date_day desc
)a
-- 该排序必须按照运动天数进行倒序，否则数据对应不上
order by a.user_id, continueday desc
) b
group by b.user_id
order by b.continueday desc
结果
将表名结构类似的多张表，全部查询出来组装到一起，如下图，按照年月分隔的数据表，我需要全部查询出来，然后进行统计，怎么做呢！
只需要这样即可（其中user_2023%是表前缀模糊查询）：
SET @table_names = NULL;
SELECT GROUP_CONCAT(table_name) INTO @table_names
FROM information_schema.tables
WHERE table_name LIKE 'user_2023%';
SET @sql = CONCAT('SELECT * FROM ', REPLACE(@table_names, ',', ' UNION ALL SELECT * FROM '));
PREPARE final_query FROM @sql;
EXECUTE final_query;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
