У меня есть представление, в котором есть список заданий, с такими данными, как то, кому они назначены, и этап, на котором они находятся. Мне нужно написать хранимую процедуру, которая возвращает количество заданий, которые есть у каждого человека на каждом этапе.
Пока у меня есть это (упрощенно):
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 было упрощением. Это действительно больше похоже на то, ГДЕ DateStarted НЕ НУЛЬ, а DateFinished НУЛЕ.
Редактировать: @BCS: Мне нравится идея сделать вставку всех сотрудников в первую очередь, поэтому мне просто нужно каждый раз обновлять. Но я изо всех сил пытаюсь получить правильные утверждения UPDATE.
Во 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)
Чтобы получить реальный тип запроса "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
Мне удалось заставить его работать с вариацией ответа 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