如何映射2表条件的第一天 [英] How to mapping 2 table condition the first day
本文介绍了如何映射2表条件的第一天的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个带列的表,看起来像这样:
表A
key_a,date_a
'k1','2015-11-25'
'k2','2015-11-10'
'k3','2015-10 -09'
表B
id,key_b,date_b
1,'k1','2015-10-01'
2,'k1','2015-11-02'
3,'k1','2015-11-25'
4,'k2','2015-10-15'
5,'k2','2015-11-09'
6,'k3','2015-09-15'
7,'k3','2015-10-02'
8,'k3','2015-10-08'
我想查找表B中的行 date_b
首先与 key_b
:
key_a,date_a,id, date_b
'k1','2015-11-25',1,2015-10-01
'k2','2015-11-10',5,2015-11-09
'k3','2015-10-09',6,2015-09-15
我可以这样做吗?
quick one - should work
SELECT
key_a,date_a,b.id AS id,b.date_b AS date_b
FROM table_a AS a
JOIN(
SELECT
id,key_b,date_b,
ROW_NUMBER( )OVER(通过key_b ORDER BY分区date_b)AS num
FROM table_b
)AS b
ON a.key_a = b.key_b
WHERE b.num = 1
I have 2 table with columns, looking like this:
Table A
key_a, date_a
'k1', '2015-11-25'
'k2', '2015-11-10'
'k3', '2015-10-09'
Table B
id, key_b, date_b
1, 'k1', '2015-10-01'
2, 'k1', '2015-11-02'
3, 'k1', '2015-11-25'
4, 'k2', '2015-10-15'
5, 'k2', '2015-11-09'
6, 'k3', '2015-09-15'
7, 'k3', '2015-10-02'
8, 'k3', '2015-10-08'
I want to find row in table B have date_b
is first with same key_b
:
key_a, date_a, id, date_b
'k1', '2015-11-25', 1, 2015-10-01
'k2', '2015-11-10', 5, 2015-11-09
'k3', '2015-10-09', 6, 2015-09-15
How can I do this?
解决方案
quick one - should work
SELECT
key_a, date_a, b.id AS id, b.date_b AS date_b
FROM table_a AS a
JOIN (
SELECT
id, key_b, date_b,
ROW_NUMBER() OVER(PARTITION BY key_b ORDER BY date_b) AS num
FROM table_b
) AS b
ON a.key_a = b.key_b
WHERE b.num = 1
这篇关于如何映射2表条件的第一天的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文