作者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/cn.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