SQL 服务器 - T-SQL 案例 检查最年轻的日期，然后检查其他值

Question

编辑：重新制作了整个问题。第一次带错了。

结果应如下所示：

MachineName | OrderNo | TaskID | Code | NettoProd | BrutoProd | DiffProd
=========================================================================
F1            123456    101      O      100000      125000      25000
F1            123456    102      P8     1000000     1250000     250000
F1            123456    103      P1     10000       12500       2500
F1            123456    104      P4     100000      125000      25000

JobSummary包含以下列：

StartDate 
TaskID

Route具有以下列：

OrderNo 
TaskID

Resource保存有关机器的数据

MachineID

我已经添加了凯尔·戈贝尔的部分，如果我首先提供了所有细节，这是正确的答案。

我想完成的是显示一个O，当它是链接到一个基于OrderNo的任务的第一taskID时，StartDate是表中的一个DateTime字段。

如果无法在一个CASE语句中同时检查Route.No和JobSummary.StarDate，那么为两者单独列也可以。

查询

SELECT Resource.DESCRIPTION AS MachineName
    ,Route.OrderNo
    ,Route.TaskID
    ,CASE
        WHEN JobSummary.StartDate = (SELECT MIN(cr.StartDate) FROM JobSummary cr) THEN 'O'
        WHEN Route.No = 1 OR Route.No = 2 THEN 'P1'
        WHEN Route.No = 4 THEN 'P4'
        WHEN Route.No >= 8 THEN 'P8'
        ELSE '*FP*'
    END AS Code
    ,JobSummary.GoodProd As NettoProd
    ,JobSummary.GoodProd + JobSummary.SetupProd + JobSummary.WasteProd As BrutoProd
    ,(JobSummary.SetupProd + JobSummary.WasteProd) As DiffProd
FROM Route
JOIN Resource ON Resource.MachineID = Route.MachineID
JOIN JobSummary ON JobSummary.TaskID = Route.TaskID AND JobSummary.MachineID = Route.MachineID

Answer 1

您可以将

ROW_NUMBER()与PARTITION BY一起使用，根据其StartDate来标识第一条记录taskID

，以用于如下所示的OrderNo ROW_NUMBER()OVER(PARTITION BY Route.OrderNo ORDER BY JobSummary.StartDate ASC

示例数据和结构

CREATE TABLE [JobSummary] (MachineID INT,TaskID INT,StartDate DATETIME,GoodProd NUMERIC(18,0),SetupProd NUMERIC(18,0),WasteProd NUMERIC(18,0));
CREATE TABLE [Route] (OrderNo INT,MachineID INT,TaskID INT,[No] INT);
CREATE TABLE [Resource] (MachineID INT,DESCRIPTION CHAR(2));

INSERT INTO [Resource] VALUES(1,'F1');
INSERT INTO [Route] VALUES(123456,1,101,1);
INSERT INTO [Route] VALUES(123456,1,102,9);
INSERT INTO [Route] VALUES(123456,1,103,2);
INSERT INTO [Route] VALUES(123456,1,104,4);
INSERT INTO [JobSummary] VALUES(1,101,'20150101',100000,20000,5000);
INSERT INTO [JobSummary] VALUES(1,102,'20150103',1000000,200000,50000);
INSERT INTO [JobSummary] VALUES(1,103,'20150102',10000,2000,500);
INSERT INTO [JobSummary] VALUES(1,104,'20150103',100000,20000,5000);

查询

;WITH CTE AS 
(
SELECT Route.No
    ,Resource.[DESCRIPTION] AS MachineName
    ,Route.OrderNo
    ,Route.TaskID
    ,JobSummary.GoodProd As NettoProd
    ,JobSummary.GoodProd + JobSummary.SetupProd + JobSummary.WasteProd As BrutoProd
    ,(JobSummary.SetupProd + JobSummary.WasteProd) As DiffProd
    ,ROW_NUMBER()OVER(PARTITION BY Route.OrderNo ORDER BY JobSummary.StartDate ASC) rn
FROM Route
JOIN Resource ON Resource.MachineID = Route.MachineID
JOIN JobSummary ON JobSummary.TaskID = Route.TaskID AND JobSummary.MachineID = Route.MachineID
)
SELECT
    MachineName,
    OrderNo,
    TaskID,
    CASE
        WHEN rn = 1 THEN 'O'
        WHEN No IN (1,2) THEN 'P1'
        WHEN No = 4 THEN 'P4'
        WHEN No >= 8 THEN 'P8'
        ELSE '*FP*'
    END AS Code,
    NettoProd,
    BrutoProd,
    DiffProd
FROM CTE
ORDER BY OrderNo,TaskID

输出

MachineName OrderNo TaskID  Code    NettoProd   BrutoProd   DiffProd
F1  123456  101 O   100000  125000  25000
F1  123456  102 P8  1000000 1250000 250000
F1  123456  103 P1  10000   12500   2500
F1  123456  104 P4  100000  125000  25000

SQL 小提琴

Answer 2

通过阅读您的问题，您可能正在寻找最小聚合函数。

case 
   when route.startDate = (select min(r.startdate) from table r) then 'O'
   when route.no = 1 or route.no = 2 then 'P1'
   ....
end as code

Answer 3

在这里使用 ROW_NUMBER 可能是一个很好的起点，但由于您只想检查第一个StartDate，因此改用 MIN() OVER 可能是一个更好的主意。您发布的查询只需要稍作更改(强调(：

SELECT Resource.DESCRIPTION AS MachineName
    ,Route.OrderNo
    ,Route.TaskID
    ,CASE
        WHEN JobSummary.StartDate = MIN(JobSummary.StartDate) OVER (PARTITION BY Route.OrderNo) THEN 'O'
        WHEN Route.No = 1 OR Route.No = 2 THEN 'P1'
        WHEN Route.No = 4 THEN 'P4'
        WHEN Route.No >= 8 THEN 'P8'
        ELSE '*FP*'
    END AS Code
    ,JobSummary.GoodProd As NettoProd
    ,JobSummary.GoodProd + JobSummary.SetupProd + JobSummary.WasteProd As BrutoProd
    ,(JobSummary.SetupProd + JobSummary.WasteProd) As DiffProd
FROM Route
JOIN Resource ON Resource.MachineID = Route.MachineID
JOIN JobSummary ON JobSummary.TaskID = Route.TaskID AND JobSummary.MachineID = Route.MachineID
;

也许 PARTITION BY 也需要包括Resource.DESCRIPTION——很难从一个小例子中分辨出来。

上述查询可能比ROW_NUMBER方法工作得更快。这是因为对于ROW_NUMBER子集需要完全排序，而 MIN(( OVER 只会从每个子集中查找单个值。

另一方面，如果以后有可能需要第二行、第三行等特殊条件，ROW_NUMBER方法在这方面肯定会更灵活。