php - 从日期之间的表中检查类型 MySQL,php

Question

我有如下所示的表格:

ID, Date, Type 
1,  2015-1-1,  2 
2,  2015-5-1,  5 
3,  2015-8-10,  4


(before 2015-1-1 .... type = default  
2015-1-1 <= Date < 2015-5-1 .... type = 2  
2015-5-1 <= Date < 2015-8-10 .... type = 5  
since 2015-8-10 .... type = 4) 

我只想检查任何 $date 的类型。

我试过了:

$sql = "SELECT * FROM smlouvy  
        WHERE Date < '".$date."'  
        ORDER BY Date LIMIT 1";

$result = MySQL_Query($sql);  
if ( mysql_num_rows($result) == 0 ) {
    $type = 'default';
}
else {
$row = mysql_fetch_array($result);
$type = $row["Type"];
}

Answer 1

你可以试试这样的:

select ID,date, 
          case  `date`
          when `date`<='2015-11-01' then 4
          when `date`>='2015-11-01' then 5
          else 1 end as `type` 
 from tbl;

注意:这只是一个示例，您可能需要根据自己的需要修改条件。