- 需求
查询小说发布到50000字记录的章节信息
- 章节表
CREATE TABLE `t_chapter` (
`id` varchar(255) NOT NULL COMMENT '主键',
`num` int(11) DEFAULT NULL COMMENT '发布字数',
`production_number` varchar(11) NOT NULL COMMENT '作品编号',
`pub_time` datetime DEFAULT NULL COMMENT '发布时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 思路:
章节信息按照发布时间排序,使用mysql自定义变量特性进行累加
- SQL实现
SELECT
id ,
a.auto_code AS autoCode ,
a.production_number AS productionNumber ,
a.pub_time AS pubTime ,
a.num ,
(@sumSalary := @sumSalary + num) AS count
FROM
t_chapter a ,
(SELECT @sumSalary := 0) b
WHERE
a.production_number = 1414(作品编号)
ORDER BY
a.pub_time ASC
查询结果如下:
- 添加条件count大于等于50000取第一条
SELECT
*
FROM
( SELECT
id ,
a.auto_code AS autoCode ,
a.production_number AS productionNumber ,
a.pub_time AS pubTime ,
a.num ,
(@sumSalary := @sumSalary + num) AS count
FROM
t_chapter a ,
(SELECT @sumSalary := 0) b
WHERE
a.production_number = 1414
ORDER BY
a.pub_time ASC
) AS p
WHERE
count >= 50000 LIMIT 0,1;
不了解@MYSQL自定义变量可以参考: