作者cutekid (可愛小孩子)
看板Database
標題Re: [SQL ] 挑選時間區間內 重複的日期和種類
時間Fri May 6 14:54:24 2016
以下為
(Sales, 同一天最多重複時有幾種product)的解法:
create table #t (
Sales char(10),
Product char(1),
StartDate char(8),
EndDate char(8)
)
-- 建立測資
insert into #t select 'Amy', 'A', '20000101', '20000105'
insert into #t select 'Amy', 'B', '20000101', '20000103'
insert into #t select 'Amy', 'C', '20000104', '20000108'
insert into #t select 'Amy', 'D', '20000103', '20000104'
insert into #t select 'Amy', 'A', '20000110', '20000111'
insert into #t select 'Jack','A', '20000201', '20000202'
insert into #t select 'Jack','B', '20000203', '20000204'
insert into #t select 'Jack','C', '20000208', '20000209'
insert into #t select 'Jack','D', '20000204', '20000207'
insert into #t select 'Jack','B', '20000204', '20000204'
-- 解法
select Sales,max(cnt) cnt
from (
select t2.Sales,count(distinct t1.Product) cnt
from #t t1 inner join #t t2
on t1.Sales = t2.Sales
and
t2.StartDate >= t1.StartDate
and
t2.StartDate <= t1.EndDate
group by t2.Sales,t2.StartDate,t2.EndDate
) t1
group by Sales
說明:
綠色和
黃色兩個條件
(交集)可創出 7 種 case 如下:
A1------
B1--------C1 case 1
A2------
B2---C2---D2 case 2
A3---B3---C3 case 3
A4--------B4---C4 case 4
A5--------B5 case 5
A6------
B6--------C6----D6 case 6
A7------
B7--------C7 case 7
↑
註: 可看出不管哪種 case 它們至少有一天的交集(綠色箭頭指向的部份)
所以就可以把這些 group by 起來做不同種 product 的計算
各 case 中 An,Bn,Cn,Dn 所代表的意義:
A1 = t1.StartDate B1 = t1.EndDate,t2.StartDate C1 = t2.EndDate
A2 = t1.StartDate B2 = t2.StartDate C2 = t1.EndDate D2 = t2.EndDate
A3 = t1.StartDate,t2.StartDate B3 = t1.EndDate C3 = t2.EndDate
A4 = t1.StartDate,t2.StartDate B4 = t2.EndDate C4 = t1.EndDate
A5 = t1.StartDate,t2,StartDate B5 = t1.EndDate,t2,EndDate
A6 = t1.StartDate B6 = t2.StartDate C6 = t2.EndDate D6 = t1.EndDate
A7 = t1.StartDate B7 = t2.StartDate C7 = t1.EndDate,t2,EndDate
※ 引述《Mutex (Mutex)》之銘言：
: 資料庫名稱：MSSQL
: 資料庫版本：2008
: 內容/問題描述：挑選時間區間內 重複的日期和最多種類
: 也可以計算出每一天的Product種類有幾種，再取MAX
: 資料：
: Sales, Product, StartDate, EndDate
: Amy, A, 20000101, 20000105
: Amy, B, 20000101, 20000103
: Amy, C, 20000104, 20000108
: Amy, D, 20000103, 20000104
: Amy, A, 20000110, 20000111
: Jack, A, 20000201, 20000202
: Jack, B, 20000203, 20000204
: Jack, C, 20000208, 20000209
: Jack, D, 20000204, 20000207
: Jack, B, 20000204, 20000204
: 希望可以彙整出來的答案為(Sales, 最多重複時有幾種product,最多重複的那一天)
: Amy, 3, 20000104
: Jack, 2, 20000204
: 或是(Sales, 同一天最多重複時有幾種product)
: Amy, 3
: Jack, 2
: 忽然想到這個情境，但是卻思索不出個好解法
: 只好來向請高手請益了
: 謝謝
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.221.80.36
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1462517666.A.669.html
※ 編輯: cutekid (61.221.80.36), 05/06/2016 15:05:08
1F：推 Mutex: 再次感謝大大 獲益良多 05/06 19:52