iT邦幫忙

0

MS SQL 新增、查詢

各位大大好,小弟的問題如下
如有敘述不清或問題模糊再麻煩提醒了,謝謝!

首先有一張表
tableA

項目 廠商 購入日期
牙刷 A 4/26
牙刷 A 4/27
牙刷 B 4/26
水壺 A 4/30
水壺 B 5/15

以4/26為第一天,往後推算4天 4/26 - 4/30
期望產生以下結果
(如果項目內有4/26-4/30區間的日期,則補上缺少的日期,但不重複日期
同項目、不同廠商也要補上日期,若在日期範圍外則不做處理)

項目 廠商 購入日期
牙刷 A 4/26
牙刷 A 4/27
牙刷 A 4/28
牙刷 A 4/29
牙刷 A 4/30
牙刷 B 4/26
牙刷 B 4/27
牙刷 B 4/28
牙刷 B 4/29
牙刷 B 4/30
水壺 A 4/26
水壺 A 4/27
水壺 A 4/28
水壺 A 4/29
水壺 A 4/30
水壺 B 5/15

我的作法是先建立一張只有日期的資料表,然後再做join
條件小於等於tableDate的日期,將tableA項目、廠商+tableDate一起撈出來

tableDate

日期
4/26
4/27
4/28
4/29
4/30
select A.項目,A.廠商,D.日期
from tableA A
right join tableDate D
on A.購入日期 <= D.日期

不過結果當然跟預期不一樣 囧
我想是join條件下的不對
但想了一整天還是不知道該怎麼串出結果Orz
還請高人指點 (跪

2
石頭
iT邦研究生 2 級 ‧ 2019-04-26 23:24:06
最佳解答

你可以用CTE遞迴做一個日曆表,另一個CTE利用聚集條件函數查找條件區間的項目,廠商 並使用CROSS JOIN 對於剛剛製作的日曆表.

最後在UNION ALL尚未在區間的列

DECLARE @startDt DATE = '2019/04/26',
        @endDt DATE = '2019/04/30'
        
WITH CTE AS (
   SELECT @startDt startDt,@endDt endDt
   UNION ALL
   SELECT  DATEADD(DAY,1,startDt),endDt
   FROM CTE 
   WHERE DATEADD(DAY,1,startDt)<=endDt
),CTE2 AS(
   SELECT 項目,廠商,startDt
   FROM (
	   SELECT [項目],
			  [廠商],
			  COUNT(CASE WHEN [購入日期] BETWEEN @startDt and @endDt THEN 1 END) cnt
		FROM tableA
		GROUP BY [項目],[廠商]
		HAVING COUNT(CASE WHEN [購入日期] BETWEEN @startDt and @endDt THEN 1 END) > 0
   ) t1 CROSS JOIN CTE 
)
SELECT 項目,廠商,[購入日期]
FROM tableA
WHERE [購入日期] < @startDt OR [購入日期] > @endDt
UNION ALL
SELECT 項目,廠商,startDt
FROM CTE2
ORDER BY 項目,廠商,[購入日期]

Result

項目	廠商	購入日期
水壺	A	26/04/2019 00:00:00
水壺	A	27/04/2019 00:00:00
水壺	A	28/04/2019 00:00:00
水壺	A	29/04/2019 00:00:00
水壺	A	30/04/2019 00:00:00
水壺	B	15/05/2019 00:00:00
牙刷	A	26/04/2019 00:00:00
牙刷	A	27/04/2019 00:00:00
牙刷	A	28/04/2019 00:00:00
牙刷	A	29/04/2019 00:00:00
牙刷	A	30/04/2019 00:00:00
牙刷	B	26/04/2019 00:00:00
牙刷	B	27/04/2019 00:00:00
牙刷	B	28/04/2019 00:00:00
牙刷	B	29/04/2019 00:00:00
牙刷	B	30/04/2019 00:00:00

sqlfiddle

暐翰 iT邦大師 1 級 ‧ 2019-04-27 14:34:30 檢舉

精簡
話說D大已經在S.O Taiwan排名第三了 太強了 /images/emoticon/emoticon12.gif

有神快拜
/images/emoticon/emoticon41.gif

jason241 iT邦新手 5 級 ‧ 2019-04-29 23:13:53 檢舉

感謝D大解答,問題已順利解決!
第一次認識CTE!/images/emoticon/emoticon41.gif

0
小魚
iT邦大師 1 級 ‧ 2019-04-26 18:04:30

我比較想問的問題是,
你為什麼要這樣做?

jason241 iT邦新手 5 級 ‧ 2019-04-26 18:13:42 檢舉

其實是為了要重新分配數量到指定日期區間的每一天,所以才想要這樣做的,但這裡我沒有加上數量,是想先弄清楚怎麼將原有資料搭配指定日期區間做呈現

小魚 iT邦大師 1 級 ‧ 2019-04-26 18:26:14 檢舉

不大懂,
不過我覺得,
也許你可以在程式處理這件事,
而不是在SQL.

jason241 iT邦新手 5 級 ‧ 2019-04-26 18:36:20 檢舉

好的,我會再嘗試
/images/emoticon/emoticon41.gif

0
paicheng0111
iT邦高手 1 級 ‧ 2019-04-26 22:10:56

這好像是笛卡爾乘積

試試看

SELECT DISTINCT a.item, b.producer, c.order_date
FROM 
    (SELECT item FROM t1 ORDER BY item) AS a
    , (SELECT producer FROM t1 ORDER BY producer) AS b
    , (SELECT order_date FROM t1 ORDER BY order_date) AS c
ORDER BY 1,2,3
jason241 iT邦新手 5 級 ‧ 2019-04-29 23:02:43 檢舉

沒錯!好像跟笛卡爾乘積有關,不過直接展開的話
好像會多出很多重複的結果 囧"
謝謝你的協助!/images/emoticon/emoticon41.gif

我要發表回答

立即登入回答