MySql 左加入几个 regs

Question

我有这张 table 1

idproduct(PK) | date_to_go
1               2010-01-18
2               2010-02-01
3               2010-02-21
4               2010-02-03

和另一个控制 date_to_go 更新的表 2

id | idproduct(FK) | prev_date_to_go | date_to_go | update_date
1    1               2010-01-01        2010-01-05   2009-12-01
2    1               2010-01-05        2010-01-10   2009-12-20
3    1               2010-01-10        2010-01-18   2009-12-20
4    3               2010-01-20        2010-02-03   2010-01-05

因此，在此示例中，对于 table1.idproduct #1 2010-01-18 是实际的 date_to_go 和 2010-01-01(table2.prev_date_to_go，第一个 reg)是原始的 date_to_go。

使用这个查询

select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go 
from table1 v 
left join produto_datas p on p.idproduto = v.idproduto
group by (v.idproduto)
order by v.idproduto

我可以假设 original_date_to_go 将是 table2 的第一个相关 reg 吗？

idproduct | date_to_go | original_date_to_go 
1           2010-01-18   2010-01-01
2           2010-02-01   NULL
3           2010-02-21   2010-01-20 
4           2010-02-03   NULL 

Answer 1

不，您不能假设您总是从第一行获得 prev_date_to_go 的值。当您选择 GROUP BY 中未列出的列时，您可以获得的值可能是来自任何行的值。请参阅 MySQL 文档 GROUP BY and HAVING with Hidden Columns了解更多信息:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.