mysql - SQL Query返回超出范围内允许量的记录

假设允许工人在定义的时间段内有可变数量的轮班。我们要查询任何期间超出允许数量的班次以及所有期间之外的班次。
我已将db fiddle与测试查询链接起来。这里的问题是：
对于过量移位，合并移位的顺序是不确定的。我只想看到超额班次（2016-05-30是一段时间内的第4个班次，只有3个班次获得授权）。
我也希望看到3个完全没有授权的班次（2019-04-25、2019-06-02、2019-06-04）。
我希望我需要翻转查询（即从shift-join授权中选择），并使用group-by、order-by和limit的组合，但是我没有成功。如有任何意见，将不胜感激。

CREATE TABLE `Authorization` (
  `AuthorizationId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `WorkerId` int(10) unsigned NOT NULL,
  `Start` date NOT NULL,
  `End` date NOT NULL,
  `ShiftsAllowed` int(10) unsigned NOT NULL,
  PRIMARY KEY (`AuthorizationId`)
);

CREATE TABLE `Shift` (
  `ShiftId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `WorkerId` int(10) unsigned NOT NULL,
  `Date_` date NOT NULL,
  PRIMARY KEY (`ShiftId`)
);

INSERT INTO Authorization (WorkerId,Start,End,ShiftsAllowed) VALUES
(1,'2019-05-01','2019-05-15',2),
(1,'2019-05-16','2019-05-31',3);

INSERT INTO Shift (WorkerId,Date_) VALUES
(1,'2019-04-25'),
(1,'2019-05-01'),
(1,'2019-05-10'),
(1,'2019-05-16'),
(1,'2019-05-20'),
(1,'2019-05-25'),
(1,'2019-05-30'),
(1,'2019-06-02'),
(1,'2019-06-04');

select
    Authorization.Start,
    Authorization.End,
    Authorization.ShiftsAllowed,
    count(Shift.Date_),
    group_concat(Shift.Date_),
    reverse(
      substring_index(
        reverse(group_concat(Shift.Date_)),
        ',',
        count(Shift.Date_) - Authorization.ShiftsAllowed
      )
    )
from Authorization
left join Shift
on
    Shift.WorkerId = Authorization.WorkerId
    and Shift.Date_ between Authorization.Start and Authorization.End
group by Authorization.AuthorizationId
having
count(Shift.ShiftId) > Authorization.ShiftsAllowed

View on db-fiddle

最佳答案

因为mysql 5.7不支持窗口功能。
我使用了相关子查询而不是它。
试试这个：

select a3.shiftid, a3.workerid, a3.date_ from (
select a2.*,
(select count(*)
 from (select s.*, a.start, a.end, a.shiftsallowed
        from Shift s
        left join Authorization a
        on a.workerid = s.workerid
        and s.date_ between a.start and a.end) a1
        where a1.date_ <= a2.date_
        and a1.workerid = a2.workerid and a1.start = a2.start and a1.end  = a2.end) rnk
from (
select s.*, a.start, a.end, a.shiftsallowed
from Shift s
left join Authorization a
on a.workerid = s.workerid
and s.date_ between a.start and a.end )a2)a3
where a3.rnk > a3.shiftsallowed or rnk = 0

如果您不理解答案，我建议您运行此查询。

select s.*, a.start, a.end, a.shiftsallowed
from Shift s
left join Authorization a
on a.workerid = s.workerid
and s.date_ between a.start and a.end

然后添加秩列——相关子查询
测试结果：
DB-Fiddle

关于mysql - SQL Query返回超出范围内允许量的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56116626/