Выбор данных из одной таблицы на основе выбора из другой таблицы в SQL

У меня есть эти 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

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

Сначала вам нужно написать запрос, который правильно объединяет эти три таблицы. Затем вам нужно построить перекрестный запрос по этому

Nick.McDermaid 16.05.2022 12:46

во 2-й и 3-й таблице нет ключа, поэтому не знаю, как присоединиться

Ahtesham ul haq 16.05.2022 13:08

Да itemTable избыточен. Но для того, чтобы «распределить» ваши даты так, как вам нужно, вам нужно что-то вроде ON DateTable.dateItem BETWEEN myTable.startdate AND myTable.enddate. Затем вы можете сопоставить несколько записей в MyTable с DateTable.dateitem. Вы получите дополнительный столбец DateTable.dateitem, который вам нужно повернуть

Nick.McDermaid 16.05.2022 13:11

можешь пожалуйста перевести это на английский :)

Ahtesham ul haq 16.05.2022 13:31

: D Я, вероятно, могу опубликовать ответ, но во-первых, являются ли даты в DateTable динамическими или фиксированными? там только три? Потому что вам, вероятно, нужна динамическая кросс-таблица, и я обычно просто говорю «сделайте это на уровне представления».

Nick.McDermaid 16.05.2022 14:27

Глядя на свои данные, можете ли вы перепроверить последнюю строку CCC? Я думаю, что это должно быть 4, 120, 45

Nick.McDermaid 16.05.2022 14:37

@Nick.McDermaid, да, 45 120,45

Ahtesham ul haq 16.05.2022 14:43
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
7
45
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Один из многих способов сделать это. Это перекрестная таблица статический. Вам нужно явно указать все столбцы (дважды)

Если ваши столбцы являются динамическими, вам необходимо использовать кросс-таблицу динамичный. Вам также следует подумать о том, чтобы сделать это в своем слое «презентация», то есть в 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, но не понял, как использовать его в текущем сценарии

Ahtesham ul haq 17.05.2022 05:07

Вам нужно взять этот запрос select item, dateitem, price from myTable inner join datetable on datetable.dateItem between mytable.startdate and myTable.enddate, а затем выполнить поиск t-sql dynamic pivot в гугле. Для кого вы производите эти данные? Потому что обычно вы бы использовали инструмент отчетности для сводной таблицы, а не SQL.

Nick.McDermaid 17.05.2022 06:29
Ответ принят как подходящий
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)

Другие вопросы по теме