问题:需要根据类型统计出来这个类型的数据并计算这是这个字段第几次出现
效果:如下图这个样子
解决方式:1、创建表时加字段进行记录并且保存
2、SQL 进行查询统计
考虑:在 sql server 中用惯了 row_number 与 over 、然后 My SQL 并没有这个函数、只能进行手动写。数据量不会太多、不进行分页考虑、只查询出数据即可
表:
CREATE TABLE `crm_client_follow` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '编号',
`client_id` varchar(64) NOT NULL COMMENT '客户id',
`follow_date` datetime NOT NULL COMMENT '跟进时间',
`type` int(11) NOT NULL COMMENT '跟进类型',
`process` int(11) NOT NULL COMMENT '跟进进程',
`body` varchar(500) NOT NULL COMMENT '内容',
`user_id` varchar(64) NOT NULL,
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
`del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`remarks` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户跟进记录';
INSERT INTO `crm_client_follow` VALUES ('79e90402d00c4c818f90293c66ccf9ed', '8f3655a1b56c4d42ae30f330b49e2220', '2019-07-24 00:00:00', '0', '0', '阿三打撒', '1', '1', '2019-07-10 10:55:35', '1', '2019-07-10 10:55:35', '0', null);
INSERT INTO `crm_client_follow` VALUES ('f3c17a42ac79401cac09f67fb072c6e1', '8f3655a1b56c4d42ae30f330b49e2220', '2019-07-30 00:00:00', '1', '0', '阿斯蒂', '1', '1', '2019-07-10 10:56:12', '1', '2019-07-10 10:56:12', '0', null);
INSERT INTO `crm_client_follow` VALUES ('ff233e496d874f36aa674ac30c5d1765', '8f3655a1b56c4d42ae30f330b49e2220', '2019-07-31 00:00:00', '0', '0', '阿斯蒂', '1', '1', '2019-07-10 10:55:56', '1', '2019-07-10 10:55:56', '0', null);
SQL:
@ 后面跟个名字,表示自定义变量。
:= 赋值的意思
用 if 进行数据判断、当满足我们的条件时、进行自增
SELECT
result.type,
result.process,
result.client_id,
result.follow_date,
result.cou
FROM
(
SELECT
follow.type,
follow.process,
follow.client_id,
follow.follow_date,
IF (
@puid = follow.client_id
AND @ptype = follow.type,
@rank :=@rank + 1 ,@rank := 1
) AS cou,
@puid := follow.client_id,
@ptype := follow.type
FROM
(
SELECT
type,
process,
client_id,
follow_date,
body
FROM
crm_client_follow
WHERE
client_id = '8f3655a1b56c4d42ae30f330b49e2220'
ORDER BY
create_date ASC
) follow,
(
SELECT
@rownum := 0,
@puid := NULL ,@ptype := NULL ,@rank := 0
) a
) result
ORDER BY
follow_date DESC
效果: