Вид, чтобы возвратить строку, дублированную # раз, представленную значением поля суммы

У меня есть таблица учета, которая содержит поле суммы в долларах. Я хочу создать представление, которое вернет одну строку за копейку этой суммы с некоторыми другими полями из таблицы.

Итак, как очень простой пример, скажем, у меня есть такая строка:

PK Amount Date
---------------------------
123 4.80 1/1/2012

Запрос/представление должен возвращать 480 строк (по одному для каждого копейки), которые выглядят следующим образом:

PK Date
-----------------
123 1/1/2012

Каким будет самый эффективный способ достичь этого? У меня есть решение, которое использует функцию, основанную на таблицах, и временную таблицу, но в глубине моей головы я все время думаю, что для этого нужно сделать это с помощью традиционного представления. Возможно, креативное крестовое соединение или что-то, что вернет этот результат, не объявив слишком много ресурсов в виде временных таблиц, а tbf и т.д. Любые идеи?

2 ответа

С помощью небольшой таблицы numbers вы можете сделать следующее:

select PK, [Date]
from YourTable as T
 inner join number as N
 on N.n between 1 and T.Amount * 100

Если у вас его нет, и вы хотите проверить это, вы можете использовать master..spt_values.

declare @T table
(
 PK int,
 Amount money,
 [Date] date
)
insert into @T values
(123, 4.80, '20120101')
;with number(n) as
(
 select number 
 from master..spt_values
 where type = 'P'
)
select PK, [Date]
from @T as T
 inner join number as N
 on N.n between 1 and T.Amount * 100

Update: Из статьи Джеффа Модена. Таблица "Numbers" или "Tally" : что это такое и как он заменяет цикл.

Таблица Tally - это не что иное, как таблица с одним столбцом очень хорошо проиндексированные последовательные числа, начинающиеся с 0 или 1 (начало мишени при 1) и поднимется до некоторого числа. Наибольшее количество в таблице Tally не должен быть просто произвольным выбором. Он должен основываться на том, что вы думаете, вы будете использовать его для. Я разделил VARCHAR (8000) с моим, так что должно быть не менее 8000 номеров. Поскольку мне иногда нужно генерировать 30 лет, я сохраняю большую часть своей таблицы Tally на уровне 11 000 или более, что более 365,25 дней раз 30 лет.


Вы можете использовать CTE, что-то вроде этого:

WITH duplicationCTE AS 
(
 SELECT PK, Date, Amount, 1 AS Count
 FROM myTable
 UNION ALL
 SELECT myTable.PK, myTable.Date, myTable.Amount, Count+1
 FROM myTable
 JOIN duplicationCTE ON myTable.PK = duplicationCTE.PK
 WHERE Count+1 <= myTable.Amount*100
)
SELECT PK, Date
FROM duplicationCTE
OPTION (MAXRECURSION 0);

Вот SqlFiddle

И, обратите внимание на 0. Это означает, что это может работать бесконечно (опасный битв). В противном случае 32676 - это максимальное количество рекурсий, которые вы можете установить (по умолчанию - 100). Однако, если вы используете 32676 циклов, то, возможно, вам нужно переосмыслить свою логику:)

licensed under cc by-sa 3.0 with attribution.