MySQL(组最小值查询)

Question

我有这个表 my_table，其中包含 3 列，如下所示

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL
,date DATE NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,date)
);

INSERT INTO my_table VALUES
(38,'2014-02-16', 3),
(38,'2014-02-17', 3),
(38,'2014-02-18', 2),
(38,'2014-02-19', 1),
(38,'2014-02-20', 3),
(38,'2014-02-21', 3),
(38,'2014-02-22', 3),
(38,'2014-02-24', 3),
(38,'2014-02-25', 3),
(38,'2014-02-26', 3),
(38,'2014-02-27', 3),
(38,'2014-02-28', 3),
(38,'2014-03-01', 3),
(38,'2014-03-02', 2),
(38,'2014-03-03', 2);

是否可以通过查询将其展平/分组，以便输出为

38, '2014-02-16', 3
38, '2014-02-18', 2
38, '2014-02-19', 1
38, '2014-02-20', 3
38, '2014-03-02', 2

这让我困惑了将近 3 天；创建临时表并添加用于分组的技巧列 ID 对我没有任何好处

我希望你能帮助我。

谢谢

Answer 1

试试这个

SELECT t1.employee_id, t1.date, t1.section_id FROM
tableA t1
LEFT JOIN tableA t2
ON t1.date = ADDDATE( t2.date, INTERVAL 1 DAY)
WHERE ISNULL(t2.section_id) OR t1.section_id <> t2.section_id
ORDER BY t1.date

但这可能非常低效，具体取决于您的情况。