在日期范围内加入 mysql [英] join mysql on a date range

问题描述

我有 2 个表，希望在某个日期范围内加入.表 1 的日期仅随年和月变化，而表 2 的日期在多天变化.

I have 2 tables that I wish to join on a date range. Table 1's date only vary on year and month while table 2 vary on multiple days.

Table 1
pur_date      product
2015-06-01    shirt
2015-06-01    shoe
2015-05-01    shoe
2015-04-01    purse
2015-04-01    bag
2014-05-01    key

Table 2
chk_date     cost  
2015-07-15    10
2015-06-30     8
2015-04-30     5
2015-07-29     9
2014-06-25     6
2015-05-30     9
2015-05-15     4 
2015-05-28     8

我想在我想要的日期加入(计算 >8 的成本数)/(总成本数)*100 其中(chk_date - pur_date)是 <= 62(即 <= 2 个月pur_date)

I want to join on date such that I want (count number of costs that are >8) / (total number of costs)*100 where (chk_date - pur_date) is <= 62 (i.e. <= 2 months of pur_date)

所以对于我的决赛桌，我应该得到这个唯一 pur 日期的输出和(>8 的成本计数)/(成本总数)*100 百分比:

So for my final table, I should get this output of unique pur dates and the (count number of costs that are >8) / (total number of costs)*100 As percent :

table 3
    pur_date     percent
    2015-06-01      2/4*100
    2015-05-01      1/5*100

我该如何为此编写连接子句?

How can I write the join clause for this?

推荐答案

使用相关子查询:

select pur_date,
       (select avg(cost > 8)
        from table2
        where chk_date <= pur_date - interval 2 month
       ) as percent
from (select distinct pur_date from table1) t;

这篇关于在日期范围内加入 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！