mysql - 存在多个实例时如何仅返回列值的 1 个(特定)实例

Question

我有以下名为“timtest”的示例表:

itemcode     qty_available  date
apple        0              1/23/2014
apple        96             1/27/2014
apple        136            2/15/2014
orange       12             1/23/2014
orange       48             2/5/2014
peach        0              1/23/2014
peach        300            2/5/2014
peach        315            2/10/2014
peach        330            2/15/2014
banana       0              1/23/2014
pineapple    24             1/23/2014

我只想在 itemcode 列中每个唯一值的一个实例。为每个唯一商品代码选择要选择的行的标准基于大于零的数量和任何大于零的数量可用的最快日期。如果今天有大于零的可用数量，我想返回该行。如果今天日期的可用数量为 0，我想在最近的将来找到下一条可用记录，其中有任何数量大于零并返回该行。如果今天的数量为零并且没有其他 future 日期，我想返回显示为零的行。该表永远不会有过去的日期，所有记录都将有一个包含今天日期的条目。

考虑到今天是 2014 年 1 月 23 日，这里是上述示例数据的所需结果集:

itemcode    qty_available   date
apple       96              1/27/2014
orange      12              1/23/2014
peach       300             2/5/2014
banana      0               1/23/2014
pineapple   24              1/23/2014

你能帮我做正确的查询吗？

Answer 1

我认为这是你想要获取日期的逻辑:

select itemcode,
       coalesce(min(case when qty_available > 0 then date end), min(date)) as thedate
from timtest tt
where date >= date(now())
group by itemcode;

表达式 coalesce(min(case when qty > 0 then date end), min(date)) 似乎封装了您的逻辑。当 qty > 0 时，合并的第一部分返回第一个日期。如果这些都不存在，则它会找到带有 0 的第一个日期。当今天没有记录时，您没有说明要做什么，但将来有 0 的记录。这将返回第一个这样的记录。

为了得到数量，让我们回到这个:

select tt.*
from timtest tt join
     (select itemcode,
             coalesce(min(case when qty_available > 0 then date end), min(date)) as thedate
      from timtest tt
      where date >= date(now())
      group by itemcode
     ) id
     on tt.itemcode = id.itemcode and tt.date = id.thedate;

编辑:

不考虑错误的日期格式。这是针对这种情况的版本:

select tt.*
from timtest tt join
     (select itemcode,
             coalesce(min(case when qty_available > 0 then thedate end), min(thedate)) as thedate
      from (select tt.*, str_to_date(date, '%m/%d/%Y') as thedate
            from timtest tt
           ) tt
      where thedate >= date(now())
      group by itemcode
     ) id
     on tt.itemcode = id.itemcode and str_to_date(tt.date, '%m/%d/%Y') = id.thedate;

对 future 的建议:将日期作为日期/日期时间数据时间而不是字符串存储在数据库中。如果您有将它们存储为字符串，请使用 YYYY-MM-DD 格式，因为您可以使用比较和排序依据。