从表格顶部选择，每列1行

Question

我有一个项目表，每个项目可以有一个或多个工作流。每个工作流程都由一个或多个步骤组成。请看下面用数据定义的表：(@project，@workflow，@step，@workflowSteps)。

根据下面显示的数据，我们有两个项目: p1和p2。@ProjectWorkflow表链接项目和工作流。p1分配了2个工作流，而p2只分配了1个工作流。

工作流1有以下步骤:启动、设计审批、完成工作流2有:启动、设计、实施、测试、完成。

随着项目从一个步骤进行到下一个步骤，我们将一条记录插入到@ProjectWorkflowSteps表中。下表定义中的注释描述了每个项目所采取的步骤。

还有一个条件需要考虑。工作流程可以更改。因此，可以为项目分配工作流2。它可以在该工作流中进行几个步骤，并且用户可以将工作流更改为工作流1。请参阅下面@ProjectWorkflowSteps的注释。

现在，我想查询所有项目的列表(每个项目一行)，第一列是projectID，第二列是用逗号分隔的步骤列表，但我只想要每个工作流的最后一步。因此，基本上，查询将返回项目和每个项目所处的当前步骤。对于包含多个工作流的项目(p1)，它将返回每个工作流的当前步骤，如下所示：

1    approve, implement
2    start

额外的帮助将返回以下结果：

1    approve: 3/4, implement 3/5
2    start: 1/4   

基本上，对于每个工作流，列出当前步骤，到目前为止已完成的步骤/工作流中的总步骤数。

任何帮助都是非常感谢的。

谢谢。

编辑：

感谢Gyromite的您的回答。我在这里使用它来澄清这个问题。

DECLARE @project TABLE (projectID INT PRIMARY KEY, name VARCHAR(50));
INSERT @project
VALUES
(1, 'p1'),
(2, 'p2');


DECLARE @Step TABLE (ID INT PRIMARY KEY, name VARCHAR(50));
INSERT @Step
VALUES
(1, 'start'),
(2, 'finish'),
(3, 'test'),
(4, 'implement'),
(5, 'approve'),
(6, 'design');
DECLARE @Workflow TABLE (ID INT PRIMARY KEY, name VARCHAR(50));
INSERT @Workflow
VALUES
(1, 'workflow1'),
(2, 'worfklow2');

DECLARE @WorkflowSteps TABLE (ID INT PRIMARY KEY, workflowID INT, stepID     INT);
INSERT @WorkflowSteps
VALUES
(1, 1, 1), --workflow1 steps: start, design approve, finish
(2, 1, 6),
(3, 1, 5),
(4, 1, 2),

(5, 2, 1),  --Workflow2: start, design, implement, test, finish
(6, 2, 6),
(7, 2, 4),
(8, 2, 3),
(9, 2, 2);


DECLARE @projectWorkflow TABLE (projectworkflowID INT PRIMARY KEY, projectID INT, workflowID INT);
INSERT @projectWorkflow
VALUES
(1, 1, 1), --proejct 1 has two workflows
(2, 1, 2),
(3, 2, 2);  --project 2 has only one workflow


DECLARE @ProjectWorkflowSteps TABLE (PWSID INT PRIMARY KEY, projectworkflowID INT, projectID INT, workflowID INT, stepID INT);
INSERT @ProjectWorkflowSteps (PWSID, projectWorkflowID, projectID, workflowID, stepID)
VALUES
(1, 1,1,1,1),  --project 1 has 2 workflows assigned to it
(2, 1,1,1,6),  --on workflow 1 it progressed thru 2 steps 
(3, 1,1,1,5),  --and is currently on the third step: approve step (ID=5)

(4, 2,1,2,1),  --project 1 -> second workflow, also progressed 2 steps
(5, 2,1,2,2),  --and is currently on third stepID = 4: 'implement'
(6, 2,1,2,4),  --


(7, 3,2,2,1), --project 2 was assigned workflowID 2 for the first two steps. 
(8, 3,2,2,2), -- then it was changed to workflowID = 1
(9, 3,2,1,1); -- PWID is still 3 here, but workflowID is changed from 2 -> 1

编辑2:下面的查询给了我想要的，但我想知道它是否可以以更有效的方式执行。

    SELECT p.projectID,
Stuff ((SELECT ',  ' + s.name 
           FROM   (SELECT * 
                   FROM   @projectWorkflow) pw 
                  LEFT OUTER JOIN (SELECT * 
                                   FROM   (SELECT projectworkflowID, 
                                  pwsID, 
                                  stepID, 
                       Row_number() OVER(partition BY ProjectworkflowID 
                                      ORDER BY pwsid DESC) rn 
                                           FROM   @ProjectWorkflowSteps 
                                           WHERE  projectID = p.projectID) 
                                          ppp 
                                   WHERE  rn = 1) pws 
                               ON pw.projectworkflowID = pws.projectworkflowid 
                  INNER JOIN @step s 
                          ON s.ID = pws.stepID
           FOR xml path('')), 1, 1, '') [Steps]
FROM   @project p 
GROUP  BY p.projectID

Answer 1

希望这能帮助你得到你想要的结果。

DECLARE @ProjectStatus TABLE (PWID INT, projectID INT, workflowID INT, stepID INT);
INSERT @ProjectStatus (PWID, projectID, workflowID, stepID)
    VALUES
    (1,1,1,1),
    (1,1,1,2),
    (1,1,1,3),
    (2,1,2,1),
    (2,1,2,2),
    (2,1,2,4),
    (3,2,2,1),
    (3,2,2,2),
    (3,2,1,1);
DECLARE @Step TABLE (ID INT PRIMARY KEY, name VARCHAR(50));
INSERT @Step
    VALUES
    (1, 'start'),
    (2, 'design'),
    (3, 'approve'),
    (4, 'implement');
SELECT PS1.projectID
    ,stuff( -- using Stuff to remove the leading comma in the concatenated string.
        (   SELECT ', ' + St.name
            FROM
            (   SELECT PS2.projectID, PS2.workflowID, MAX(PS2.stepID) AS stepID
                FROM @ProjectStatus AS PS2
                GROUP BY PS2.projectID, PS2.workflowID
            ) AS PSWorkflowStep
            INNER JOIN @Step AS St ON St.ID = PSWorkflowStep.stepID
            WHERE PSWorkflowStep.projectID = PS1.projectID
            ORDER BY PSWorkflowStep.workflowID
            for xml path('') -- For XML PATH is the method I'm using for concatenating multiple rows into one.
            -- This next line translates the XML back to text (to avoid issues with special characters @,&,<,>) but it is expensive in CPU.  You may remove it if you don't need it.
            , root('XMLVal'), type).value('/XMLVal[1]','varchar(max)'
        )
    , 1, 2, '') AS workflowSteps
FROM @ProjectStatus AS PS1
GROUP BY PS1.projectID
ORDER BY PS1.projectID

返回的结果如下：

projectID   workflowSteps
1   approve, implement
2   start, design

描述中有几个部分我不理解，所以我做了几个假设。看起来您的第一个示例表中可能存在错误，其中步骤ID 1被注释为"start“和"design"，所以我假设步骤ID 1应该是"start”。此外，我看不出你谈到的“工作流程可以改变”这一段的相关性，所以我在示例中省略了这一点。最后，关于你的奖励帮助结果，我没有从你的模型中看到如何区分给定工作流的当前步骤，所以我没有尝试这一部分。

将来，如果您可以包含T-SQL脚本来重新创建您的示例(表和数据)，这将使其他人更容易帮助您编写查询。