将start_date和_end_date连接到另一个表并进行汇总 [英] join start_date and _end_date to another table and sum up

问题描述

表1的表列为:

cancel_date    product  total_cancels
6/1/2017       a        100
6/1/2017       b        40
6/2/2017       b        10
6/3/2017       b        20
.
.
.
6/1/2018       a        40
6/1/2018       b        10

表2

realdate  cancel_start_date    cancel_end_date
6/1/2017    6/1/2016           4/1/2017
6/2/2017    6/2/2016           4/2/2017
6/3/2017    6/3/2016           4/3/2017
.
.
.

---

因此table1.cancel_date在t2.cancel_start_date和t2.cancel_end_date之间， 我怎样才能将这两个表连接起来?

---

so table1.cancel_date between t2.cancel_start_date and t2.cancel_end_date, how can I join these two tables?

我想得到什么

product    realdate      total_cancels   cancel_date between start_date and end_date
a          6/1/2017       100000         6/1/2016-4/30/2017 
b          6/1/2017       8000           6/1/2016-4/30/2017
a          6/2/2017       100000         6/2/2016-5/1/2017
b          6/2/2017       8000           6/2/2016-5/1/2017
...

推荐答案

您要做的是使用table_1这样的on条件将table_2连接到table_1. cancel_date在table_2.cancel_start_date和table_2.cancel_end_date之间.但是首先我们需要使用DATE_PARSE函数来使日期具有可比性.最后，只需将这些值相加即可.

What you want to do is join table_2 to the table_1, using the on condition such that table_1. cancel_date is between table_2.cancel_start_date and table_2.cancel_end_date. But first we need to use the DATE_PARSE function to make the dates comparable. Finally just sum up the values.

SELECT
  table_1.product,
  table_2.realdate,     
  SUM(total_cancels) AS total_cancels,   
  CONCAT(table_2.cancel_start_date, '-', table_2.cancel_end_date) as start_to_end
FROM table_1 
JOIN table_2
WHERE DATE_PARSE(table_1. cancel_date, '%m/%d/%Y') 
  BETWEEN DATE_PARSE(table_2.cancel_start_date, '%m/%d/%Y') 
   AND DATE_PARSE(table_2.cancel_end_date, '%m/%d/%Y') 
GROUP BY 1, 2, 4 

这篇关于将start_date和_end_date连接到另一个表并进行汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！