我有一个视图，其中包含一份工作列表，其中包含诸如被分配给谁以及他们所处的阶段之类的数据。我需要编写一个存储过程，以返回每个人每个阶段有多少工作。

到目前为止，我有这个(简体)：

DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName

问题在于行不合并。因此，如果某个工作人员在stage1和stage2中有工作，则@ResultTable中有两行。我真正想做的是更新工作人员的行，如果不存在则插入新行。

有谁知道该怎么做，或者可以建议其他方法？
我真的很想避免使用游标在用户列表上进行迭代(但这是我的后备选项)。

我正在使用SQL Server 2005.

编辑：@Lee：不幸的是InStage1 = 1是一种简化。它实际上更像是WHERE DateStarted IS NOT NULL和DateFinished IS NULL。

编辑：@BCS：我喜欢首先插入所有职员的想法，因此我每次都必须进行更新。但是我正在努力使那些UPDATE语句正确。

解决方案

回答

要获得真正的" upsert"类型的查询，我们需要使用if if ...类型的东西，不幸的是，这意味着使用游标。

但是，我们可以运行两个查询，一个查询在存在现有行的地方进行更新，然后再插入新查询。我认为这种基于集合的方法将是可取的，除非我们专门处理少量的行。

回答

我们可以检查是否存在并使用适当的命令。我相信这确实在幕后使用了一个游标，但这是我们可能会得到的最好的结果：

IF (EXISTS (SELECT * FROM MyTable WHERE StaffName = @StaffName))
begin
    UPDATE MyTable SET ... WHERE StaffName = @StaffName
end
else
begin
    INSERT MyTable ...
end

SQL2008具有新的MERGE功能，这很酷，但是2005年还没有。

回答

实际上，我认为我们正在使它变得更加困难。此代码对我们要执行的操作无效吗？

SELECT StaffName, SUM(InStage1) AS 'JobsAtStage1', SUM(InStage2) AS 'JobsAtStage2'
  FROM ViewJob
GROUP BY StaffName

回答

结果表上的以下查询应再次合并行。这是假设InStage1和InStage2永远都不为" 1"。

select distinct(rt1.StaffName), rt2.Stage1Count, rt3.Stage2Count
from @ResultTable rt1
left join @ResultTable rt2 on rt1.StaffName=rt2.StaffName and rt2.Stage1Count is not null
left join @ResultTable rt3 on rt1.StaffName=rt2.StaffName and rt3.Stage2Count is not null

回答

IIRC中存在某种"重复"(名称可能错误)语法，可让我们更新是否存在行(MySQL)

或者是以下形式的某种形式：

INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)
  SELECT StaffName,0,0 FROM ViewJob
  GROUP BY StaffName

UPDATE @ResultTable Stage1Count= (
  SELECT COUNT(*) AS count FROM ViewJob
  WHERE InStage1 = 1
  @ResultTable.StaffName = StaffName)

UPDATE @ResultTable Stage2Count= (
  SELECT COUNT(*) AS count FROM ViewJob
  WHERE InStage2 = 1
  @ResultTable.StaffName = StaffName)

回答

我设法使它与BCS的答案一起工作。但是，它不允许我使用表变量，因此我必须制作一个临时表。

CREATE TABLE #ResultTable
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO #ResultTable (StaffName)
  SELECT StaffName FROM ViewJob
  GROUP BY StaffName

UPDATE #ResultTable SET 
  Stage1Count= (
    SELECT COUNT(*) FROM ViewJob V
    WHERE InStage1 = 1 AND 
        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS
    GROUP BY V.StaffName),
  Stage2Count= (
    SELECT COUNT(*) FROM ViewJob V
    WHERE InStage2 = 1 AND 
        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS
    GROUP BY V.StaffName)

SELECT StaffName, Stage1Count, Stage2Count FROM #ResultTable

DROP TABLE #ResultTable