MySQL Advanced sort & Advanced Matching query example

In most scenarios, we use MySQL to query with only the ' = ', ' > ', ' < ', in, like and other commonly used methods, it seems, in most cases, is enough to deal with our small applications. However, in some special scenarios, a special query method is required.

1. Queries sorted by Status

Suppose that there are now four states of a record, unhandled (0), processing (2), processing success (1), processing failure (4), and the reason their values are this way, because we generally do not use it to sort, so it is natural to think that some of these values give meaning. But how do you deal with it when you sort? If the order of the requirements is this: unhandled > Processing failure > Processing > Processing success, what I can think of is that assuming that these states are incremented in order of the ordered values, then only one order by that field is required. The specific implementation is as follows:

Select *  fromTab_task bOrder  by( CaseB.deal_status when 0  Then Ten  when 2  Then  -  when 1  Then  +  when 4  Then  -  ELSE  + END)ASC, B.add_timedesc     //According to the desired sort value, assign the value in turn

If you want to sort by a calculated value, such as by average, the implementation is as follows:

SELECT *  from GROUP  by ORDER  by AVG　　　　DESC; // Direct calculation of sorted values, using rand () to get a random sort

2. More complex fuzzy queries

Sometimes we need to filter the values that match the criteria from a field, but the value is not a separate field, so we need some advanced fuzzy queries (regular).

For example, you need to query a record with a value greater than 0, as follows:

SELECT *  from WHERE ' . *rd1= ([0][1-9]+|[ 1-9][0-9]*). *';      // query rd{x} records that are greater than 0, where Rd may be a number of similar unimportant values that are stored in a field, in the same vein as other

The MySQL regular syntax is as follows:

// . Indicates a match to any one character// |as an OR operator that matches one of these// [ ]match any single character// [: A;num:]Any letter and number (same as[a-za-z0-9])// [: Alpha:]Any character (same as[a-za-z])// [: blank:]Spaces and tables (same[\\t])// [: Cntrl:]ASCII control characters (ASCII0 to 31 and 127)// [:d igit:]Any number (same as[0-9])// [: Graph:]And["Print:]same, but does not include spaces// [: Lower:]Any lowercase line (same as[A- z])// [:p rint:]any printable character// [:p UNCT:]is neither in[: Alnum:]It's not.[: Cntrl:]any of the characters in// [Space:]Any whitespace character, including spaces (same as[\\f\\n\\t\\r\\v])// [: Upper:]Any size letter (same as[A- z])// [: xdigit:]Any hexadecimal digit (same as[a-fa-f0-9])//Meta characters.*?+{m} {m,} {m, n}

The grammar is not consistent with the Orthodox regular, but the general principle is the same.

With MySQL to do complex matching, although it can be achieved, but relatively inefficient, it is best to use the form of independent fields to query, space exchange for TIME!

　　

Ask?

After MySQL sub-table (such as by the year table, multiple related tables synchronized split), query should be how to do?

MySQL Advanced sort & Advanced matching query example