映射2表与每行之间的日期 [英] Mapping 2 table with between date for each row
本文介绍了映射2表与每行之间的日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个带列的表,看起来像这样:
表A
rows,key_a,date_a
1,'k1','2015-11-12'
2,'k2','2015-11-20'
3, 'k3','2015-12-01'
表B
row,key_b,date_b,Code
1,'k1','2015-10-12',C1
2,'k1 ','2015-09-12',C2
3,'k1','2015-11-01',C3
4,'k1','2015-10-20',C4
5,'k1','2015-08-19',C5
6,'k1','2015-11-02',C6
7,'k2','2015- 10-12',C7
8,'k2','2015-09-12',C8
9,'k2','2015-11-01',C9
10, 'k2','2015-10-20',C10
11,'k2','2015-08-19',C11
12,'k2','2015-11-02', C12
13,'k3','2015-10-12',C13
14,'k3','2015-09-12',C14
15,'k3',' 2015-11-01',C15
16,'k3','2015-10-20 ',C16
17,'k3','2015-08-19',C17
18,'k3','2015-11-02',C18
我想查找表B中的行 date_b
首先具有相同的 key_b
,并且只能从 date_a
到之间的
date_b
date_a - 30
这意味着:
'k1'=> '2015-11-12'至'2015-10-12'=>行视图(1,3,4,6)
'k2'=> '2015-11-20'至'2015-10-20'=>行视图(9,10,12)
'k3'=> '2015-12-01'至'2015-11-01'=>行视图(15,18)
结果
key_a,date_a,Code
'k1','2015-11-12',C1
'k2','2015-11-20',C10
'k3','2015-12-01',C15
我该怎么办这个?
解决方案
SELECT key_a,date_a,code
FROM(
SELECT *,ROW_NUMBER()OVER(PARTITION BY key_a ORDER BY date_b)AS num
FROM(
SELECT key_a,date_a,date_b,code,
FROM table_a as a
JOIN table_b AS b
ON a.key_a = b.key_b
WHERE date_b BETWEEN DATE(DATE_ADD(TIMESTAMP(date_a),-1,MONTH))和date_a
)
)
WHERE num = 1
ORDER BY key_a
结果: p>
key_a date_a code
k1 2015-11-12 C1
k2 2015-11-20 C10
k3 2015-12-01 C15
I have 2 table with columns, looking like this:
Table A
rows, key_a, date_a
1, 'k1', '2015-11-12'
2, 'k2', '2015-11-20'
3, 'k3', '2015-12-01'
Table B
row, key_b, date_b, Code
1, 'k1', '2015-10-12', C1
2, 'k1', '2015-09-12', C2
3, 'k1', '2015-11-01', C3
4, 'k1', '2015-10-20', C4
5, 'k1', '2015-08-19', C5
6, 'k1', '2015-11-02', C6
7, 'k2', '2015-10-12', C7
8, 'k2', '2015-09-12', C8
9, 'k2', '2015-11-01', C9
10, 'k2', '2015-10-20', C10
11, 'k2', '2015-08-19', C11
12, 'k2', '2015-11-02', C12
13, 'k3', '2015-10-12', C13
14, 'k3', '2015-09-12', C14
15, 'k3', '2015-11-01', C15
16, 'k3', '2015-10-20', C16
17, 'k3', '2015-08-19', C17
18, 'k3', '2015-11-02', C18
I want to find row in table B have date_b
is first with same key_b
and only select data in table B with date_b
between from date_a
to date_a - 30
It mean:
'k1' => '2015-11-12' to '2015-10-12' => rows view (1,3,4,6)
'k2' => '2015-11-20' to '2015-10-20' => rows view (9,10,12)
'k3' => '2015-12-01' to '2015-11-01' => rows view (15,18)
Result
key_a, date_a, Code
'k1', '2015-11-12', C1
'k2', '2015-11-20', C10
'k3', '2015-12-01', C15
How can I do this?
解决方案
SELECT key_a, date_a, code
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY key_a ORDER BY date_b) AS num
FROM (
SELECT key_a, date_a, date_b, code,
FROM table_a AS a
JOIN table_b AS b
ON a.key_a = b.key_b
WHERE date_b BETWEEN DATE(DATE_ADD(TIMESTAMP(date_a), -1, "MONTH")) AND date_a
)
)
WHERE num = 1
ORDER BY key_a
Result:
key_a date_a code
k1 2015-11-12 C1
k2 2015-11-20 C10
k3 2015-12-01 C15
这篇关于映射2表与每行之间的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文