我有這 3 張桌子
首先包含給定日期價格的專案
第二個是專案表
第三個是我們要在每個日期顯示第二個表格專案的價格的日期表
如果第一張桌子上沒有持續時間，它應該是 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
我不確定要搜索什么:) 因為我想為它撰寫查詢，它將我的愿望表作為資料回傳（或在臨時表中）
uj5u.com熱心網友回復：
執行此操作的眾多方法之一。這是一個靜態交叉表。您需要明確列出所有列（兩次）
如果您的列是動態的，則需要使用動態交叉表。您還應該考慮在您的“演示”層中執行此操作，即 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
uj5u.com熱心網友回復：
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/476859.html
上一篇：SQL錯誤訊息：SELECTINTO陳述句不能包含將值分配給變數的SELECT陳述句
下一篇：SQL查詢排除某天某用戶