在mysql查询中使用if

I already have this query:

我已经有了这个查询:

cursor.execute("SELECT calldate, dst, billsec, accountcode, disposition, 
                       (billsec/60*%s) as total 
                FROM   cdr 
                WHERE  calldate >= '%s' and calldate < '%s' 
                   and disposition like '%s' and accountcode = '%s' 
                   and dst like '%s'" %(rate, start_date, end_date, status, accountcode, destino)
              )

Obs: dst is a telephone number.

Obs: dst是一个电话号码。

But now what I need to do is: If the 5th char of dst is <= 5, then billsec/60*0.11 as total else billsec/60*0.16.

但是现在我需要做的是:如果dst的第5个char是<= 5，那么billsec/60*0.11就等于total else billsec/60*0.16。

Is it possible?

是可能的吗?

This way I got an error in mysql syntax:

这样我在mysql语法中得到了一个错误:

    cursor.execute("""SELECT calldate, dst, billsec, accountcode, disposition, 
                    case when cast(substring(dst,4,1), unsigned) <= 5 then
                            billsec/60*%s as total
                    else
                            billsec/60*%s as total
                    end case
                    FROM cdr where calldate >= '%s' and calldate < '%s' and disposition like '%s' and accountcode = '%s' and dst like '%s'""" %(rate_fixo, rate_movel, start_date, end_date, status, accountcode, destino))

2 个解决方案

#1

With SELECT you actually specify set of columns to be returned. This set can be simply names of columns, or specific transformation of these columns, calling SQL function. "IF" actually is more related to procedural SQL, in your example you need something called CASE expression. The trick is to define a column in result set by CASE expression, like this

使用SELECT，您实际上指定要返回的列集。这个集合可以是简单的列名，或者是这些列的特定转换，调用SQL函数。“IF”实际上与过程SQL更相关，在您的示例中，您需要一些称为CASE表达式的东西。诀窍是按CASE表达式在结果集中定义一个列，如下所示

SELECT acol, bcol, ccol, dcol from t1 where ...

compare with

与

SELECT acol, CASE WHEN sqlfunc_like_substr(someparams,bcol) THEN bcol ELSE some_other_way_modified(bcol) END, ccol, dcol from t1 WHERE ...

Don't remember CASE exact syntax at the moment, but this is the way. More, you can name resulting column eg.

现在不记得大小写的语法，但就是这样。此外，还可以命名结果列eg。

SELECT acol as uuu, CASE WHEN ... END as mmm, ccol as nnn, dcol as qqq FROM t1 where...

and so on :) The point is to understand, that SELECT actually does not selects columns to be retrieved, but defines particular set of result columns, with some of them as they are in table, and some as subresult of column value transformation or a string, or NULL, or sth else.

等等:)关键是理解,其实选择不选择列检索,但定义了特定的结果列,其中一些表,和一些列值的subresult转换或一个字符串,或零,或某事。

#2

Yes this is possible. Mysql queries can contain if statements, only they are called case statements, as has been noted by other Piotr Wadas.

是的这是可能的。Mysql查询可以包含if语句，只有它们被称为case语句，其他Piotr Wadas已经注意到。

See here: http://dev.mysql.com/doc/refman/5.0/en/case.html

在这里看到的:http://dev.mysql.com/doc/refman/5.0/en/case.html

To extract a character from a string use substring. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

要从字符串中提取字符，请使用子字符串。http://dev.mysql.com/doc/refman/5.0/en/string-functions.html function_substr

So the complete solution would be something like:

所以完整的解决方案应该是这样的:

case when cast(substring(dst,4,1), unsigned) <= 5 then 
   billsec/60*0.11
else
   billsec/60*0.16
end case

#1

SELECT acol, bcol, ccol, dcol from t1 where ...