我想从第二个表中提取日期。我的第二个表可能有 100 多个日期到唯一记录(A,B,C(。我有我的第一个表:
Name Count Data1
A 1 20190201
B 3 20190201
C 6 20190201
第二个表:
Name Date
A 20190101
B 20190203
A 20190102
B 20190103
C 20190305
C 20190809
A 20190304
B 20190405
当日期大于第一个表中的date1时,我想从第二个表中提取日期第一个日期。
结果必须是:
Name Count Data1 Date2
A 1 20190201 20190304
B 3 20190201 20190203
C 6 20190201 20190305
如何提取日期。它必须是一个过程或其他解决方案。 感谢您的帮助:)
使用 join 和 min((
select t1.name,t1.data1,t1.count,min(t2.date) as date2
from table1 t1 join table2 t2
on t1.name=t2.name
and t1.date<t2.date
group by t1.name,t1.date,t1.count
您希望第二个表中的最早日期也大于第一个表中的日期:
有多种解决方案,包括相关的子查询:
SELECT t1.*,
( SELECT MIN( "DATE" )
FROM Table2 t2
WHERE t1.name = t2.name
AND t1.data1 < t2."DATE" ) AS data2
FROM table1 t1
或
SELECT name,
"COUNT",
data1,
data2
FROM (
SELECT t1.*,
t2."DATE" AS data2,
ROW_NUMBER() OVER ( PARTITION BY t1.name ORDER BY t2."DATE" ) AS rn
FROM table1 t1
INNER JOIN table2 t2
ON ( t1.name = t2.name AND t1.data1 < t2."DATE" )
)
WHERE rn = 1
您可以简单地使用聚合:
select t1.name, t1.count, t1.data1, min(t2.date) date2
from table1 t1
inner join table2 t2 on t1.name = t2.name and t2.date > t1.data1
group by t1.name, t1.count, t1.data1
您需要分析功能:
SELECT NAME, COUNT_, DATA1, DATE2 FROM
(SELECT T1.NAME, T1.COUNT_, T1.DATA1,
ROW_NUMBER()
OVER (PARTITION BY T1.NAME ORDER BY T2.DATE) AS RN,
T2.DATE AS DATE2
FROM TABLE1 T1 JOIN TABLE2 T2
ON (T1.NAME = T2.NAME))
WHERE T2.DATE > T1.DATA1
WHERE RN = 1
干杯!!