如果我理解正确的话，你想把所有的行都放进去 table1 哪里 table1.date 与中月份的最后一个工作日对应 table2.date .
我假设这些是真的 date 列，尽管格式不同。如果不是，就把它们转换成真正的约会。 table1.date 已经处于可接受的状态 YYYYMMDD 格式，以便mysql为您转换它。 table2.date 可以更改为可接受的 YYYYMMDD 格式 concat(date, "01") . 然后可以使用mysql的日期函数。我强烈建议你通过改变表格使这种转换永久化，这将使一切变得更简单和更快。
mysql没有最后一个工作日，但它确实有 last_day 以及 dayofweek （这里是我感叹mysql函数名约定不一致的地方）。为了得到最后一个工作日，我们找到了一个月的最后一天，然后是一周中的哪一天，然后根据需要减去。
令人烦恼的是， dayofweek 星期天返回1，而不是0，这会让事情变得更困难。

case
    -- Sunday, move to Friday
    when dayofweek(last_day('2010-02-01')) = 1 then last_day('2010-02-01') - 2
    -- Saturday, move to Friday
    when dayofweek(last_day('2010-02-01')) = 7 then last_day('2010-02-01') - 1
    -- Weekday
    when dayofweek(last_day('2010-02-01')) in(2,3,4,5,6) then last_day('2010-02-01') 
    else null
end as last_business_day;

这最好作为存储过程来完成。这不仅避免了重复，因为我们可以使用变量，它还将逻辑封装在一个整洁的、可测试的函数中。我们也可以申报 deterministic 这意味着mysql可以缓存相同日期的计算，从而节省一些cpu时间。

create function last_business_day(date date)
returns date
deterministic
begin
    declare last_day date;
    declare last_dow int;

    set last_day = last_day(date);
    set last_dow = dayofweek(last_day);
    return case
        -- Sunday, move to Friday
        when last_dow = 1 then
            last_day - 2
        -- Saturday, move to Friday
        when last_dow = 7 then
            last_day - 1
        -- Weekday
        when last_dow in(2,3,4,5,6) then
            last_day
        else
            null
    end;
end;

现在我们可以问了 last_business_day('2010-02-01') 然后得到 2010-02-26 .
有了它，查询就变得简单了。

select t1.product, t1.date, t1.state, t2.country
from table1 t1
join table2 t2 on t1.product = t2.product and
                  t1.date = last_business_day(t2.date)

以下是您的sql：

select x.product,x.date,x.state,x.country
from
(select x.product,x.date,x.state,country
from (select product,max(date) date from table1 group by product) t1
join table2 on (left(t1.date,6) = table2.date)
join table1 x on (left(x.date,6) = left(t1.date,6))) x
join (select x.product,max(x.date) date
from (select product,max(date) date from table1 group by product) t1
join table2 on (left(t1.date,6) = table2.date)
join table1 x on (left(x.date,6) = left(t1.date,6))
     group by x.product) y
on (x.product = y.product and x.date = y.date);

示例：

mysql> create table table1(product varchar(20),date varchar(8), state varchar(20));
Query OK, 0 rows affected (0.41 sec)

mysql> insert into table1 values
    -> ('A','20080107','NY'),
    -> ('A','20080131','TX'),
    -> ('B','20100212','CT'),
    -> ('B','20100226','MT'),
    -> ('C','20150312','HG'),
    -> ('C','20140425','UP');
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table table2(Product varchar(20),Date varchar(8),Country varchar(20));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into table2 values
    -> ('A','200801','USA'),
    -> ('C','201503','AUS'),
    -> ('B','201002','UK'),
    -> ('B','201704','FIN'),
    -> ('C','200605','IRE'),
    -> ('A','200805','CAN');
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select x.product,x.date,x.state,x.country
    -> from
    -> (select x.product,x.date,x.state,country
    -> from (select product,max(date) date from table1 group by product) t1
    -> join table2 on (left(t1.date,6) = table2.date)
    -> join table1 x on (left(x.date,6) = left(t1.date,6))) x
    -> join (select x.product,max(x.date) date
    -> from (select product,max(date) date from table1 group by product) t1
    -> join table2 on (left(t1.date,6) = table2.date)
    -> join table1 x on (left(x.date,6) = left(t1.date,6))
    ->      group by x.product) y
    -> on (x.product = y.product and x.date = y.date);
+---------+----------+-------+---------+
| product | date     | state | country |
+---------+----------+-------+---------+
| A       | 20080131 | TX    | USA     |
| B       | 20100226 | MT    | UK      |
| C       | 20150312 | HG    | AUS     |
+---------+----------+-------+---------+
3 rows in set (0.01 sec)

演示

http://www.sqlfiddle.com/#!2011年9月6日

根据op， date 存储在 INT . 查找每个月的最后日期（ FLOOR(date/100) )对于表1中的每种产品，使用它与表2合并。

select 
t1.product,
t1.date,
t1.state,
t2.country
from 
table2 t2
join
(select table1.* 
 from table1 join
 (select product, max(date) lastdayOfMonth 
  from table1
  group by product, floor(date/100)) t 
  on table1.product = t.product and table1.date = t.lastdayofMonth ) t1 
 on 
 t1.product = t2.product
 and
 t1.date = t2.date

演示：sqlfiddle