mysql - 子查询或连接以匹配值范围内 2 个字段上的 2 个表

Question

对于每个唯一的 pur_dates，product_sold 字段中的项目数等于 product 字段中的项目数 WHERE chk_date 在 pur_date 的 31 天内除以那 31 天内的 product_sold 总数。

2 个大条件是 product_sold 中的项目等于 product 中的项目并且要使匹配有效，table2 中的日期范围必须在一个月内表 1。

product 字段是唯一的，而 product_sold 字段可以有重复的产品。 pur_date 字段仅在年份和月份上有所不同。

Table1
    pur_date      product
    2015-07-01    shirt
    2015-06-01    shoe
    2015-04-01    purse
    2015-04-01    bag
    2014-05-01    key
    2015-05-01    gloves

Table2
chk_date     cost  product_sold
2015-07-29     9    bag
2015-07-15    10    shoe
2015-06-30     8    shirt
2014-06-25     6    bag
2015-06-01     9    shirt
2015-05-28     8    shoe
2015-05-15     4    key
2015-04-28     5    shirt
2015-03-15     6    purse
2015-03-15     4    ring
2015-03-10     4    key
2015-03-01     2    bag

我有一个非工作子查询来做这样的事情:

        Select pur_date,
        (Select SUM(CASE WHEN product_sold IN (select product from table1) THEN 1 ELSE 0 END)/COUNT(product_sold) 
        from table2
        where (pur_date - chk_date) <=31) AS percent_sold 
        from (select distinct pur_date from table1) t;

我得到的错误是:CASE 的 WHEN 子句中的非法表达式

例子:

output_table
pur_date   num_product_match
2015-07-01    2/3       <--for the 3 product_sold in June,2 items(shirt) match product = shirt
2015-06-01    1/2       <-- for the 2 product_sold in May,1 item(shoe) match product = shoe  
2015-05-01    1/1       <-- for th 1 product_sold in April, 1 item(shirt) match product = shirt
2015-04-01    2/4       <-- for the 4 product sold in March, 2 item(purse, bag) match product = purse and product = bag

Answer 1

SELECT pd.pur_date, 
    SUM(CASE WHEN t1.product IS NOT NULL THEN 1 ELSE 0 END) / 
    CAST(COUNT(t2.product_sold) as float)  As num_product_match    
FROM (SELECT DISTINCT pur_date FROM Table_1) pd
INNER JOIN Table_2 t2 ON t2.chk_date < pd.pur_date 
    AND t2.chk_date >= DATE_ADD(pd.pur_date, INTERVAL -1 MONTH)
LEFT JOIN Table_1 t1 ON t1.pur_date > t2.chk_date 
    AND t1.pur_date <= DATE_ADD(t2.chk_date, INTERVAL 1 MONTH) 
    AND t1.product = t2.product_sold
GROUP BY pd.pur_date