例如有两个表goods和prices,商品表中的一条商品信息会对应价格表中的多条价格信息,现在要根据商品表查询其商品对应的可用最新的价格,即要满足价格的生效日期必须在商品上市之前,如果有多个价格,就选取最后生效的那个价格。
goods:商品表
序号 | 商品名称 | 上市日期 |
1 | iphone6 | 2015-10-01 |
2 | iphone6 | 2016-08-01 |
3 | iphone6 | 2017-09-01 |
prices:价格表
序号 | 商品名称 | 单价 | 生效日期 |
6 | iphone6 | 6500 | 2015-09-15 |
7 | iphone6 | 6000 | 2016-06-15 |
8 | iphone6 | 5800 | 2017-08-15 |
希望得到的结果是:
货物序号 | 商品名称 | 上市日期 | 单价 | 单价生效日期 |
1 | iphone6 | 2015-10-01 | 6500 | 2015-09-15 |
2 | iphone6 | 2016-08-01 | 6000 | 2016-06-15 |
3 | iphone6 | 2017-09-01 | 5800 | 2017-08-15 |
select A.*,B.price,B.effective_date from goods A left join prices B
on A.goods_name = B.goods_name and A.launch_date > B.effective_date
and B.effective_date = (
select max(effective_date) from prices where A.goods_name = goods_name and A.launch_date > effective_date)
order by A.id红色部分语句是用来筛选B表中满足条件的第一条数据,相当于把left join后的联查条件做了二次查询,这个条件如果漏掉,会导致查询结果为一对多。
(2) 当然还有一种不太严谨的写法,必要前提是prices表格中,数据 插入顺序(或者说主键顺序)与生效时间顺序是一致的,那么上面那个sql就可以简写为:
select A.*,B.price,B.effective_date from goods A left join prices B
on B.id = (select max(id) from prices where A.goods_name = goods_name and A.launch_date > effective_date)
order by A.id
这种写法使用场景有限,需要慎用。