mysql - SQL 按日期选择查询

Question

我有两个相关的表，其中包含许多基于日期的记录。我只需要根据特定日期选择有效汇率。

这些是数据的例子:

在标题表上...

 ID, PICKUP, DELIVERY, VALIDFROM, VALIDTO, COMMODITY, CARRIER1, NEW YORK, CHICAGO, 01-OCT-13,31-OCT-13, FOOTWEAR, DHL2, NEW YORK, CHICAGO, 20-OCT-13,31-OCT-13, FOOTWEAR, UPS3, NEW YORK, CHICAGO, 30-OCT-13,31-OCT-13, FOOTWEAR, DHL4, NEW YORK, CHICAGO, 30-OCT-13,31-OCT-13, WOODEN PRODUCTS, DHL

on linestable:

ID, HEADERTABLEID, RATEDESC, RATE2325, 1, DELIVERY, 10002354, 2, DELIVERY, 15002355, 2, TAX, 1002355, 3, DELIVERY, 12002356, 3, TAX, 1102380, 4, DELIVERY, 1000

So by using a certain date (thedate variable below), I need to display ONLY one option. For example, if thedate=25-oct-13, then the record to be shown is Id 2 (from headertable + linked rates in linestable)

If thedate=10-oct-13, then option 1 is the one to be shown.

if thedate=30-oct-13, then 2 records (Id 3 and 4) will be displayed (difference is the COMMODITY)

This is the code I am using (I've tried many others, with no luck) but obviously there is something missing as it displays all records.

Any help is appreciated.

SELECT  * from schema1.headertable, schema1.linestable
WHERE headertable.Id= linestable.headertableId
AND headertable.Validfrom <= STR_TO_DATE('" & thedate & "','%Y-%m-%d')
AND headertable.Validto >= STR_TO_DATE('" & thedate & "','%Y-%m-%d')
AND headertable.PICKUP LIKE '%NEW YORK%'
AND headertable.DELIVERY LIKE '%CHICAGO%'
ORDER BY headertable.Validfrom DESC, linestable.Id DESC

Answer 1

要仅返回结果集中的第一条记录，请将LIMIT 1 附加到查询末尾。