各位大大好，小弟的問題如下
如有敘述不清或問題模糊再麻煩提醒了，謝謝!
首先有一張表
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
還請高人指點 (跪
已邀請的邦友 {{ invite_list.length }}/5
你可以用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
我比較想問的問題是,
你為什麼要這樣做?
這好像是笛卡爾乘積
試試看
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
iThome鐵人賽
看影片追技術