SQL需求
筛选同一部门，同一供应商，30天内采购累计金额达到5万元（含）的记录；
建表语句
CREATE TABLE `same_dept` (
`id` int(10) NOT NULL COMMENT '报账单id',
`dept_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '部门名称',
`account` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '报账金额',
`create_time` date DEFAULT NULL COMMENT '创建时间',
`end_time` date DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `same_dept` VALUES (1,'部门1','20000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (2,'部门1','30000','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (3,'部门1','10000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (4,'部门2','20000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (5,'部门2','20000','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (6,'部门2','10000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (7,'部门3','1000','2022-06-25','2022-06-30');
INSERT INTO `same_dept` VALUES (8,'部门3','900','2022-07-24','2022-06-29');
INSERT INTO `same_dept` VALUES (9,'部门3','49000','2022-07-30','2022-07-31');
INSERT INTO `same_dept` VALUES (10,'部门3','1000','2022-08-01','2022-08-01');
INSERT INTO `same_dept` VALUES (11,'部门3','40000','2022-09-01','2022-09-03');
INSERT INTO `same_dept` VALUES (12,'部门3','10000','2022-09-15','2022-09-17');
脚本SQL–方法一（选择最小时间）
最主要的逻辑是要选好参照时间，没有参照时间变数太多了。
# 获取最小时间
select dept_name,min(create_time) from same_dept group by dept_name;
# 根据最小时间 除以 30 进行 分组
select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name
# 针对分组 进行统计总数
select
t.dept_name,
t.group_id,
sum(account)
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) t
group by dept_name,group_id
# 让部门 、分组 作为条件去匹配所有字段
select
z.id,
z.dept_name,
z.account,
z.create_time,
z.group_id,
z1.sum_accout
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) z
inner join
(select
t.dept_name,
t.group_id,
sum(account) as sum_accout
from
(select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
(select dept_name,min(create_time) as min_create_time from same_dept group by dept_name) b on a.dept_name = b.dept_name) t
group by dept_name,group_id) z1 on z.dept_name = z1.dept_name and z.group_id = z1.group_id
------------------------------------------------
------------------------------------------------
# 下面是创建建立临时表
# 获取最小时间
drop table if exists min_time_temp;
create table min_time_temp as
select dept_name,min(create_time) as min_create_time from same_dept group by dept_name;
# 根据最小时间 除以 30 进行 分组
drop table if exists group_temp;
create table group_temp as
select
a.id,
a.dept_name,
a.account,
a.create_time,
b.min_create_time,
round(TIMESTAMPDIFF(DAY,b.min_create_time,a.create_time)/30) as group_id
from same_dept a
inner join
min_time_temp b on a.dept_name = b.dept_name;
# 针对分组 进行统计总数
drop table if exists group_sum_temp;
create table group_sum_temp as
select
t.dept_name,
t.group_id,
sum(account)
from
group_temp t
group by dept_name,group_id;
# 让部门 、分组 作为条件去匹配所有字段
select
z.id,
z.dept_name,
z.account,
z.create_time,
z.group_id,
z1.sum_accout
from
group_temp z
inner join
group_sum_temp z1 on z.dept_name = z1.dept_name and z.group_id = z1.group_id
开始查询
最终查询 – 最后再判断是否大于5万，即可
那么问题来了，如果你以3月10号为最小时间，那么4月10和4月11号，这两个就会在不同分组里面，这个是不符合逻辑的。
脚本SQL–方法二（合理利用笛卡尔积和去重）
注意数据展示方式是以可供理解的方式展示，但是实际数据排序不一样，但是最终的值是一样的。
第一部分 样例取：15天内的数据
表结构为：编号,部门,时间,数量
# 假装是排序好的 数据，进行笛卡尔积关联， 左边是根据部门进行时间的降序排序 右边也是根据部门进行降序排序
数据如下
a编号,a部门,a时间，b编号,b部门,b时间 相差天数
1 0001 2023-03-30 0001 2023-03-30 0天
1 0001 2023-03-30 0001 2023-03-20 10天
1 0001 2023-03-30 0001 2023-03-15 15天
1 0001 2023-03-30 0001 2023-03-10 20天
2 0001 2023-03-20 0001 2023-03-30 -10天
2 0001 2023-03-20 0001 2023-03-20 0天
2 0001 2023-03-20 0001 2023-03-15 5天
2 0001 2023-03-20 0001 2023-03-10 10天
3 0001 2023-03-15 0001 2023-03-30 -15天
3 0001 2023-03-15 0001 2023-03-20 -10天
3 0001 2023-03-15 0001 2023-03-15 0天
3 0001 2023-03-15 0001 2023-03-10 5天
这里我们对相差天数进行0<=X<=15天的判断
我们取后半部分数据 ，那么将会有以下三种数据成组出现 （为何取右半部分，因为根据前面的的数据，我们可以判断出后半部分是不通的值）
一组： 2023-03-30
2023-03-20
2023-03-15
二组： 2023-03-20
2023-03-15
2023-03-10
二组： 2023-03-15
2023-03-10
第二部分--存疑
# 根据上面的，就存在 2023-03-30、2023-03-20、2023-03-15为一组 ,2023-03-20、2023-03-15、2023-03-10为一组 这是必须筛选出来的，这样的数据是我们想要的
# 但是如果出现 2023-03-20、2023-03-15、2023-03-10为一组 ，2023-03-15、2023-03-10又为一组，那么2023-03-15、2023-03-10就会出现一组重复数据，我们必须要筛选掉它
二组： 2023-03-20
2023-03-15
2023-03-10
二组： 2023-03-15
2023-03-10
第三部分--解决
# 怎么筛选掉？我的方法 是给上面的数据设置最小时间列（为何要设置最小时间列?因为是倒叙往下排的。如果是升序就最大时间列）设置完如下
最小时间列
二组： 2023-03-20 2023-03-10
2023-03-15 2023-03-10
2023-03-10 2023-03-10
二组： 2023-03-15 2023-03-10
2023-03-10 2023-03-10
# 然后根据最小的时间列进行group by 或者ditsinct 进行去重
#根据 最小时间去重 去重结果为
数据如下
一组： 2023-03-30
2023-03-20
2023-03-15
二组： 2023-03-20
2023-03-15
2023-03-10
# 然后再根据的出来的数据 去关联相关信息 获取统计值 或其他方式
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
