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

coder 2023-10-26 原文

想象一下，允许一名 worker 在定义的时间段内进行可变数量的轮类。我们想要查询任何期间内超过允许数量的类次以及所有期间之外的类次。

我已将 db-fiddle 与测试查询相关联。这里的问题是:

对于超额类次，合并类次的顺序是不确定的。我只想看到多余的类次(2016-05-30 是期间的第 4 个类次，期间只授权了 3 个类次)。
我还想查看完全未授权的 3 个类次(2019-04-25、2019-06-02、2019-06-04)。

我希望我需要翻转查询(即从 Shift join Authorization 中选择)并使用 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

然后添加rank列——相关子查询

测试结果:

DB-Fiddle

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

有关mysql - SQL 查询返回超出范围内允许数量的记录的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2
为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。最佳答案参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。关于ruby-为什么4.1%2使用Ruby返
ruby - Sinatra:运行 rspec 测试时记录噪音 - 2
Sinatra新手；我正在运行一些rspec测试，但在日志中收到了一堆不需要的噪音。如何消除日志中过多的噪音？我仔细检查了环境是否设置为:test，这意味着记录器级别应设置为WARN而不是DEBUG。spec_helper:require"./app"require"sinatra"require"rspec"require"rack/test"require"database_cleaner"require"factory_girl"set:environment,:testFactoryGirl.definition_file_paths=%w{./factories./test/
ruby - 触发器 ruby 中 3 点范围运算符和 2 点范围运算符的区别 - 2
请帮助我理解范围运算符...和..之间的区别，作为Ruby中使用的“触发器”。这是PragmaticProgrammersguidetoRuby中的一个示例:a=(11..20).collect{|i|(i%4==0)..(i%3==0)?i:nil}返回:[nil,12,nil,nil,nil,16,17,18,nil,20]还有:a=(11..20).collect{|i|(i%4==0)...(i%3==0)?i:nil}返回:[nil,12,13,14,15,16,17,18,nil,20] 最佳答案触发器(又名f/f)是
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
ruby - 检查字符串是否包含散列中的任何键并返回它包含的键的值 - 2
我有一个包含多个键的散列和一个字符串，该字符串不包含散列中的任何键或包含一个键。h={"k1"=>"v1","k2"=>"v2","k3"=>"v3"}s="thisisanexamplestringthatmightoccurwithakeysomewhereinthestringk1(withspecialcharacterslike(^&*$#@!^&&*))"检查s是否包含h中的任何键的最佳方法是什么，如果包含，则返回它包含的键的值？例如，对于上面的h和s的例子，输出应该是v1。编辑:只有字符串是用户定义的。哈希将始终相同。最佳答案
ruby-on-rails - 相关表上的范围为 "WHERE ... LIKE" - 2
我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que
ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2
我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？最佳答案 Isthisabug,or
ruby-on-rails - Rails 5 Active Record 记录无效错误 - 2
我有两个Rails模型，即Invoice和Invoice_details。一个Invoice_details属于Invoice，一个Invoice有多个Invoice_details。我无法使用accepts_nested_attributes_forinInvoice通过Invoice模型保存Invoice_details。我收到以下错误:(0.2ms)BEGIN(0.2ms)ROLLBACKCompleted422UnprocessableEntityin25ms(ActiveRecord:4.0ms)ActiveRecord::RecordInvalid(Validationfa
ruby-on-rails - RSpec:避免使用允许接收的任何实例 - 2
我正在处理旧代码的一部分。beforedoallow_any_instance_of(SportRateManager).toreceive(:create).and_return(true)endRubocop错误如下:Avoidstubbingusing'allow_any_instance_of'我读到了RuboCop::RSpec:AnyInstance我试着像下面那样改变它。由此beforedoallow_any_instance_of(SportRateManager).toreceive(:create).and_return(true)end对此:let(:sport_

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

有关mysql - SQL 查询返回超出范围内允许数量的记录的更多相关文章

随机推荐