几个常用sql函数使用举例

 1.FIND_IN_SET、replace函数获取某个结点在某个结点下的级别以及case/when/then/end的使用：

select u.userid,u.repid,u.parentid,u.usertype,u.firstname,u.lastname,u.city,u.state,
FIND_IN_SET('-',replace(replace(parentid,'101-102-103-104-',''),'-',',-')) downlevel,
case when c.status=0 then 'ACTIVE' when c.status=2 then 'LOCKED'  when c.status=3 then 'VACATED' when c.status=4 then 'VACATING' END status ,
l.levelname rank,c.personalcv totalcv
from commission c  
LEFT OUTER JOIN  user u ON u.userid = c.distributorid 
LEFT OUTER JOIN level l ON c.levelid = l.levelid 
where c.year= 2008 and month = 3  AND ( u.parentid LIKE '101-102-103-104-%' OR u.parentid LIKE '%-101-102-103-104-%' ) 

order by downlevel

 

注：（1）如果parentid等于‘101-102-103-104-105-106-’，那么此结点相对于104结点来说，downlevel的值是3。

        （2）注意case/when/then/end的使用。

2.IF函数的使用

select o.ordernumberfordisplay,if(o.state is null or o.state = '',u.state,o.state) state,
if(o.city is null or o.city = '',u.city,o.city) city,
if(o.county is null or o.county = '',u.county,o.county) county,o.tax,o.paidtime
from orders o,user u
where o.paidtime BETWEEN '2008-09-03 0:00:00' AND '2050-01-02 0:00:00'  and u.state = 'AK' and o.status in ( 20,21,40,41,50 )  and u.userid = o.shipto

 

注：使用IF语句，不仅可以判断is null，还能够判断为空的情况。

3.SUM和IFNULL函数的使用

SELECT levelid,
triplediamondexecutive,
SUM(IFNULL(retailcommission, 0))+SUM(IFNULL(overridecommission,0))+SUM(IFNULL(adjustedcommission, 0)) AS commissionspaid
FROM commission
WHERE (year*100+month) > '$P!{fromdate}' AND (year*100+month) < '$P!{todate}'
group by commissioned

 

 

其实，在使用sql函数时，我们可以到MySQL用户手册中查，这不需要多长时间。