mysql - 复杂的 SELECT 语句包含多个未返回所需结果的联接

Question

我正在尝试创建一个 SELECT 来从几个表中返回信息。我让它工作了，但后来收到了额外的要求，现在我无法弄清楚如何获得我想要的东西。

我有一个表格，其中包含有关可以包含在报告中的计划的信息(根据进一步的要求)...该文件称为里程碑。我有另一个表，其中包含与程序相关的项目 - 如果 ID 匹配我有一个新表，其中有手动输入的覆盖结束日期 - 这是新要求。里程碑表中有一个系统结束日期，但如果输入此覆盖日期，则它将优先于系统结束日期。如果输入了覆盖日期，异常(exception)文件将具有相同的程序 ID 和与里程碑表中的日期匹配的两个日期。

日期格式为 yyyy-mm-dd

Example data:

Milestones:

prgId | startDate  | endDate
------------------------------
  123 | 2014-03-09 | 2014-11-10
  123 | 2014-07-10 | 2014-11-10
  324 | 2014-05-09 | 2014-11-12

exceptions:

prgId | startDate  | overEnd
-------------------------------
  123 | 2014-03-09 | 2014-05-31

projects:

prgId | cust
-------------
  123 | 12121
  123 | 4323

我目前退回的是:

prgId       prjCnt       startDate       endDate     overEnd
123           2          2014-03-09     2014-11-10   2014-05-31
123           2          2014-07-10     2014-11-10
324           0          2014-05-09     2014-11-12

我确实意识到，现在计划 123 的两个项目将在两条线上显示 - 我们将寻找一种方法将它们与正确的项目关联起来，但目前还没有。

我们添加了覆盖日期要求，以便当前程序的报告不会同时显示“123”行，而只会显示当前的行(第二行)。

我当前的 SELECT 是这样的(抱歉，我无法更容易地显示它真的很长):

SELECT milestones.*,  newtbl.prjcnt, exceptions.overEnd  
FROM milestones 
LEFT JOIN ((
     SELECT prgGuid, count( prgGuid ) AS prjcnt 
     FROM projects 
     GROUP BY prgGuid 
    ) AS newtbl ) 
ON milestones.prgId = newtbl.prgId 
LEFT JOIN exceptions 
ON (milestones.prgId = exceptions.prgId 
AND milestones.startDate = exceptions.startDate) 
WHERE <(milestones.startDate > '2013-00-00') 
AND (milestones.startDate <= CURDATE() AND milestones.endDate >= CURDATE()) 
ORDER BY milestones.endDate, milestones.startDate DESC 

现在我想要的是将其更改为仅获取程序、项目计数、开始和结束日期以及开始日期为从 2013 年到当前日期且尚未结束的任意程序的覆盖结束日期。现在....如果程序有覆盖结束日期并且该结束日期是当前日期(>= 当前日期)，则应包含它，但如果覆盖日期为 NULL 或 <= 当前日期，我不想包含它。

我想要返回的是:

prgId       prjCnt       startDate       endDate     overEnd
123           2          2014-07-10      2014-11-10
324           0          2014-05-09      2014-11-12

之前的第一行已过期，因此不应显示。

我尝试了一些方法，但最终要么没有结果，要么得到了我当前得到的一切。

有人可以帮我弄清楚 SELECT 应该是什么吗？

Answer 1

因此，如果我关注您，您的 DDL 可能如下所示:

CREATE TABLE MILESTONES
    (`prgId` int, `startDate` varchar(10), `endDate` varchar(10))
;

INSERT INTO MILESTONES
    (`prgId`, `startDate`, `endDate`)
VALUES
    (123, '2014-03-09', '2014-11-10'),
    (123, '2014-07-10', '2014-11-10'),
    (324, '2014-05-09', '2014-11-12')
;

CREATE TABLE EXCEPTIONS
    (`prgId` int, `startDate` varchar(10), `overEnd` varchar(10))
;

INSERT INTO EXCEPTIONS
    (`prgId`, `startDate`, `overEnd`)
VALUES
    (123, '2014-03-09', '2014-05-31')
;

CREATE TABLE PROJECTS
    (`prgId` int, `cust` int)
;

INSERT INTO PROJECTS
    (`prgId`, `cust`)
VALUES
    (123, 12121),
    (123, 4323)
;

您当前不起作用的查询是这样的(请注意，我已经更正了我认为问题中的查询中的拼写错误):

SELECT milestones.*,  newtbl.prjcnt, exceptions.overEnd  
FROM milestones 
LEFT JOIN ((
     SELECT prgId, count( prgId ) AS prjcnt 
     FROM projects 
     GROUP BY prgId 
    ) AS newtbl ) 
ON milestones.prgId = newtbl.prgId 
LEFT JOIN exceptions 
ON (milestones.prgId = exceptions.prgId 
AND milestones.startDate = exceptions.startDate) 
WHERE (milestones.startDate > '2013-00-00') 
AND (milestones.startDate <= CURDATE() AND milestones.endDate >= CURDATE()) 
ORDER BY milestones.endDate, milestones.startDate DESC 

可行的解决方案如下所示:

SELECT DISTINCT
  M.prgId as PRGID
, ( SELECT COUNT(X.prgID)
    FROM PROJECTS X
    WHERE X.prgID = M.prgID ) as PRJCNT
, M.startDate as STARTDATE
, M.endDate as ENDDATE
, COALESCE(E.overEnd,'') as OVEREND
FROM MILESTONES M
  LEFT OUTER JOIN PROJECTS P
    ON M.prgId = P.prgId
  LEFT JOIN EXCEPTIONS E
    ON M.prgId = E.prgId
   AND M.startDate = E.startDate
WHERE M.startDate > '2013-01-01'
  AND M.startDate <= CURDATE()
  AND M.endDate >= CURDATE()
  AND ( E.overEnd IS NULL
     OR E.overEnd > CURDATE() )

您可以在此处查看它的实际效果:SQLFiddle .

请注意，该解决方案依赖于 COALESCE 函数来实现干净的输出，并且更多的业务规则被放置在 WHERE 子句中。