sql-server - T-SQL CASE 检查最年轻的日期，然后对照其他值

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

路线具有以下列:

OrderNo 
TaskID

资源保存有关机器的数据

MachineID

如果我一开始就提供了所有详细信息，我已经添加了凯尔·戈贝尔的部分，这就是正确的答案。

我想要完成的是，当它是链接到一个 OrderNo 的任务的第一个 taskID 时，显示一个 O。 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 结合使用，根据 OrderNo 的 StartDate 来识别第一条记录 taskID > 像这样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 Fiddle