在日期范围内加入 mysql

本文介绍了在日期范围内加入 mysql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有 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;