一条SQL就能解决，为啥要写代码？

公号:咻咻ing

发布于 2021-04-07 15:20:02

7830

发布于 2021-04-07 15:20:02

文章被收录于专栏：公众号:咻咻ing公众号:咻咻ing

SQL（发音为“ ess-que-el”）是一门结构化查询语言，用于与数据库进行通信，它是关系型数据库管理系统的标准语言。SQL语句用于执行任务，例如更新数据库上的数据或从数据库检索数据。

对于后端程序员来说，经常需要与SQL语句打交道，尤其是CRUD程序员来说，SQL语句几乎包含了所有的工作内容。熟练的掌握SQL，可以减少工作做很多代码的编写。

今天，小编就通过实际示例来讲述，如何通过一条SQL语句，来解决复杂的逻辑问题，几乎不用写代码就能轻松满足需求功能。

本文可能比较长，如果下面的需求场景也是大家经常遇到的，可以先把此文收藏起来，方便以后抄作业。说是一条SQL语句，但是写这条SQL语句还是花了不少时间的。

首先来说下需求场景：

公司内部组织了一个ARTS打卡的活动，ARTS打卡简单说明下：

ARTS是左耳朵耗子陈皓在极客时间的专栏左耳听风发起的一个活动。
1. Algorithm：每周至少做一个 leetcode 的算法题
2. Review：阅读并点评至少一篇英文技术文章
3. Tip：学习至少一个技术技巧
4. Share：分享一篇有观点和思考的技术文章
每个单词的首字母连起来就是ARTS。

我们打算做一个ARTS打卡榜单，对于榜单前三名的同学会给予奖励，「榜单的规则」如下：

统计某一个月的打卡榜单。
每周ART分别至少完成一次，算本周打卡成功，如完成4次A，RT没有完成，则不算成功。
本月里的每周都打卡成功，算本月全勤打卡，有其中一周未完成，则不算全勤打卡。
榜单按照ART完成的次数和进行排序，但是完成全勤打卡的优先排序，如完成全勤打卡次数和是12次，可能只完成40次A，则12次排在40次之前。

我们有两张表来存储这些数据，DB数据文件后台回复“arts”即可获取：

user表：用户相关信息

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(45) NOT NULL COMMENT '用户名',
  `email` varchar(45) NOT NULL COMMENT '邮箱',
  `organization` varchar(45) NOT NULL COMMENT '部门',
  `vg` varchar(11) NOT NULL COMMENT '事业部',
  `active` int(11) DEFAULT NULL,
  `account` varchar(45) NOT NULL,
  `extra` varchar(100) DEFAULT NULL,
  `project_name` varchar(100) NOT NULL,
  `project_url` varchar(200) DEFAULT NULL,
  `project_desc` varchar(200) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='用户名';

project表：记录打卡情况

CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '打卡记录id',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `a` int(11) DEFAULT '0' COMMENT 'A完成次数',
  `r` int(11) DEFAULT '0' COMMENT 'R完成次数',
  `t` int(11) DEFAULT '0' COMMENT 'T完成次数',
  `s` int(11) DEFAULT '0' COMMENT 'S完成次数',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '统计时间',
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1782 DEFAULT CHARSET=utf8mb4;

注：每天统计一次今天的打卡次数，写入project表

思考：

大家可以思考一下，如果让你实现上面的需求，你会怎么做？

把数据查出来，通过代码来统计想想都觉得很麻烦。

SQL实现：

小编将一步步讲解如何通过一条SQL来实现上述需求。

根据id关联两张表查询，以统计8月份的情况为例：

select a.id, a.username, p.a as A ,p.r as R, p.t as T, p.s as S, p.time
from `user` a
left join project p  on a.id = p.user_id
where time between '2020-08-01' and '2020-08-31'

+----+----------+------+------+------+------+---------------------+
| id | username | A    | R    | T    | S    | time                |
+----+----------+------+------+------+------+---------------------+
|  1 | 张三     |    0 |    0 |    0 |    0 | 2020-08-16 00:00:00 |
|  1 | 张三     |    1 |    0 |    1 |    0 | 2020-08-23 00:00:00 |
|  1 | 张三     |    1 |    0 |    0 |    0 | 2020-08-03 00:00:00 |
|  1 | 张三     |    1 |    0 |    0 |    0 | 2020-08-11 00:00:00 |
|  1 | 张三     |    0 |    0 |    1 |    0 | 2020-08-10 00:00:00 |
|  1 | 张三     |    0 |    1 |    1 |    0 | 2020-08-09 00:00:00 |
|  1 | 张三     |    1 |    0 |    1 |    0 | 2020-08-30 00:00:00 |
|  2 | 李四     |    1 |    1 |    1 |    0 | 2020-08-14 00:00:00 |
|  2 | 李四     |    1 |    1 |    1 |    0 | 2020-08-31 00:00:00 |
|  2 | 李四     |    1 |    1 |    1 |    0 | 2020-08-18 00:00:00 |
|  3 | 王二     |    1 |    0 |    0 |    0 | 2020-08-15 00:00:00 |
|  3 | 王二     |    1 |    0 |    0 |    0 | 2020-08-14 00:00:00 |
|  3 | 王二     |    1 |    0 |    0 |    0 | 2020-08-29 00:00:00 |
|  3 | 王二     |    1 |    0 |    0 |    0 | 2020-08-23 00:00:00 |
|  3 | 王二     |    1 |    0 |    0 |    0 | 2020-08-21 00:00:00 |
+----+----------+------+------+------+------+---------------------+

这里使用LEFT JOIN 关键字，会从左表 (user) 返回所有的行，即使在右表 (project) 中没有匹配的行。这来保证每个同学的打卡记录都有统计到。

按周来统计打卡情况，同一周的记录相加，如果同一周ART值都大于0，则mark标记为1

select id,username,sum(A) as A,sum(R) as R,sum(T) as T,sum(S) as S,sum(A+R+T) as total, DATE_FORMAT(time, '%Y%u') weeks, (sum(A)>0 and sum(R) >0 and sum(T)>0) as mark
from (select  a.id, p.a as A ,p.r as R, a.username, p.t as T,p.s as S,p.time
    from `user` a
    left join project p  on a.id = p.user_id
    where time between '2020-08-01' and '2020-08-31') as dd
    group by username, weeks;

+----+----------+------+------+------+------+-------+--------+------+
| id | username | A    | R    | T    | S    | total | weeks  | mark |
+----+----------+------+------+------+------+-------+--------+------+
|  1 | 张三     |    1 |    1 |    1 |    0 |     3 | 202032 |    1 |
|  1 | 张三     |    1 |    0 |    1 |    0 |     2 | 202033 |    0 |
|  1 | 张三     |    1 |    0 |    1 |    0 |     2 | 202034 |    0 |
|  1 | 张三     |    1 |    0 |    1 |    0 |     2 | 202035 |    0 |
|  2 | 李四     |    1 |    1 |    1 |    0 |     3 | 202033 |    1 |
|  2 | 李四     |    1 |    1 |    1 |    0 |     3 | 202034 |    1 |
|  2 | 李四     |    1 |    1 |    1 |    0 |     3 | 202036 |    1 |
|  3 | 王二     |    2 |    0 |    0 |    0 |     2 | 202033 |    0 |
|  3 | 王二     |    2 |    0 |    0 |    0 |     2 | 202034 |    0 |
|  3 | 王二     |    1 |    0 |    0 |    0 |     1 | 202035 |    0 |
+----+----------+------+------+------+------+-------+--------+------+
10 rows in set (0.01 sec)

这里使用了DATE_FORMAT函数，根据上面的time字段来按周格式化时间

对应同一周的，使用sum函数求和

根据username，合并整月的打卡情况

select id,username,sum(ifnull(dd.A,0)) as A,sum(ifnull(dd.R,0)) as R,sum(ifnull(dd.T,0)) as T,sum(ifnull(dd.S,0)) as S, sum(ifnull(dd.total,0)) as total, min(dd.mark) as mark, count(weeks) as weeks
from (select id,username,sum(A) as A,sum(R) as R,sum(T) as T,sum(S) as S,sum(A+R+T) as total, DATE_FORMAT(time, '%Y%u') weeks, (sum(A)>0 and sum(R) >0 and sum(T)>0) as mark
    from (select  a.id, p.a as A ,p.r as R, a.username, p.t as T,p.s as S,p.time
        from `user` a
        left join project p  on a.id = p.user_id
        where time between '2020-08-01' and '2020-08-31') as dd
        group by username, weeks) as dd GROUP BY username;

+----+----------+------+------+------+------+-------+------+-------+
| id | username | A    | R    | T    | S    | total | mark | weeks |
+----+----------+------+------+------+------+-------+------+-------+
|  1 | 张三     |    4 |    1 |    4 |    0 |     9 |    0 |     4 |
|  2 | 李四     |    3 |    3 |    3 |    0 |     9 |    1 |     3 |
|  3 | 王二     |    5 |    0 |    0 |    0 |     5 |    0 |     3 |
+----+----------+------+------+------+------+-------+------+-------+
3 rows in set (0.00 sec)

使用min函数计算mark的值，如果mark为0，说明本月未完成打卡

统计本月是否完成打卡，并按照优先完成打卡的和打卡总数来排序

select u.id,u.username,dd.A,dd.R,dd.T,dd.S, dd.total, dd.mark, dd.weeks, (dd.mark > 0 and dd.weeks >= 4) as awarded 
FROM (select id,username,sum(ifnull(dd.A,0)) as A,sum(ifnull(dd.R,0)) as R,sum(ifnull(dd.T,0)) as T,sum(ifnull(dd.S,0)) as S, sum(ifnull(dd.total,0)) as total, min(dd.mark) as mark, count(weeks) as weeks
from (select id,username,sum(A) as A,sum(R) as R,sum(T) as T,sum(S) as S,sum(A+R+T) as total, DATE_FORMAT(time, '%Y%u') weeks, (sum(A)>0 and sum(R) >0 and sum(T)>0) as mark
from (select  a.id, p.a as A ,p.r as R, a.username, p.t as T,p.s as S,p.time
    from `user` a
    left join project p  on a.id = p.user_id
    where time between '2020-08-01' and '2020-08-31') as dd
    group by username, weeks) as dd GROUP BY username) dd
right join user u on u.id = dd.id
order by awarded desc, total desc ;

+----+----------+---+---+---+---+-------+------+-------+---------+
| id | username | A | R | T | S | total | mark | weeks | awarded |
+----+----------+---+---+---+---+-------+------+-------+---------+
|  2 | 李四     | 3 | 3 | 3 | 0 |     9 |    1 |     3 |       0 |
|  1 | 张三     | 4 | 1 | 4 | 0 |     9 |    0 |     4 |       0 |
|  3 | 王二     | 5 | 0 | 0 | 0 |     5 |    0 |     3 |       0 |
+----+----------+---+---+---+---+-------+------+-------+---------+
3 rows in set (0.01 sec)

使用每周都打卡而且打卡周数大于等于4，来判断本月是否完成打卡。

至此，通过步骤4的这条完整SQL语句，就实现了本次的需求。如果要是通过代码实现，你想好怎么做了吗？

想自己操作的，后台回复“ARTS”，获取完成SQL数据文件，导入MySQL即可使用。

结合代码：

我们把这条SQL语句，结合到代码里，使用@Select注解，效果如下：

@Select("<script>" +
            "select u.id,u.username, u.active,ifnull(dd.A,0) as A,ifnull(dd.R,0) as R,ifnull(dd.T,0) as T,ifnull(dd.S,0) as S,u.vg,u.organization, ifnull(dd.total,0) as total, dd.mark as mark, dd.weeks as weeks, (dd.mark > 0 and dd.weeks >= #{weeks}) as awarded \n" +
            "FROM (select id,username, active,sum(ifnull(dd.A,0)) as A,sum(ifnull(dd.R,0)) as R,sum(ifnull(dd.T,0)) as T,sum(ifnull(dd.S,0)) as S, sum(ifnull(dd.total,0)) as total, min(dd.mark) as mark, count(weeks) as weeks\n" +
            "        from (select id,username,active,sum(A) as A,sum(R) as R,sum(T) as T,sum(S) as S,sum(A+R+T) as total, DATE_FORMAT(time, '%Y%u') weeks, (sum(A)>0 and sum(R) >0 and sum(T)>0) as mark\n" +
            "            from (select  a.id, a.active, p.a as A ,p.r as R, a.username, p.t as T,p.s as S,p.time\n" +
            "                from `user` a\n" +
            "                left join project p  on a.id = p.user_id\n" +
            "                where time between #{startTime} and #{endTime}) as dd\n" +
            "                group by username, weeks) as dd GROUP BY username) dd\n" +
            "        right join user u on u.id = dd.id where u.active = 1 " +
            "<if test='vg!=null'>and vg = #{vg} </if>" +
            "<if test='org!=null'>and organization=#{org} </if>" +
            "order by awarded desc, total desc ;" +
            "</script>")
 List<Data> queryData(@Param("org") String org, @Param("vg") String vg, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("weeks") int weeks);

核心代码逻辑功能就实现了，在这里我们定义了一些参数，比如startTime和endTime可以计算自定义时间段的打卡情况，只需要加上Controller接口就完成了。

总结：

在后端开发中，基本使用Mybatis框架自动生成增删该查的代码。对于复杂的逻辑，我们可以通过@Select注解来写SQL解决。而且，如果对SQL语句比较熟练的话，可以少写很多代码逻辑呢！

所以，能通过SQL解决的问题，为啥还要写代码呢？

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-03-25，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

本文分享自咻咻ing 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

数据库

登录后参与评论

0 条评论

热度