在成本数据范围内有数据标记的产品行的mysql和

Question

我有三张桌子product，cost和account

CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `datestamp` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `cost` (
  `account_id` int(11) NOT NULL,
  `amount` float NOT NULL,
  `datestamp` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `account` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;

+-----+----------+-----------------+
| id  | name     | email           |
+-----+----------+-----------------+
| 101 | Acme Inc | jondoe@host.com |
+-----+----------+-----------------+

+------------+--------+------------+
| account_id | amount | datestamp  |
+------------+--------+------------+
|        101 |      5 | 2014-01-01 |
|        101 |     10 | 2014-01-03 |
|        101 |     15 | 2014-01-06 |
+------------+--------+------------+

+----+------------+---------+------------+
| id | account_id | name    | datestamp  |
+----+------------+---------+------------+
|  1 |        101 | Alfa    | 2014-01-01 |
|  2 |        101 | Bravo   | 2014-01-02 |
|  3 |        101 | Charlie | 2014-01-03 |
|  4 |        101 | Delta   | 2014-01-04 |
|  5 |        101 | Echo    | 2014-01-06 |
|  6 |        101 | Foxtrot | 2014-01-08 |
+----+------------+---------+------------+

我想返回每个帐户的单位成本和总数量，方法是根据日期邮票将产品行与其成本相匹配，并将它们按成本和帐户分组，这样我就可以将它们加在一起。

这是我在查询中所得到的，但我仍在努力使产品与正确的价格相匹配。

select
    a.id as account_id,
    count(p.id) as qty,
    c.amount as cost_per_unit,
    c.datestamp as cost_date,
    p.datestamp as product_date
from product p
left join account a on a.id = p.account_id
left join cost c on 1
where p.datestamp <is within right range based on cost date stamps>
group by c.amount, a.id
;

因此，如果2014-01-01年度为5美元，2014-01-3年度为10美元，2014-01-06年度为15美元，则在10美元日期之前盖印的每个产品日期为5美元，15美元之前为10美元，以此类推。

关于如何处理这个问题，有什么建议吗？

-根据下面的答案编辑我能得到我需要的东西

SELECT 
    a.id AS account_id,
    COUNT(p.id) AS qty,
    c1.amount AS cost_per_unit,
    c1.datestamp AS cost_date
FROM cost c1
    INNER JOIN account a ON a.id = c1.account_id
    LEFT JOIN cost c2 ON c2.datestamp = 
    (
        select datestamp 
        from cost 
        where account_id = a.id 
        and datestamp > c1.datestamp 
        order by datestamp ASC 
        LIMIT 1
    )
    LEFT JOIN product p ON p.datestamp >= c1.datestamp
WHERE p.datestamp < c2.datestamp OR c2.datestamp IS NULL
GROUP BY a.id, c1.amount
ORDER BY c1.datestamp ASC
;

Answer 1

在生产环境中，这可能是极其低效的，但是为了您的轻松享受，这个查询应该可以让您获得大部分的方法。我知道你能擦亮边缘，因为你目前做的很好:)

SELECT a.id AS account_id,
    COUNT(p.id) AS qty,
    c2.amount AS previous_cost_per_unit,
    c1.amount AS cost_per_unit,
    c1.datestamp AS cost_date,
    p.datestamp AS product_date
FROM product p
    INNER JOIN account a ON a.id = p.account_id
    INNER JOIN cost c1 ON c1.account_id = p.account_id AND c1.datestamp = p.datestamp
    LEFT JOIN cost c2 ON c2.datestamp = (SELECT MAX(datestamp) FROM cost WHERE account_id = c1.account_id AND datestamp < c1.datestamp)
GROUP BY account_id, product_date