Изначально в обычной таблице есть два столбца Key
и ParentKey
. Key
автоматически получает свое значение как столбец идентификаторов. ParentKey
- по выражению
WITH
latest
as
(
SELECT
ProductID,
Date,
[Key],
ROW_NUMBER() OVER (
PARTITION BY ProductID
ORDER BY [Date] DESC -- order by latest Date
) rn
FROM
[MyTable]
)
UPDATE
u
SET
u.[ParentKey] = latest.[Key]
FROM
[MyTable] u
INNER JOIN
latest
ON u.ProductID = latest.ProductID
WHERE
latest.rn = 1
Можно ли совместить оба этих шага одновременно при создании View (Key и ParentKey становятся виртуальными)? У меня есть решение для первой части этой задачи - создание ключевого столбца в представлении. Можно ли это совместить с шагом, который затем устанавливает ParentKey?
Текущий код
CREATE VIEW v_Test
AS
SELECT
ProductID
,Date,
CAST (ROW_NUMBER() OVER( ORDER BY [ProductID] ) AS int) as [Key]
-- some expression for ParentKey?
FROM [MyTable]
Желаемый вывод представления (Key и ParentKey должны быть обработаны во время создания представления)
+-------------------------------------------------
|ProductID | Date | Key | ParentKey
+-------------------------------------------------
|111 | 2017-12-31 | 1 | 3
|111 | 2018-12-31 | 2 | 3
|111 | 2019-12-31 | 3 | 3
|222 | 2017-12-31 | 4 | 6
|222 | 2018-12-31 | 5 | 6
|222 | 2019-12-31 | 6 | 6
|333 | 2017-12-31 | 7 | 9
|333 | 2018-12-31 | 8 | 9
|333 | 2019-12-31 | 9 | 9
Простое использование: SELECT *, MAX(Key) OVER(PARTITION BY ProductID) Key2 FROM (ваш запрос) x
Если я понял, что вы пытаетесь сделать правильно, вы можете поместить запрос с номером строки в CTE или производную таблицу, а затем сослаться на это в оконном агрегате, чтобы получить макс.
CREATE VIEW v_Test
AS
WITH T
AS (SELECT ProductID,
Date,
ROW_NUMBER() OVER(ORDER BY [ProductID] ASC, [Date] ASC ) AS [Key]
FROM [MyTable])
SELECT ProductID,
Date,
[Key],
MAX([Key]) OVER (PARTITION BY [ProductID]) AS [ParentKey]
FROM T
Эти «ключи» не будут стабильными с течением времени, поскольку они могут измениться после вставок для несвязанных продуктов.
Рекурсивный CTE — это то, что нужно, если ключи могут измениться, но они могут быть дорогостоящими в вычислительном отношении, и существует ограничение на количество рекурсий, по умолчанию оно равно 100, но его можно переопределить с помощью подсказки, если вы не находитесь в таблице. функция, в этом случае она фиксируется на 100. Другим вариантом может быть добавление логики к триггеру. Я не большой поклонник триггеров, их можно отключить и иметь неожиданные последствия при использовании @@IDENTITY, но если отношения никогда не меняются, они могут быть действительны здесь.
@AaronReese - Если у вас есть ответ, опубликуйте его. Не оставляйте это как неуместный комментарий к моему. Я не согласен, что рекурсивные CTE могут здесь что-то предложить.
Извините, мой плохой, я просмотрел сообщение и предположил, что ОП искал иерархические отношения для цепочки доказательств, а ваше представление использовало рекурсивный CTE, поэтому подтверждало ваше намерение. Вы правы, рекурсивный CTE здесь бесполезен. Не уверен, что вам нужен CTE в вашем запросе. OP говорит, что ключ является автоматически заполняемым полем IDENTITY, поэтому вы можете быть уверены, что MAX (ключ) является последней записью.
@AaronReese - столбец IDENTITY является начальным состоянием. ("Изначально в обычной таблице") Почему-то все это дело пытаются заменить "виртуальными" столбцами
Если я правильно вас понял, я считаю, что будет намного короче, используя только MAX с предложением OVER. Что-то вроде
UPDATE
u
SET
u.[ParentKey] =
FROM
[MyTable] u
CROSS APPLY (SELECT MAX( [Key] OVER ( PARTITION BY ProductID ORDER BY [DATE] DESC ) LatestKey ) ca
Как правило: просто определите выражение, например A + B AS Something.