MySQL中一些常见及特殊函数用法

1.decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
 

例子：(SUBSTRB(P.PAYTYPE, 2)=
       DECODE(SUBSTRB('007', 2), '03', '09', SUBSTRB('007', 2)) OR
       SUBSTRB(P.PAYTYPE, 2) =

       DECODE(SUBSTRB('007', 2), '09', '03', SUBSTRB('007', 2)))

 

翻译：如果07==03，则替换为09，否则 默认07 

  or    如果07==09，则替换为03，否则 默认07

 

2.CAST(CAST(SYSDATE()AS DATE)AS DATETIME)

业务：一天内一个用户用同一手机号发送短信次数不能超过10条

sql可以这样写：

select COUNT(*) from t_activity_register  tar where  tar.mobile=#{mobile}  and  tar.fans_id=#{fansId} and
tar.create_time >= CAST(CAST(SYSDATE()AS DATE)AS DATETIME)   （发送时间>= 系统0点时间）

 

3.COUNT(DISTINCT IF(表达式，字段名，NULL))

   SELECT
        tbca.id,
        tbca.corps_introduce,
        tbca.corps_type,
        tbca.limit_people_num,
        tbca.admin_name,
        tbca.create_time,
        tbca.corps_act_status,
        tbca.up_time,
        tbca.down_time,
        tbca.corps_own_type,
        COUNT(DISTINCT tbcj.id) AS joinNum,
        COUNT(DISTINCT tbci.id) AS openCorpsNum,
        COUNT(DISTINCT IF(tbci.success_flag = 1,tbci.id,NULL)) AS corpsSucceedNum
        FROM
        tb_corps_activity tbca
        LEFT JOIN tb_corps_info tbci ON tbci.corps_act_id = tbca.id
        LEFT JOIN tb_corps_join tbcj ON tbcj.corps_act_id = tbca.id

      能够查询出成功开团的数量

 

4.GROUP_CONCAT(字符串1，字符串2，。。。)

在有group by的查询语句中，select指定的字段包含在group by语句的后面，作为一个字段进行分组的依据。

5.CONCAT_WS(separator,str1,str2,...)

功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

实例用法：

SELECT
    tf.display_name AS displayName,
    tf.mobile AS mobile,
    tf.type AS type,
    tcj.open_id AS openId,
    tcj.is_new_vip AS isNewVip,
    tcj.corps_people_type AS corpsPeopleType,
    tca.corps_introduce AS corpsIntroduce,
    tca.corps_type AS corpsType,
    tca.corps_act_status AS corpsActStatus,
    tca.up_time AS upTime,
    tca.down_time AS downTime,
    tci.success_flag AS groupStatus,
    tci.corps_end_time AS corpsEndTime,
    tci.corps_people_type AS corpsInfoPeopleType,
    tcj.id AS id,
    tcj.corps_info_id AS corpsInfoId,
    tcj.join_time AS joinTime,
    GROUP_CONCAT( DISTINCT CONCAT_WS( ' ', tctr.response_code, tctr.ticket_id, tctr.ticket_code ) SEPARATOR '##' ) remark 
FROM
    tb_corps_join tcj
    JOIN tb_corps_info tci ON tci.id = tcj.corps_info_id
    JOIN tb_corps_activity tca ON tca.id = tci.corps_act_id
    LEFT JOIN t_fans tf ON tf.id = tcj.fans_id
    LEFT JOIN tb_corps_ticket_record tctr ON tctr.corps_act_id = tcj.corps_act_id 
    AND tctr.corps_info_id = tcj.corps_info_id 
    AND tctr.vip_open_id = tf.vip_open_id 
WHERE
    1 = 1 
GROUP BY
    tcj.id