Mysql查询，用于在两个日期之间返回数据，并带有联接

问：

我正在努力进行mysql查询，以在涉及连接子查询的两个日期之间返回数据。

场景：我有一个product_revenues表和一个product_costs表。我需要根据过去 12 个月的最新成本开始日期，将每个收入金额与成本金额进行匹配来计算利润。因此，这是 9 月份的收入和 3 月份的最新成本应该在下面的数据中匹配。我的数据如下

产品收入表

注意：“created”是时间戳 = 2023-09-13

产品成本表

我的查询

SELECT 
    MONTH(FROM_UNIXTIME(product_revenues.created)) AS revenueMonth,
    SUM(product_revenues.amount) AS totalRevenue,
    SUM(product_costs.amount) AS totalCost,
    SUM(product_revenues.amount - product_costs.amount) AS totalProfit
FROM
    sample.product_revenues
        JOIN
    sample.product_costs ON product_costs.start_date <= FROM_UNIXTIME(product_revenues.created)
        AND product_costs.start_date = (SELECT 
            Max(start_date)
        FROM
            product_costs
        WHERE
            start_date <= FROM_UNIXTIME(product_revenues.created))
WHERE
    product_revenues.team_id = 1
        AND product_costs.team_id = 1
        AND product_revenues.stripe_product_id = 'prod_1234'
        AND product_costs.stripe_product_id = 'prod_1234'
        AND FROM_UNIXTIME(product_revenues.created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
GROUP BY revenueMonth
ORDER BY revenueMonth ASC

由此，我希望结果表显示：

当我将成本start_date更改为 2023-04-13 及以后时，查询会正确返回结果，但 2023-03-13 及更早版本不会返回任何结果。 就好像 2023-03-13 及更早的时间超出了范围，但介于两者之间是 12 个月。

是mysql缺陷吗？我的查询哪里不正确？

下面是用于创建示例表和示例数据以进行说明的脚本。

-- Create the "sample" schema if it doesn't exist 
CREATE SCHEMA IF NOT EXISTS sample;

-- Create the "teams" table
CREATE TABLE sample.teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(255) NOT NULL
);

-- Create the "product_revenues" table
CREATE TABLE sample.product_revenues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    stripe_product_id VARCHAR(255) NOT NULL,
    created int NOT NULL,
    FOREIGN KEY (team_id) REFERENCES sample.teams(id)
);

-- Create the "product_costs" table
CREATE TABLE sample.product_costs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    stripe_product_id VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    FOREIGN KEY (team_id) REFERENCES sample.teams(id)
);

-- Insert sample data into the "teams" table
INSERT INTO sample.teams (team_name) VALUES
    ('Team A');

-- Insert sample data into the "product_revenues" table
INSERT INTO sample.product_revenues (team_id, amount, stripe_product_id, created) VALUES
    (1, 1000.00, 'prod_1234', '1694563200');

-- Insert sample data into the "product_costs" table
INSERT INTO sample.product_costs (team_id, amount, stripe_product_id, start_date) VALUES
    (1, 400.00, 'prod_1234', '2023-01-13'),
    (1, 500.00, 'prod_1234', '2023-03-13');