记录项目中的select场景语句以及mybatis中的对应操作，如：分组补0，递归查询

置顶流连勿忘返

已于 2024-01-01 21:41:27 修改

阅读量295

点赞数

于 2023-03-08 12:01:15 首次发布

本文链接：https://blog.csdn.net/qq_26112725/article/details/129400088

版权

java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

SQL SELECT语句的执行顺序：

1、from子句组装来自不同数据源的数据；

2、where子句基于指定的条件对记录行进行筛选；

3、group by子句将数据划分为多个分组；

4、使用聚集函数进行计算；

5、使用having子句筛选分组；

6、计算所有的表达式；

7、使用order by对结果集进行排序；

8、select 集合输出。

前言：

1. group by：如何让 group by分组后，每组中的所有数据都显示出来

问题描述：

表如下，如何让这个表按device_id这个字段分组，且组中的每条数据都查寻出来？(假如说这个表名为：devicedata)

错误答案：select * from devicedata GROUP BY device_id

这个sql得到的结果是：

每一组只显示了一条数据，显然没达到我们的目的。

正确的结果：select * from devicedata GROUP BY device_id, id

查询到的结果：

这样就达到了我们的目的了，将每组中的每条数据都查寻出来了。

解释一下这条sql:

select * from devicedata GROUP BY device_id, id

这条sql就是，首先根据device_id这个字段来分组，因为这个表里面的id为1、3、4、5的记录他们的device_id的值相同，所以就查询了这个组中的一条，然后在将1、3、4、5这四条记录按id分组，因为这四条记录的id都不同，所以每一条都查寻出来了。

2.时间格式化：

SELECT date_format('2022-07-06 16:26:20.125', '%Y-%m-%d %H:%i:%S')

年：
    %Y     四位数字表示的年份（2015,2016...）
    %y     两位数字表示的年份（15,16...）

月：
    %M     英文月名（January,February, ...,December）
    %b     英文缩写月名（Jan,Feb, ...,Dec）
    %m     两位数字表示月份（01,02, ...,12）
    %c     数字表示月份（1,2, ...,12）

日：
    %d     两位数字表示月中天数（01,02, ...,31）
    %e     数字表示月中天数（1,2, ...,31）
    %D     英文后缀表示月中天数（1st,2nd,3rd ...）
    %j     以三位数字表示年中天数（001,002, ...,366）

时：
    %H    24小时制，两位数形式小时（00,01, ...,23）
    %h    12小时制，两位数形式小时（00,01, ...,12）
    %k    24小时制，数形式小时（0,1, ...,23）
    %l    12小时制，数形式小时（0,1, ...,12）
    %T    24小时制，时间形式（HH:mm:ss）
    %r    12小时制，时间形式（hh:mm:ss AM 或 PM）
    %p    AM上午或PM下午

分：
    %I、%i    两位数字形式的分（ 00,01, ..., 59）

秒：
    %S、%s    两位数字形式的秒（ 00,01, ..., 59）

周：
    %W    一周中每一天的名称（Sunday,Monday, ...,Saturday）
    %a    一周中每一天名称的缩写（Sun,Mon, ...,Sat）
    %w    以数字形式标识周（0=Sunday,1=Monday, ...,6=Saturday）
    %U    数字表示周数，星期天为周中第一天
    %u    数字表示周数，星期一为周中第一天

文字输出：
    %文字     直接输出文字内容

3.获取当前时间：now()

4.find_in_set函数：

find_in_set（a,b）a单个内容，而b是多个内容

5.使用group by函数后，获取最新日期的那一条数据

select member_id,max(add_time),name from print_share_log where store_id = #{storeId} and share_id = #{shareId} group by member_id

在group by 之后，可以直接使用max函数

---------------------------------------------------------------------------------------------------------------------------------

需求1：分组获取某天的数据数量，例如每一天新注册的用户数量，效果图：

select date_format(created_time,'%Y-%m-%d') as created_time,count(created_time) as couont from print_user where created_time is not null group by date_format(created_time,'%y-%m-%d') order by created_time desc;

解释：查询表 print_user 然后先排除 create_time 为 null 的数据，然后按照 create_time 分组，使用 date_format(created_time,'%y-%m-%d') 来进行格式化，例如格式化的结果为 '2023-01-09' 那就按照这个时间来分组，然后select 做统计
注：avg（）忽略值为null的行，count（*）时统计所有行，count（列）时忽略为null的行

对应的mybatisplus：
QueryWrapper<PrintUser> printUserQueryWrapper = new QueryWrapper<>();
            printUserQueryWrapper
                    .select("date_format(created_time,'%Y-%m-%d') as created_time,count(created_time) as couont")
                    .eq("referral_id",dealerUser.getId())
                    .isNotNull("created_time")
                    .groupBy("date_format(created_time,'%y-%m-%d')")
                    .orderByDesc("created_time");
            // 使用selectmaps 可以直接返回 created_time , couont
            List<Map<String, Object>> mapList = printUserMapper.selectMaps(printUserQueryWrapper);

需求2：获取10天内，每一天的用户新注册数量，没有数据的话，就补0，下图：

这个需求并没有使用sql语句直接写出，查询网上的资料，也并不能完成理解，所以我结合了一下我自己的方法，使用java的map集合以及sql语句，做出类似的结果：

先查询出10天内注册，且有数据的统计一个统计结果，然后把10天内的每一天日期写入到一个map结果：

然后把查询出来的结果再赋值到map中：
        LocalDate now = LocalDate.now();
        Map<String, String> map = new HashMap<>();
        for (int i = 0; i < 10; i++) {
            map.put(now.minusDays(i).toString(),"0");
        }
        System.out.println(map);


        List<Map<String, String>> resultList = printUserMapper.getWithint10DaysNewUserCount();
        for (Map<String, String> Map : resultList) {
            map.put(Map.get("created_time"),Map.get("count"));
        }
        TreeMap<String, String> stringStringTreeMap = new TreeMap<>(map);
        System.out.println(stringStringTreeMap);
mapper接口中的select语句：
@Select(value = "select date_format(created_time, '%Y-%m-%d') as created_time, count(created_time) as count from print_user where created_time >= date_sub(date_format(now(), '%Y-%m-%d'), interval 9 day) group by date_format(created_time, '%Y-%m-%d')")
List<Map<String,String>> getWithint10DaysNewUserCount();

需求3：递归查询mysql中分层菜单，在java中stream流处理结果，变成树

需求图：

该需求的难度较大，我单独写了一篇文章：大家可以去参考一下

mysql数据库递归查询树形结构（适用场景：菜单多级分类，多级关联评论查询）_流连勿忘返的博客-CSDN博客

需求4：模糊匹配字段里面的json数据

需求：
现在在表里面有一个字段pa_data，其中一条数据保存的值：[{"barcode":"169231110065","converts":1.00,"id":"1692358969441239041"}] 然后想对pa_data字段里面的barcode这个key对应的value值做模糊匹配

sql语句：
SELECT * FROM your_table WHERE JSON_EXTRACT(pa_data, '$[*].barcode') REGEXP 'your_string'
在上述SQL语句中，your_table是表名，pa_data是保存JSON字符串的字段名，your_string是你想要模糊匹配的字符串。JSON_EXTRACT(pa_data, '$[*].barcode')用于提取JSON字段中barcode键的值，并将其与REGEXP正则表达式进行匹配。

请注意，在使用REGEXP进行模糊匹配时，你可以编写自己的正则表达式来匹配特定的模式，例如：

your_string：模糊匹配your_string字符串出现在 barcode值的任意位置。
^your_string：模糊匹配以your_string字符串开头的 barcode值。
your_string$：模糊匹配以your_string字符串结尾的 barcode值。

需求5：同一天，同一账号视为一条数据（要用到去重），然后统计七天内，每一天都有几条数据

select
    card_share_log.add_time as add_time ,count(add_time) as count
from
    (select
         DISTINCT member_id,date_format(add_time, '%Y-%m-%d') as add_time
     from card_share_log
     where share_id = #{shareId} and type = '4') card_share_log

where card_share_log.add_time >= date_sub(date_format(now(), '%Y-%m-%d'), interval 6 day) group by date_format(card_share_log.add_time, '%Y-%m-%d')

这里主要是用到了去重以及嵌套查询

6.查询到两个结果集，分别是：A，B，这两个结果集中都有一个字段叫PD，然后要找到A结果集中，PD字段对应的值是在B的结果集中有的数据，以及没有的数据

需求：A的结果集：1,2 B的结果集：2,3

我要找到在A结果集中的数据，在B的结果集中也是有的数据，以及没有的数据

相同的数据：2，不同的数据：1

mysql：
SELECT DISTINCT a.pd
FROM a
INNER JOIN b ON a.pd = b.pd;
这个是找相同的
SELECT DISTINCT a.pd
FROM a
LEFT JOIN b ON a.pd = b.pd
WHERE b.pd IS NULL;
这个是找不同的

7.IFNULL和COALESCE和IF

1. IFNULL 函数接受两个参数，第一个参数是需要检查是否为NULL的值，第二个参数是当第一个参数为NULL时返回的默认值。

2. COALESCE 函数接受一个或多个参数，依次检查参数是否为NULL，返回第一个非NULL值。

3. IF
    SUM(IF(handle_time IS NULL, 1, 0))  countSum

    card_gather;
在这个查询中，使用 IF 函数来判断 handle_time 是否为null。如果是，则返回1，否则返回0。然后，SUM函数将计算返回的值的总和，并将结果命名为countSum。

8.CASE 的语法


CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

9.现在有两个表，一个是用户表，一个是邮件表，然后每天凌晨都会执行一个任务，如果这个用户三十天都没有发送过邮件，那么系统就会自动删除该用户，然后在管理员查看用户列表的时候，要显示该用户还有多少天会被清理

对于这个需求，可以考虑使用定时任务来实现自动删除用户的功能，而不是在每次查询用户列表时都进行关联查询。具体思路如下：

创建一个定时任务，每天凌晨执行一次。
定时任务中，查询邮件表，获取所有发送过邮件的用户ID。
将这些用户ID存储到一个临时表中，作为已发送过邮件的用户列表。
查询用户表，获取所有用户ID。
遍历用户列表，对于每个用户ID，判断是否在已发送过邮件的用户列表中。
如果用户ID存在于已发送过邮件的用户列表中，说明用户发送过邮件，将该用户的清理计时器重置为30天。
如果用户ID不存在于已发送过邮件的用户列表中，说明用户三十天都没有发送过邮件，将该用户删除。
在管理员查看用户列表时，直接从用户表中查询用户信息，并计算出距离清理的天数。
在用户表中添加一个字段，用于存储清理倒计时的天数，每天凌晨定时任务执行时更新该字段的值。

通过这种方式，可以避免在查询用户列表时进行关联查询，提高查询效率。定时任务每天凌晨执行一次，将用户的发送邮件状态进行更新，同时删除符合条件的用户。管理员查看用户列表时，直接查询用户表并计算清理倒计时的天数，而不需要再进行复杂的关联查询操作。

10.需求图片（表设计）：

这个需求需要用到一个数据统计流水表，记录每一个用户，当天所发生的操作数量，这个功能里面，有一个明星表，然后每一个用户都会对这个明星做关联，表示某一个明星是由该用户进行对接，然后这个明星表里面有个合作状态的，对应到表上就是达人合作效果，比如：星期一对这个达人的合作状态是：已合作，那么在星期一进行统计的时候，就要记录这个用户的合作数量加1，但是星期二在统计合作状态的时候，如果这个达人的合作状态没有变过，那就不能合作数量加1，所以要在明星表里面加一个合作状态的变化时间字段，用来判断这个合作状态的变化时间，比如星期一达成合作的，那变化时间就是星期一，在统计的时候，就可以进行加1，然后在星期二进行统计的时候，因为合作时间没有变化，还是星期一，所以就知道，这个明星的合作状态没变过，所以就不用对他进行统计

流连勿忘返

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记录项目中的select场景语句以及mybatis中的对应操作，如：分组补0，递归查询

表如下，如何让这个表按device_id这个字段分组，且组中的每条数据都查寻出来？正确的结果：select * from devicedata GROUP BY device_id, id。需求1：获取某天的数据数量，例如：获取2023年3月8号，新注册的用户数量。这样就达到了我们的目的了，将每组中的每条数据都查寻出来了。2、where子句基于指定的条件对记录行进行筛选；3、group by子句将数据划分为多个分组；1、from子句组装来自不同数据源的数据；4、使用聚集函数进行计算；6、计算所有的表达式；
复制链接

扫一扫