如何将我的两个查询合并为 1？

debugcn 发表于 Dev

用户1452574

所以我有：

SELECT
  od.PartNo,
  CAST(od.PartDesc AS NVARCHAR(MAX)) AS [PartDesc],
  MAX(CAST(t.TicketDate AS DATETIME)) AS [EndDate]
FROM TimeTicketDet t JOIN OrderDet od ON t.JobNo = od.JobNo
  JOIN OrderRouting r ON t.JobNo = r.JobNo
WHERE t.WorkCntr = 500
  AND r.Status = 'Finished'
GROUP BY od.PartNo, CAST(od.PartDesc AS NVARCHAR(MAX))
ORDER BY MIN(CAST(t.TicketDate AS DATETIME)) DESC

和

SELECT
  od.PartNo,
  CAST(od.PartDesc AS NVARCHAR(MAX)) AS [PartDesc],
  MIN(CAST(t.TicketDate AS DATETIME)) AS [StartDate]
FROM TimeTicketDet t JOIN OrderDet od ON t.JobNo = od.JobNo
  JOIN OrderRouting r ON t.JobNo = r.JobNo
WHERE t.WorkCntr = 100
GROUP BY od.PartNo, CAST(od.PartDesc AS NVARCHAR(MAX))
ORDER BY MIN(CAST(t.TicketDate AS DATETIME)) DESC

我需要 MIN 和 MAX 列在 1 个查询中，但是因为我在 WHERE 子句中指的是 2 个不同的工作中心，所以我不确定如何去做。我试过这个：

SELECT
  od.PartNo,
  CAST(od.PartDesc AS NVARCHAR(MAX)) AS [PartDesc],
    (SELECT
       MIN(CAST(t.TicketDate AS DATETIME)) AS [StartDate]
     FROM TimeTicketDet t JOIN OrderDet od ON t.JobNo = od.JobNo
       JOIN OrderRouting r ON t.JobNo = r.JobNo
     WHERE t.WorkCntr = 100
     GROUP BY od.PartNo
     ),
  MAX(CAST(t.TicketDate AS DATETIME)) AS [EndDate]
FROM TimeTicketDet t JOIN OrderDet od ON t.JobNo = od.JobNo
  JOIN OrderRouting r ON t.JobNo = r.JobNo
WHERE t.WorkCntr = 500
  AND r.Status = 'Finished'
GROUP BY od.PartNo, CAST(od.PartDesc AS NVARCHAR(MAX))
ORDER BY MIN(CAST(t.TicketDate AS DATETIME)) DESC

这几乎奏效了，除了 MIN 列只是系统中的第一个日期，它与 PartNo/Workcenter 无关，所以我被卡住了。我不确定我是否接近或者我是否应该使用 CTE 之类的其他东西

提前致谢

肖恩00

如果您希望开始和结束作为单独的列，那么case表达式将允许您在更改where条件以允许两组行通过后挑选适当的值。如果所需的开始日期总是早于最终结束日期，正如我想象的那样，您可能甚至不需要那么花哨。你确实提到在第二个结果中有更多的行，所以我不确定它来自哪里，它可能会稍微改变逻辑。

在这种情况下，主要是使用OR.

SELECT
  od.PartNo,
  MIN(CAST(od.PartDesc AS NVARCHAR(MAX))) AS PartDesc,
  MIN(CASE WHEN t.WorkCntr  = 100 THEN CAST(t.TicketDate AS DATETIME) END)) AS StartDate,
  MAX(CASE WHEN t.WorkCntr <> 100 THEN CAST(t.TicketDate AS DATETIME) END)) AS EndDate
FROM
    TimeTicketDet t
    INNER JOIN OrderDet od ON t.JobNo = od.JobNo
    INNER JOIN OrderRouting r ON t.JobNo = r.JobNo
WHERE t.WorkCntr = 100 OR t.WorkCntr = 500 AND r.Status = 'Finished'
GROUP BY od.PartNo
ORDER BY PartDesc DESC;

注意：我选择了结束日期条件是因为它们彼此相反，对我来说它更明显地表明了正在发生的事情。

顺便说一句，如果您将它关联起来，您可以让您的子查询工作：

...
(
    SELECT MIN(CAST(t.TicketDate AS DATETIME))
    FROM OrderDet od2 INNER JOIN TimeTicketDet t
        ON t.JobNo = od2.JobNo
    WHERE t.WorkCntr = 100 AND od2.PartDesc = od.PartDesc
) AS StartDate
...

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。