- Вопрос или проблема
- Ответ или решение
- 1. Использование JOIN с предопределённой таблицей чисел
- 2. Использование рекурсивных CTE (Common Table Expressions)
- 3. Использование CROSS APPLY с вспомогательной таблицей чисел
- 4. Использование generate_series в PostgreSQL
- 5. Применение функции table(sequence()) в Trino
- Заключение
Вопрос или проблема
У меня есть следующая таблица.
Таблица A:
ID ProductFK Количество Цена
------------------------------------------------
10 1 2 100
11 2 3 150
12 1 1 120
----------------------------------------------
Мне нужно выбрать строки, повторяющиеся N раз согласно значению в столбце «Количество».
Итак, мне нужен следующий результат запроса:
ID ProductFK Количество Цена
------------------------------------------------
10 1 1 100
10 1 1 100
11 2 1 150
11 2 1 150
11 2 1 150
12 1 1 120
Вы можете использовать простой JOIN для получения желаемого результата, как показано ниже:
SELECT t1.*, t2.number + 1 AS RepeatNumber
FROM TableA t1
JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number < t1.Quantity
Вышеуказанный запрос повторяет каждую запись на указанное количество из столбца Количество.
Примечание для master.dbo.spt_values на type='P':
Эта таблица используется для получения серии чисел, которые жестко закодированы в ней по
условию type='P'.
Вы могли бы сделать это с помощью рекурсивного CTE, используя UNION ALL:
;WITH cte AS
(
SELECT * FROM Table1
UNION ALL
SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
FROM cte INNER JOIN Table1 t
ON cte.[ID] = t.[ID]
WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1
Вот работающий SQLFiddle.
Вот более длинное объяснение этой техники.
Поскольку ваш ввод слишком велик для этой рекурсии, вы можете использовать вспомогательную таблицу, чтобы иметь “много” фиктивных строк, а затем использовать SELECT TOP([Order]) для каждой входной строки (CROSS APPLY):
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER BY 1
(Вспомогательная таблица заимствована из здесь, она позволяет до 65536 строк на каждую входную строку и может быть расширена при необходимости)
Вот работающий SQLFiddle.
Ключевое слово generate_series может быть использовано:
Select ID,ProductFK,1 as Количество, Price from TableA t cross join generate_series(1,t.Quantity);
рассматривая эту таблицу, называемую prep:
и используя функцию table(sequence()) (доступную в trino),
вы можете создать временную таблицу с n числами, присоединить <= это к вашему начальному столбцу «Количество»
select 1 as new_quantity, действие, пользователь
from (
select *
from prep
) t1
left join (
select sequential_number
from table(sequence(1,1000))
) t2
on t2.sequential_number <= t1.quantity
если вам нужен диапазон от 0 до n (как у меня), то просто выберите столбец sequential_number.
CREATE TAblE #temp
(
T_Name VARCHAR(50),
T_Times BIGINT
)
INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4)
INSERT INTO #temp(T_Name,T_Times) VALUES ('PANKAJ',3)
INSERT INTO #temp(T_Name,T_Times) VALUES ('RUPESH',2)
INSERT INTO #temp(T_Name,T_Times) VALUES ('MANISH',5)
SELECT t.T_Name ,t.T_Times FROM
(SELECT T_Name,T_Times,CAST(('<val>'+REPLICATE(T_Name+'</val><val>',T_Times-1)
+'</val>') AS XML )AS X FROM #temp)t CROSS APPLY t.X.nodes('/val')y(z)
drop table #temp
Ответ или решение
Для решения задачи повтора строк в таблице в соответствии со значениями в колонке Quantity, существует несколько методов в SQL. Вы представили таблицу Table A, и результатом выполнения задачи должно быть повторение строк столько раз, сколько указано в колонке Quantity.
Вот несколько подходов для достижения этой цели:
1. Использование JOIN с предопределённой таблицей чисел
Можно использовать предопределённую таблицу чисел, такую как master.dbo.spt_values, чтобы создать необходимое количество строк:
SELECT t1.ID, t1.ProductFK, 1 AS Quantity, t1.Price
FROM TableA t1
JOIN master.dbo.spt_values t2
ON t2.type = 'P' AND t2.number < t1.Quantity
ORDER BY t1.ID;Этот запрос повторяет каждую запись в зависимости от значения в колонке Quantity.
2. Использование рекурсивных CTE (Common Table Expressions)
Если у вас нет доступа к таблице с предопределёнными числами, можно использовать рекурсивное CTE:
;WITH cte AS (
SELECT ID, ProductFK, Quantity, Price
FROM TableA
UNION ALL
SELECT ID, ProductFK, Quantity - 1, Price
FROM cte
WHERE Quantity > 1
)
SELECT ID, ProductFK, 1 AS Quantity, Price
FROM cte
WHERE Quantity > 0
ORDER BY ID;Этот метод более универсален и может использоваться для динамического повтора по снижающимся значениям.
3. Использование CROSS APPLY с вспомогательной таблицей чисел
Можно также создать вспомогательную таблицу чисел с помощью CROSS APPLY:
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.ID, t.ProductFK, 1 AS Quantity, t.Price
FROM TableA t
CROSS APPLY (SELECT TOP(t.Quantity) N FROM E16) ca
ORDER BY t.ID;Этот метод позволяет получить большое количество повторов, что делает его удобным для больших наборов данных.
4. Использование generate_series в PostgreSQL
Если вы используете PostgreSQL, можно воспользоваться конструкцией generate_series для достижения аналогичного результата:
SELECT ID, ProductFK, 1 AS Quantity, Price
FROM TableA t
CROSS JOIN generate_series(1, t.Quantity);5. Применение функции table(sequence()) в Trino
Если вы используете Trino, можно создать последовательность значений и объединить их с оригинальной таблицей:
SELECT 1 AS new_quantity, t.action, t.user
FROM prep t
LEFT JOIN table(sequence(1, 1000)) seq
ON seq.sequential_number <= t.quantity;Этот запрос также позволяет получить необходимое количество повторов.
Заключение
Каждый из этих методов имеет свои преимущества и может быть адаптирован в зависимости от используемой системы управления базами данных. Выберите тот, который соответствует вашим требованиям и состоянию вашей базы данных.