mysql 查询设计,库存流水成本

JeffreyLin 发布于 2014/12/16 18:14
阅读 576
收藏 0
请大家帮忙设计一个 sql 语句,
数据库表设计如下,需求是
查询每一笔流水对应的成本价,谢谢!


/* 建表语句: */
CREATE TABLE cost_record
(
record_id int NOT NULL AUTO_INCREMENT,
product_id int,
start_date date,
cost_price numeric(8,2),
PRIMARY KEY (record_id)
);

CREATE TABLE stock_io
(
io_id int NOT NULL AUTO_INCREMENT,
product_id int,
io_date date,
PRIMARY KEY (io_id)
);

/* 测试数据 */
insert into cost_record(product_id, start_date, cost_price) values
(1, '2014-01-01', 5.00),
(1, '2014-02-04', 4.80),
(1, '2014-03-08', 4.90),
(2, '2014-01-05', 9.00),
(2, '2014-02-07', 8.80),
(2, '2014-03-15', 8.90);

insert into stock_io(product_id, io_date) values
(1, '2014-01-08'),
(1, '2014-01-09'),
(1, '2014-02-15'),
(1, '2014-04-03'),
(2, '2014-01-10'),
(2, '2014-03-17'),
(2, '2014-05-18');


加载中
0
quanwei9958
quanwei9958
select (select cost_price from cost_record where start_date=(select max(start_date) from cost_record where product_id=stock_io.product_id)) as ori_cost,io_id from stock_io;



quanwei9958
quanwei9958
select (select cost_price from cost_record where start_date=(select max(start_date) from cost_record where product_id=stock_io.product_id)) as ori_cost,io_id from stock_io;
0
quanwei9958
quanwei9958
上面看错了要求,修改过的:
select (select cost_price from cost_record where (stock_io.io_date-start_date)=(select min(stock_io.io_date-start_date) from cost_record where stock_io.io_date>=start_date and product_id=stock_io.product_id)) as ori_cost,io_id from stock_io;


0
JeffreyLin
JeffreyLin

引用来自“quanwei9958”的评论

上面看错了要求,修改过的:
select (select cost_price from cost_record where (stock_io.io_date-start_date)=(select min(stock_io.io_date-start_date) from cost_record where stock_io.io_date>=start_date and product_id=stock_io.product_id)) as ori_cost,io_id from stock_io;


谢谢,但有个子查询好像少了  product_id=stock_io.product_id
我改了一下,你帮我看一下对不对?
select *,
(
    select cost_price 
    from cost_record 
    where start_date=
    (
        select max(start_date)
        from cost_record 
        where stock_io.io_date>=start_date and product_id=stock_io.product_id
    )
    and product_id=stock_io.product_id
) as ori_cost
from stock_io;


quanwei9958
quanwei9958
回复 @JeffreyLin : 这也是我以前遇到过的一个问题,貌似只有增加子句查询来解决了,我也没有更好的方法
JeffreyLin
JeffreyLin
回复 @quanwei9958 : 谢谢!但这样还有一个问题,如果 cost_record 表里还有一个币种 currency_id 字段需要返回,采用这种方式,就还需要写一遍子查询,代码和查询算法感觉就不是很优雅了,不知道有没有更好的办法
quanwei9958
quanwei9958
恩,这样是对的。
0
William
William

其实我建议在 『cost_record表中增加一个 "end_date" 字段。

当新增一条成本记录时, 更新下上一个记录的 "end_date"。

这样你做查询时会容易join一条记录。 可按如下去写。 会对以后查询及统计方便些。

select m.product_id, m.io_date, c.cost_price
from stock_io as m
join cost_record as c on m.product_id = c.product_id
    and m.io_date between c.start_date and c.end_date
-- where ...
order by m.io_date




JeffreyLin
JeffreyLin
你说得对,不过 cost_record 是已有系统的订单数据的抽象,要往订单表里加个 end_date 不太现实。
返回顶部
顶部