У меня есть эти 3 таблицы
Сначала содержит товар с ценой на указанные даты
2-й это таблица предметов
3-я таблица дат, в которой мы хотим показать цену 2-го элемента таблицы на каждую дату.
если продолжительность недоступна в первой таблице, она должна быть 0
with myTable ( item,startdate,enddate,price) as
(
select 'AAAA' ,'16-3-2020','19-3-2020','50' union all
select 'AAAA' ,'16-4-2020','19-4-2020','70' union all
select 'BBB' ,'16-3-2020','19-3-2020','20' union all
select 'BBB' ,'16-4-2020','19-4-2020','90' union all
select 'CCC' ,'16-3-2020','29-3-2020','45' union all
select 'CCC' ,'16-4-2020','19-4-2020','120'
)
select item,startdate,enddate,price from myTable
GO
with itemTable ( item) as
(
select 'AAAA' union all
select 'BBB' union all
select 'CCC'
)
select item from itemTable
GO
with DateTable ( dateItem) as
(
select '16-3-2020' union all
select '19-4-2020' union all
select '20-3-2020'
)
select dateItem from DateTable
GO
и результат моего желания должен быть таким (выше приведены динамические данные)
with mydesireTable (item, [16-3-2020],[19-4-2020],[20-3-2020]) as
(
select 'AAAA' ,'50','70','0' union all ---0 as its not on above data in duration
select 'BBB' ,'20','90','0' union all
select 'CCC' ,'45','120','45'
)
select item, [16-3-2020],[19-4-2020],[20-3-2020] from mydesireTable
Я не уверен, что искать :), так как я хочу написать для него запрос, который возвращает мою таблицу желаний в виде данных (или как во временной таблице)
во 2-й и 3-й таблице нет ключа, поэтому не знаю, как присоединиться
Да itemTable
избыточен. Но для того, чтобы «распределить» ваши даты так, как вам нужно, вам нужно что-то вроде ON DateTable.dateItem BETWEEN myTable.startdate AND myTable.enddate
. Затем вы можете сопоставить несколько записей в MyTable с DateTable.dateitem. Вы получите дополнительный столбец DateTable.dateitem
, который вам нужно повернуть
можешь пожалуйста перевести это на английский :)
: D Я, вероятно, могу опубликовать ответ, но во-первых, являются ли даты в DateTable динамическими или фиксированными? там только три? Потому что вам, вероятно, нужна динамическая кросс-таблица, и я обычно просто говорю «сделайте это на уровне представления».
Глядя на свои данные, можете ли вы перепроверить последнюю строку CCC? Я думаю, что это должно быть 4, 120, 45
@Nick.McDermaid, да, 45 120,45
Один из многих способов сделать это. Это перекрестная таблица статический. Вам нужно явно указать все столбцы (дважды)
Если ваши столбцы являются динамическими, вам необходимо использовать кросс-таблицу динамичный. Вам также следует подумать о том, чтобы сделать это в своем слое «презентация», то есть в Excel или в том, что вы передаете.
Вы должны подумать, что вы хотите, когда что-то в mytable появляется против ведра дважды (это решение добавит цены)
with myTable ( item,startdate,enddate,price) as
(
select 'AAAA' ,CAST('2020-03-16' AS DATE),CAST('2020-03-19' AS DATE),50 union all
select 'AAAA' ,'2020-04-16','2020-04-19',70 union all
select 'BBB' ,'2020-03-16','2020-03-19',20 union all
select 'BBB' ,'2020-04-16','2020-04-19',90 union all
select 'CCC' ,'2020-03-16','2020-03-29',45 union all
select 'CCC' ,'2020-04-16','2020-04-19',120
),
itemTable ( item) as
(
select 'AAAA' union all
select 'BBB' union all
select 'CCC'
)
,DateTable ( dateItem) as
(
select CAST('2020-03-16' AS DATE) union all
select '2020-04-19' union all
select '2020-03-20'
)
SELECT item,
[2020-03-16],[2020-04-19], [2020-03-20]
FROM
(
select item, dateitem, price from myTable
inner join datetable on datetable.dateItem between mytable.startdate and myTable.enddate
) As Src
PIVOT
(
SUM(price)
FOR
dateitem IN ([2020-03-16],[2020-03-20],[2020-04-19])
) as P
а если столбцы динамические? я имею в виду, что пытался искать перекрестные таблицы в SQL, но не понял, как использовать его в текущем сценарии
Вам нужно взять этот запрос select item, dateitem, price from myTable inner join datetable on datetable.dateItem between mytable.startdate and myTable.enddate
, а затем выполнить поиск t-sql dynamic pivot
в гугле. Для кого вы производите эти данные? Потому что обычно вы бы использовали инструмент отчетности для сводной таблицы, а не SQL.
IF OBJECT_ID('tempdb..#myTable', 'U') IS NOT NULL
DROP TABLE #myTable;
IF OBJECT_ID('tempdb..#itemTable', 'U') IS NOT NULL
DROP TABLE #itemTable;
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL
DROP TABLE #DateTable;
CREATE TABLE #myTable (
item VARCHAR(MAX) NOT NULL,
startdate DATE NOT NULL,
enddate DATE NOT NULL,
price INT NOT NULL DEFAULT(0)
);
INSERT #myTable (item, startdate, enddate, price) VALUES
('AAAA' ,CAST('2020-03-16' AS DATE),CAST('2020-03-19' AS DATE),50),
('AAAA' ,'2020-04-16','2020-04-19',70),
('BBB' ,'2020-03-16','2020-03-19',20),
('BBB' ,'2020-04-16','2020-04-19',90),
('CCC' ,'2020-03-16','2020-03-29',45),
('CCC' ,'2020-04-16','2020-04-19',120)
CREATE TABLE #itemTable (
item VARCHAR(MAX) NOT NULL
)
INSERT #itemTable (item) VALUES
('AAAA'),
('BBB'),
('CCC')
CREATE TABLE #DateTable (
dateItem DATE NOT NULL
)
INSERT #DateTable (dateItem) VALUES
(CAST('2020-03-16' AS DATE)),
(CAST('2020-04-19' AS DATE)),
(CAST('2020-03-20' AS DATE)),
(CAST('2020-03-21' AS DATE)),
(CAST('2021-03-21' AS DATE)),
(CAST('2022-03-21' AS DATE))
Declare @DynamicCol nvarchar(max),@DynamicColNull nvarchar(max)
,@Sql nvarchar(max)
SELECT @DynamicColNull=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(dateItem),','+'''0'''+') As '+QUOTENAME(dateItem)
FROM #DateTable FOR XML PATH ('')),1,2,'')
SELECT @DynamicCol=STUFF((SELECT DISTINCT ', '+QUOTENAME(dateItem) FROM #DateTable FOR XML PATH ('')),1,2,'')
SET @Sql='SELECT [item], '+@DynamicColNull+' From
(
select item, dateitem, price from #myTable
inner join #datetable on #datetable.dateItem between #mytable.startdate and #myTable.enddate
)
AS Src
PIVOT
(
SUM(price) FOR [dateitem] IN ('+@DynamicCol+')
)AS Pvt'
PRINT @Sql
EXEC(@Sql)
Сначала вам нужно написать запрос, который правильно объединяет эти три таблицы. Затем вам нужно построить перекрестный запрос по этому