mysql分组求组内的最大最小值
可能有一段时间没写代码,今天在项目里发现了一个bug,有点对自己无语,特此记录下~
(1)数据准备
《节假日表》
CREATE TABLE `app_calendar_holiday` (
`holiday_name` varchar(50) NOT NULL COMMENT '假期名',
`holiday_date` varchar(10) NOT NULL COMMENT '假期的日期(yyyy-MM-dd)',
`rel_date` varchar(10) NOT NULL COMMENT '节假日相关联的日子',
`rel_date_status` int(1) NOT NULL COMMENT '节假日相关联的日是否上班( 1:放假,2:上班)',
`is_delete` int(1) NOT NULL DEFAULT '0' COMMENT '删除flg',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE KEY `rel_date` (`rel_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='假期表';
-- ----------------------------
-- Records of app_calendar_holiday
-- ----------------------------
INSERT INTO `app_calendar_holiday` VALUES ('元旦', '2019-01-01', '2018-12-29', '2', '0', '2019-09-01 23:08:09', '2019-09-02 10:48:11');
INSERT INTO `app_calendar_holiday` VALUES ('元旦', '2019-01-01', '2018-12-30', '1', '0', '2019-09-01 23:08:10', '2019-09-02 10:48:16');
INSERT INTO `app_calendar_holiday` VALUES ('元旦', '2019-01-01', '2018-12-31', '1', '0', '2019-09-01 23:08:09', '2019-09-02 10:48:17');
INSERT INTO `app_calendar_holiday` VALUES ('元旦', '2019-01-01', '2019-01-01', '1', '0', '2019-09-01 23:08:10', '2019-09-02 10:48:18');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-02', '2', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:22');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-03', '2', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:23');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-04', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:24');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-05', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:25');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-06', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:25');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-07', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:26');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-08', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:26');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-09', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:27');
INSERT INTO `app_calendar_holiday` VALUES ('春节', '2019-02-05', '2019-02-10', '1', '0', '2019-09-01 23:08:11', '2019-09-02 10:48:27');
INSERT INTO `app_calendar_holiday` VALUES ('清明节', '2019-04-05', '2019-04-05', '1', '0', '2019-09-01 23:08:12', '2019-09-02 10:59:26');
INSERT INTO `app_calendar_holiday` VALUES ('清明节', '2019-04-05', '2019-04-06', '1', '0', '2019-09-01 23:08:12', '2019-09-02 10:59:28');
INSERT INTO `app_calendar_holiday` VALUES ('清明节', '2019-04-05', '2019-04-07', '1', '0', '2019-09-01 23:08:13', '2019-09-02 10:59:30');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-04-28', '2', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:31');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-05-01', '1', '0', '2019-09-01 23:08:13', '2019-09-02 10:48:45');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-05-02', '1', '0', '2019-09-01 23:08:13', '2019-09-02 10:48:46');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-05-03', '1', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:47');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-05-04', '1', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:47');
INSERT INTO `app_calendar_holiday` VALUES ('劳动节', '2019-05-01', '2019-05-05', '2', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:48');
INSERT INTO `app_calendar_holiday` VALUES ('端午节', '2019-06-07', '2019-06-07', '1', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:53');
INSERT INTO `app_calendar_holiday` VALUES ('端午节', '2019-06-07', '2019-06-08', '1', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:54');
INSERT INTO `app_calendar_holiday` VALUES ('端午节', '2019-06-07', '2019-06-09', '1', '0', '2019-09-01 23:08:14', '2019-09-02 10:48:55');
INSERT INTO `app_calendar_holiday` VALUES ('中秋节', '2019-09-13', '2019-09-13', '1', '0', '2019-09-01 23:08:16', '2019-09-02 10:48:59');
INSERT INTO `app_calendar_holiday` VALUES ('中秋节', '2019-09-13', '2019-09-14', '1', '0', '2019-09-01 23:08:16', '2019-09-02 10:49:00');
INSERT INTO `app_calendar_holiday` VALUES ('中秋节', '2019-09-13', '2019-09-15', '1', '0', '2019-09-01 23:08:16', '2019-09-02 10:49:01');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-09-29', '2', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:04');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-01', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:05');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-02', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:06');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-03', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:06');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-04', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:06');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-05', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:07');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-06', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:08');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-07', '1', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:10');
INSERT INTO `app_calendar_holiday` VALUES ('国庆节', '2019-10-01', '2019-10-12', '2', '0', '2019-09-01 23:08:17', '2019-09-02 10:49:11');
(2)需求明确
查询条件:某年某月 yyyy-MM
查询结果:求出在该月里 的节假日
例子:条件为2019-09,返回中秋节的2019-09-13 ~ 2019-09-15 ,国庆节的放假日2019-10-1~2019-10-7 。
并输出对应的节假日日期、节假日名。
(3)错误log
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'panshi.t1.holiday_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
提示的 panshi.t1.holiday_name 无法取出。
(4)错误sql
SELECT
t1.holiday_date AS holidayDate,
t1.holiday_name AS holidayName,
MIN(t1.rel_date) AS minRelDate,
MAX(t1.rel_date) AS maxRelDate
FROM
app_calendar_holiday t1
INNER JOIN (
SELECT DISTINCT
holiday_date
FROM
`app_calendar_holiday`
WHERE
LEFT (rel_date, 7) = '2019-09'
) t2 ON t1.holiday_date = t2.holiday_date
WHERE
t1.rel_date_status = 1
GROUP BY
t1.holiday_date;
(5)纠正后的SQL
纠正:select 的字段要是group by 所允许的!!!追加分组的字段 t1.holiday_date
方法一:分组 + 每组里使用聚合函数
SELECT
t1.holiday_date AS holidayDate,
t1.holiday_name AS holidayName,
MIN(t1.rel_date) AS minRelDate,
MAX(t1.rel_date) AS maxRelDate
FROM
app_calendar_holiday t1
INNER JOIN (
SELECT DISTINCT
holiday_date
FROM
`app_calendar_holiday`
WHERE
LEFT (rel_date, 7) = '2019-09'
) t2 ON t1.holiday_date = t2.holiday_date
WHERE
t1.rel_date_status = 1
GROUP BY
t1.holiday_date,
t1.holiday_name;
方法二:分组 + 每组里日期排序,再取第一个
SELECT
t1.holiday_date AS holidayDate,
t1.holiday_name AS holidayName,
SUBSTRING_INDEX(
GROUP_CONCAT(
t1.rel_date
ORDER BY
t1.rel_date ASC
),
',',
1
) AS minRelDate,
SUBSTRING_INDEX(
GROUP_CONCAT(
t1.rel_date
ORDER BY
t1.rel_date DESC
),
',',
1
) AS maxRelDate
FROM
app_calendar_holiday t1
INNER JOIN (
SELECT DISTINCT
holiday_date
FROM
`app_calendar_holiday`
WHERE
LEFT (rel_date, 7) = '2019-09'
) t2 ON t1.holiday_date = t2.holiday_date
WHERE
t1.rel_date_status = 1
GROUP BY
t1.holiday_date,
t1.holiday_name;
结果:都是
(6)拓展和总结
两者方法都需要注意,如果SELECT ( 某些字段) FROM xxxx 。。。,SELECT 后的字段必须是group by 后面的,或者 使用聚合函数。
我出错就在于,之前的sql 里 GROPY BY 只是写了 节假日期,但又需要取出节假日名。
方法一:
注意,再mysql 里用到group_concat的时候,拼接后的字符串 是有长度限制的。默认是1024,
可以通过修改mysql配置文件里的【group_concat_max_len】字段
其实 这种情况使用 group_concat + order by 的组合 ,有点多余的,因为已经有聚合函数的出现。
不过想想,如果分组后 是组内 需要按照某些字段排序的,返回 字符串类型的,可以用这个方法