我有 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
发布评论