douhao6271
2015-10-09 17:39
浏览 137
已采纳

Mysql:如果不满足搜索条件,查询可以返回最后一个条目吗?

I have a script that searches certain values depending on a requested date.

SELECT `entry_value` FROM `data` WHERE (`entry_year` =  $year AND `entry_month` = $month) LIMIT 1;

Now, the problem is that when the user enters a too recent date and no values are found for that, I want to get the most recent available entry.

I know how to retrieve the last entry and I could use a first call to check first what the last available date is, and call either the query above or an other one to get the last value if the requested one is not available. Just wondering, though, is there a way to ask that directly to mysql? I can't write the code as I have no idea of the kind of construct to use, but ideally the logic would be something like:

SELECT `entry_value` FROM `data` 
WHERE (`entry_year` =  $year AND `entry_month` = $month) 
OR_IF_NOT_FOUND WHERE MAX(`id`) 
LIMIT 1;

Is there something like that in mysql?

Kind regards

图片转代码服务由CSDN问答提供 功能建议

我有一个脚本可根据请求的日期搜索某些值。

  SELECT`entry_value` FROM`data` WHERE(`entry_year` = $ year AND`intry_month` = $ month)LIMIT 1; 
   
 
 

现在, 问题是,当用户输入一个过于日期的日期并且没有找到任何值时,我想获得最新的可用条目。

我知道如何检索最后一个条目和 我可以使用第一个调用来检查最后一个可用日期是什么,并调用上面的查询或另一个来获取最后一个值,如果请求的那个不可用。 但是,我想知道,有没有办法 直接问mysql? 我无法编写代码,因为我不知道要使用的构造类型,但理想情况下逻辑类似于:

  SELECT`entry_value` FROM`data  `
WHERE(`entry_year` = $ year AND`intry_month` = $ month)
OR_IF_NOT_FOUND WHERE MAX(`id`)
LIMIT 1; 
   
 
 

是否有 类似于mysql中的东西?

亲切的问候

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • doubo7131 2015-10-09 18:06
    已采纳

    Method 1

    You can run your query, followed by the "default" query with the same fields in the same order, using UNION.

    SELECT ...query1...
    UNION
    SELECT * FROM ( SELECT ... LIMIT 1 ) AS defaults;
    

    This will always run the first query and the second query, and will always return one result more than the first query alone would. It is up to you then to discard the last result.

    Another possibility would be do it in reverse order. Now the "empty" result is returned first:

    SELECT * FROM ( SELECT ... LIMIT 1 ) AS defaults;
    UNION
    SELECT ...query1...
    

    This way you can extract one tuple, and then go on as if you had run the first query alone.

    Method 2

    In some cases you can establish a preference order with results. For example:

    SELECT `entry_value` FROM `data` WHERE (`entry_year` = $year AND
    `entry_month` = $month) LIMIT 1;
    

    could become:

    SELECT `entry_value` FROM `data` ORDER BY 
        (`entry_year` = $year AND `entry_month` = $month) DESC,
        `entry_year` = $year DESC,
        ABS(`entry_year` - $year)*12 + ABS(`entry_month` - $month)
    LIMIT 1;
    

    This will retrieve all data, and (entry_year = $year AND entry_month = $month) can be either true (1) or false (0). The DESC order will make it so the first row will be the one with a perfect match.

    Similarly, among rows that do not match, a row at random with the right year will be selected if it exists. But if no data at all exists for the wanted year, then the row with the minimum distance in time will be selected instead.

    This method strongly recommends having an index on the used fields (year and month), and will do a scan for all the records. So it can be quite expensive.

    Method 3

    Performance-wise, method 1 is almost the same thing as running both queries, so if at all possible you would rather do it explicitly, something like:

    // CODE BEFORE MODIFICATION (ran 1 query)
    $query = "SELECT...";
    // Execute query
    
    foreach ($tuple = SQLFetch($handle)) {
         // ...do something with data
    }
    
    
    
    // CODE AFTER (2 queries - or any number, actually)
    
    $queries = [                   
        "SELECT ...",
        "SELECT ... LIMIT 1",
    ];
    foreach ($queries as $query) { 
        // $query = 
        // Execute $query
        foreach ($tuple = SQLFetch($handle)) {
            // ...do something with data
        }
        if (SQLNumRows($handle) > 0) {
            break;
        }
    }
    

    As you can see, the old code is practically kept intact, and the extra query (or queries) is only executed if the query before it did not return anything. The actual instructions to fetch tuples and retrieve tuple count depend on the actual database interface you're using.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongwei2983 2015-10-09 17:55

    http://sqlfiddle.com/#!9/437374/5

    SELECT 
      COALESCE(d1.id,d.id) id, 
      COALESCE(d1.entry_value,d.entry_value) entry_value 
    FROM `data` d
    LEFT JOIN `data` d1
    ON d1.entry_year =  $year
      AND d1.entry_month = $month 
    ORDER BY d.id DESC
    LIMIT 1;
    
    评论
    解决 无用
    打赏 举报
  • duanliang1898 2015-10-09 18:28

    I think this might work for you:

     SELECT `entry_value` FROM `data` WHERE 
     (`entry_year` =  $year AND `entry_month` = $month) 
     OR 
     (`entry_year` =  $most_recent_year AND `entry_month` = $most_recent_month) 
     LIMIT 1;
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题