在SQL中根据从另一个表的选择从一个表中选择数据

Question

我有这三个表

首先包含给定日期的价格项

第二个是项目表

第3个是日期表，我们希望在其中显示第2个表中商品在每个日期的价格

如果第一个表中没有duration，那么它应该是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

我不确定要搜索什么:)因为我想为它写查询，它返回我想要的表作为数据(或作为临时表)

Answer 1

这是许多方法之一。这是一个静态交叉表。您需要显式列出所有列(两次)

如果列是动态的，则需要使用动态交叉表。你也应该考虑在你的"演讲"中这样做。图层，比如excel或者其他你要交上去的东西

您应该考虑当mytable中的某些内容对bucket出现两次时您想要什么(此解决方案将添加价格)

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

Answer 2

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)