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