关键词:
【中文标题】Mysql - 使用多个连接表查找丢失的日期【英文标题】:Mysql - Finding missing dates with multiple join tables 【发布时间】:2014-02-27 19:16:12 【问题描述】:我需要一些帮助来编写一个 mysql 查询。我需要查找特定业务 ID/项目 ID 的给定日期范围内所有错过的报告。
基本上,对于给定的企业 ID,我需要知道项目的名称以及报告缺失或未标记为已完成的所有日期。
我正在使用日历表技巧(如 here 和 here 所述)来查找缺少的报告日期,但我在加入项目表以查找缺少报告的相关项目/业务时遇到问题.
我基本上需要一个结果集,它会给我类似这样的数据:
+------------+-----------+--------------+
| project_id | name | missing_date |
+------------+-----------+--------------+
| 1 | Project 1 | 2014-01-01 |
| 1 | Project 1 | 2014-01-03 |
| 1 | Project 1 | 2014-01-04 |
| 1 | Project 1 | 2014-01-07 |
| 1 | Project 1 | 2014-01-09 |
| 2 | Project 2 | 2014-01-02 |
| 2 | Project 2 | 2014-01-03 |
| 2 | Project 2 | 2014-01-04 |
+------------+-----------+--------------+
这是我的架构:
projects table:
+----------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+----------------+
| project_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| business_id | int(10) unsigned | NO | MUL | NULL | |
| name | tinytext | YES | | NULL | |
+----------------+------------------+------+-----+-------------------+----------------+
reports table:
+---------------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+-------------------+----------------+
| report_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| project_id | int(10) unsigned | NO | MUL | NULL | |
| report_date | date | NO | MUL | NULL | |
| completed | bit(1) | NO | | b'0' | |
+---------------------+------------------+------+-----+-------------------+----------------+
calendar table:
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| dt | date | NO | PRI | NULL | |
| month_name | varchar(9) | YES | | NULL | |
| day_name | varchar(9) | YES | | NULL | |
| y | smallint(6) | YES | | NULL | |
| q | tinyint(4) | YES | | NULL | |
| m | tinyint(4) | YES | | NULL | |
| d | tinyint(4) | YES | | NULL | |
| dw | tinyint(4) | YES | | NULL | |
| w | tinyint(4) | YES | | NULL | |
| is_weekday | bit(1) | YES | | NULL | |
| is_holiday | bit(1) | YES | | NULL | |
| holiday_desc | varchar(32) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
下面的查询可以返回未完成报告的列表,但我仍然需要用根本没有报告记录的日期来填补空白。
select
p.project_id,
p.name,
c.dt as missing_date,
r.completed
from reports r
join projects p on (r.project_id = p.project_id)
right join calendar c on (c.dt = r.report_date)
where c.dt >= '2014-02-01'
and c.dt <= '2014-02-10'
-- and r.report_date is null /** THE RESULT SET IS EMPTY IF I UNCOMMENT THIS **/
and r.completed = false
and c.is_holiday = false
and c.is_weekday = true
and p.business_id = 1001
order by p.project_id, r.report_date, c.dt;
任何帮助将不胜感激!
【问题讨论】:
您的查询结果是什么?为什么要对日历表进行右连接? 我得到一个空的结果集。我需要加入日历表,以便确定应该创建报告的所有日期。 我看不出需要 right 连接,为什么要包含所有 calendar 记录而不匹配 reports记录? 您可以尝试拆分查询并检查子查询的结果:A:“日历没有连接,只有日期”,B:“日历没有连接和完整的where语句”,C:“报告加入日历”,... 【参考方案1】:我阅读了您提供的链接,这种存储日期的方法非常棒。 您在查询中使用的任何方式:
right join calendar c on (c.dt = r.report_date)
但我在您的报告表中没有看到任何 report_date。 如果问题是因为它,我建议你检查一下,因为除此之外你的查询似乎工作正常。
【讨论】:
抱歉,报告表确实包含 report_date 列。我在发布示例模式时不小心删除了它。我已经编辑了示例架构以显示它现在在那里。【参考方案2】:好的,我终于让它工作了。这是一个相当复杂的查询,需要在项目表上进行内部连接,但我不知道更好的方法 - 我是一个 Java 人,这些天过于依赖休眠方式来构建我的查询:)。如果有人有更有效的解决方案,我会全力以赴!
最终查询:
select
p.project_id,
p.name,
c.dt as missing_date,
r.report_date,
r.completed
from calendar c
inner join (
select
p1.project_id,
p1.name
from projects p1
where p1.project_id = 1005
-- where p1.business_id = 1001 /** OR USE THE BUSINESS ID **/
) p on c.dt between '2014-02-01' and '2014-02-28'
left join reports r on r.report_date = c.dt
and r.project_id = p.project_id
and r.completed = false
where (r.report_date is null or r.completed = false)
and c.is_holiday = false
and c.is_weekday = true
order by p.project_id, c.dt;
产生正确的结果:
+------------+--------------+--------------+-------------+-----------+
| project_id | name | missing_date | report_date | completed |
+------------+--------------+--------------+-------------+-----------+
| 1005 | Project 1005 | 2014-02-03 | 2014-02-03 | 0 |
| 1005 | Project 1005 | 2014-02-04 | 2014-02-04 | 0 |
| 1005 | Project 1005 | 2014-02-05 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-06 | 2014-02-06 | 0 |
| 1005 | Project 1005 | 2014-02-07 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-10 | 2014-02-10 | 0 |
| 1005 | Project 1005 | 2014-02-11 | 2014-02-11 | 0 |
| 1005 | Project 1005 | 2014-02-12 | 2014-02-12 | 0 |
| 1005 | Project 1005 | 2014-02-13 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-14 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-18 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-19 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-20 | 2014-02-20 | 0 |
| 1005 | Project 1005 | 2014-02-21 | 2014-02-21 | 0 |
| 1005 | Project 1005 | 2014-02-24 | 2014-02-24 | 0 |
| 1005 | Project 1005 | 2014-02-25 | 2014-02-25 | 0 |
| 1005 | Project 1005 | 2014-02-26 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-27 | NULL | NULL |
| 1005 | Project 1005 | 2014-02-28 | NULL | NULL |
+------------+--------------+--------------+-------------+-----------+
感谢各位朋友的帮助!
【讨论】:
在多个字段上连接 mysql 表
...返回包含相关团队名称和徽标的游戏列表。现在,我正在使用他们的 查看详情
MySQL 无法在 FROM 多个表连接中指定要更新的目标表
...了解如何在尝试更新多连接表中的字段时解决此错误,并使用同一多连接表集中的最短日期。这是我的更新声明:updatevtiger_projectmilestoneIn 查看详情
在 MySQL 中的表的多个列中查找连接的字符串
】在MySQL中的表的多个列中查找连接的字符串【英文标题】:LookupforaconcatanatedstringinmultiplecolumnsinatableinMySQL【发布时间】:2012-02-0506:18:37【问题描述】:我有一个地址字符串的单个用户输入文本框,即它不分为街区号、街道地址... 查看详情
使用多个连接查找最小值
】使用多个连接查找最小值【英文标题】:FindingMinimumValueswithMultipleJoins【发布时间】:2021-04-1418:53:24【问题描述】:我在BigQuery中使用StandardSQL,并且有7个表,每个表有10到75列和数千行。为简单起见,我只会将相关的表和列用... 查看详情
如何使用 MySQL 连接语句选择与链接表中的多个值匹配的记录?
】如何使用MySQL连接语句选择与链接表中的多个值匹配的记录?【英文标题】:HowcanIselectrecordsthatmatchmultiplevaluesinalinkingtableusingaMySQLjoinstatement?【发布时间】:2020-04-2314:18:42【问题描述】:我正在尝试修复用于产品过滤的查询,... 查看详情
mysql使用内部连接根据数据库中的日期范围显示数据
】mysql使用内部连接根据数据库中的日期范围显示数据【英文标题】:mysqldisplaydatabasedondaterangefromdatabaseusinginnerjoin【发布时间】:2019-11-2012:09:46【问题描述】:我有两个表事件和会话,看起来像这样事件表会话表我想根据日期范... 查看详情
如何正确使用连接/子查询从多个表中选择数据? (PHP-MySQL)
】如何正确使用连接/子查询从多个表中选择数据?(PHP-MySQL)【英文标题】:Howtoselectdatafrommultipletablesusingjoins/subqueryproperly?(PHP-MySQL)【发布时间】:2012-06-2521:58:23【问题描述】:我有如下图所示的三个表。注意:projectheader表的前... 查看详情
加入多个表时从表中选择最大日期
...个查询来检索最大调度日期,并从多个表中引入字段。我使用其他表中的字段,因为包含发送日期的表不包含我要查找的任何信息。(希望有意义)这是我目前正在使用的代码,但它不起作用:这是我开始使用代码之前得到的结... 查看详情
如何从一个表中查找多个列满足不同条件的记录?
...entcriteria?【发布时间】:2016-08-1014:08:16【问题描述】:我使用的是MSSQLServer2012。我正在尝试创建一个以@nint作为参数的存储过程。该过程基本上是尝试根据记录的日期早于另一个表中的某些日期,从一个表中查找需要重新计算 查看详情
使用多个“和”连接多个表
】使用多个“和”连接多个表【英文标题】:joinmultipletableswithmultiple\'and\'【发布时间】:2014-06-0617:15:53【问题描述】:我有3张桌子要合并。一个是Personnel列表,一个是Personnel表中每个人的Schedules列表,第三个是每个人的临时计... 查看详情
mysql入门指南4(查询进阶,外连接)(代码片段)
...f0c;数据表操作) MySQL入门指南2(SQLyog使用,增删改查) MySQL入门指南3(常用函数)目录一、查询进阶 1.查询增强 2.多表查询 3.子查询 4.... 查看详情
使用多个重复的最大日期时间检索每个组中的最大日期时间记录 - MySQL 问答
】使用多个重复的最大日期时间检索每个组中的最大日期时间记录-MySQL问答【英文标题】:Retrievingthemaxdatetimerecordineachgroupwithmultipleduplicatemaxdatetime-MySQLAsk【发布时间】:2020-10-0316:52:57【问题描述】:有一个名为\'**work**\'的表包... 查看详情
来自连接表的 MySQL 更新语句(受该表的最新值限制)
...table)【发布时间】:2020-04-2921:25:51【问题描述】:我正在使用MariaDB5.5我有2个表,我想根据表B上的信息更新表A中的一列。表B有多个我要查找的ID条目,但每个注册表都有一个updated_at列,所以我只想获取最新 查看详情
MySQL:查找日期范围之间的缺失日期
】MySQL:查找日期范围之间的缺失日期【英文标题】:MySQL:FindMissingDatesBetweenaDateRange【发布时间】:2011-07-0117:19:40【问题描述】:我需要一些有关mysql查询的帮助。我有db表,其中包含2011年1月1日至2011年4月30日的数据。每个日期... 查看详情
从查找表中获取每个类型和每个日期的最大 ID
...据列有多个日期,下图仅显示01.01.2021。问题:t1是我需要使用的查找表,我的挑战是它不包含供参考的日期列。selec 查看详情
在多个表上进行条件 mysql 连接的最有效方法? (Yii php 框架)
...:我有五张桌子:tab_template模板组群用户组用户Tab_template使用template_group关系表组织成组。使用user_g 查看详情
错误代码:2013。查询期间丢失与 MySQL 服务器的连接
...ery【发布时间】:2012-05-2018:34:25【问题描述】:当我尝试使用MySQLWorkbench向表添加索引时,我收到错误代码:2013。在查询期间丢失与MySQL服务器的连接错误。我还注意到,每当我运行长查询时它就会出现。有没有办法增加超时值... 查看详情
错误代码:2013。查询期间丢失与 MySQL 服务器的连接
...ery【发布时间】:2012-05-2018:34:25【问题描述】:当我尝试使用MySQLWorkbench向表添加索引时,我收到错误代码:2013。在查询期间丢失与MySQL服务器的连接错误。我还注意到,每当我运行长查询时它就会出现。有没有办法增加超时值... 查看详情