Question

I have a table in MYSQL on which I need to perform a select and then order it by a fairly complex logic. Here is an example of my logic:

if(column_a is not null && column_a > 0){
   var_a = column_a
else
   var_a = 1;

if(column_a is not null){
   var_b = column_b
else
   var_b = 0

if(column_c is not null){
   var_c = column_c
else
   var_c = column_d

页: 1

因此,基本上,我想按照SélectT* 的行文,从表WHERE参数 = 按变量---顺序排列的ORDER。我将不得不使用一些公式来变换——顺序——但我找不到这样做的任何方法,这不会涉及8个单独的化学文摘社国际协会、欧洲地球科学委员会、以及似乎极其令人迷惑的发言。任何其他建议?

Thanks!

Answer 1

For a down and dirty way, you can use functions in an order by clause. In specific, you can use the if and ifnull functions. Therefore, you should be able to do something like this:

SELECT ...
ORDER BY IF(some_value IS NOT NULL AND some_value RLIKE  ^[[:digit:]]+$ , some_value, other_value)
       - (column_baz + IF(foo > bar, a, b))/(IFNULL(yet_another_value, 0) + IF(foo > bar, a, b))

(where the regular expression may have to be more complex if you want to include all reals instead of just integers, if you don t want to include a string starting with 0, etc.)

Answer 2

a complex case/where structure is really what you need, so the mess is in the syntax. if you want to make the query more elegant, check out the Adding New Functions to MySQL section of the reference manual. then you ll have something like this:

SELECT * from mytable WHERE parameter = value 
ORDER BY meaningfull_name_of_logic(
    foo, bar, some_value, other_value, yet_another_value, column_baz, a, b
);

Answer 3

things:

SELECT my_table.*,
  IF( foo > bar, a, b ) AS var_a,
  IF( some_value IS NOT NULL AND
        is_numeric( some_value ), some_value, other_value ) AS var_b,
  IFNULL( yet_another_value, 0 ) AS var_c
FROM my_table
ORDER BY var_b - (column_baz + var_a)/(var_c + var_a)

There will be 3 extra columns in your results that you should ignore. There are other ways to do this to avoid the 3 extra columns like using variables @var_a := but that will have its own messiness, I prefer the above method.

友情链接