Mysql：如果不满足搜索条件，查询可以返回最后一个条目吗？

Question

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

Answer 1

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.