如何对mysql表中的行进行分组，直到值发生变化？(不使用变量)

Question

我有一个名为“employees”的表，我只想按行的指定进行分组，直到它改变为止。

请查看示例表。

Id  Name    Designation     Employee Id Last Modified   
1   John    General Manager     1       15-10-2001  
2   John    General Manager     1       05-11-2001  
3   John    Product Manager     1       06-11-2001  
4   John    Product Manager     1       15-11-2001  
5   John    General Manager     1       30-12-2001  

预期输出

Employee Id Name    Designation     Valid From  Valid to
1           John    General Manager 15-10-2001  05-11-2001
1           John    Product Manager 06-11-2001  15-12-2001
1           John    General Manger  16-12-2001  30-12-2001

我试着使用group by子句，但它只给了我2行(总经理、产品经理)。但是我想要一个输出，如表所示，我可以解决一个更大的问题，包括每个角色的开始日期和结束日期。提前谢谢！.

Answer 1

给你：

WITH cte1 AS(
  SELECT EmployeeId, 
        LastModified,
        Designation,
        LEAD(Designation) OVER (ORDER BY EmployeeId, LastModified) AS NextDesg,
        LAG(LastModified) OVER (ORDER BY EmployeeId, LastModified) AS PrevLm
   FROM mytable),
  cte2 AS (
SELECT *,
       ROW_NUMBER()
           OVER (PARTITION BY EmployeeId ORDER BY LastModified) AS NewGroup
  FROM cte1
WHERE (Designation != NextDesg OR NextDesg IS NULL)
  ORDER BY LastModified) 
  
SELECT t.EmployeeId, t.Name, t.Designation, 
       MIN(t.LastModified) AS "Valid From", 
       MAX(t.LastModified) AS "Valid To",
       COALESCE(c1.NewGroup, c2.NewGroup) AS NwGr
  FROM mytable t
  LEFT JOIN cte2 c1 
ON t.EmployeeId=c1.EmployeeId
 AND t.LastModified=c1.LastModified
 LEFT JOIN cte2 c2
ON t.EmployeeId=c2.EmployeeId
 AND t.LastModified=c2.PrevLm
  GROUP BY t.EmployeeId, t.Name, t.Designation, NwGr;

这里的主要思想是将每个集合指定为一个新的组，然后根据结果对其进行分组。

我们从两次执行公共表表达式开始。第一项是：

WITH cte1 AS(
  SELECT EmployeeId, 
        LastModified,
        Designation,
        LEAD(Designation) OVER (ORDER BY EmployeeId, LastModified) AS NextDesg,
        LAG(LastModified) OVER (ORDER BY EmployeeId, LastModified) AS PrevLm
   FROM mytable)

我使用LEAD()获取要用作分隔符的下一个Designation值，使用LAG()获取最后查询中的第二个LEFT JOIN在ON中使用的前一个LastModified值。

第二cte

cte2 AS (
SELECT *,
       ROW_NUMBER()
           OVER (PARTITION BY EmployeeId ORDER BY LastModified) AS NewGroup
  FROM cte1
WHERE (Designation != NextDesg OR NextDesg IS NULL)
  ORDER BY LastModified)

这里我使用了使用LEAD() (第一个cte )返回的值来标识Designation中的更改，然后添加ROW_NUMBER()函数作为NewGroup生成。

然后在最后一个查询中：

...
FROM mytable t
  LEFT JOIN cte2 c1 
ON t.EmployeeId=c1.EmployeeId
 AND t.LastModified=c1.LastModified
 LEFT JOIN cte2 c2
ON t.EmployeeId=c2.EmployeeId
 AND t.LastModified=c2.PrevLm
...

我用cte2从主表中做了两次cte2，第一次是通过EmployeeId和LastModified进行匹配，第二次是通过EmployeeID和从LAG()函数获得的以前的LastModified值进行匹配。

演示小提琴