mysql函数使用-字符串处理
1、按地区统计数据
select q.psn,t.name,sum(q.quarantinePigCount) from (select RPAD(substring(pigSourceNo,1,2),6,'0') as psn,quarantinePigCount from quarantines where status <> -1 and createDate between '2012-05-01 00:00:00' and '2012-05-05 23:59:59' ) q,typed_datas t where q.psn=t.id group by q.psn,t.name
1
selectq.psn,t.name,sum(q.quarantinePigCount)from(selectRPAD(substring(pigSourceNo,1,2),6,'0')aspsn,quarantinePigCountfromquarantineswherestatus<>-1andcreateDatebetween'2012-05-01 00:00:00'and'2012-05-05 23:59:59')q,typed_datastwhereq.psn=t.idgroupbyq.psn,t.name
欢迎大家阅读《mysql函数应用-字符串处理》,跪求各位点评,by 搞代码
2、其中select RPAD(substring(pigSourceNo,1,2),6,'0') 包含两个内容
(1)MySQL 字符串截取函数:left(), right(), substring(), substring_index()。
substring从字符串的第 1 个字符位置开始取,只取 2 个字符。
mysql> select substring('510700', 1, 2); +---------------------------------+ | substring('510700', 1, 2) | +---------------------------------+ | 51 | +---------------------------------+
1
mysql>selectsubstring('510700',1,2);+---------------------------------+|substring('510700',1,2)|+---------------------------------+|51|+---------------------------------+
(2)Mysql函数中RPAD(str,len,padstr) 意思为:返回字符串str,右面用字符串padstr填补直到str是len个字符长。
mysql> select RPAD('51', 6, '0'); +---------------------------------+ | RPAD('51', 6, '0') | +---------------------------------+ | 510000 | +---------------------------------+
1
mysql>selectRPAD('51',6,'0');+---------------------------------+|RPAD('51',6,'0')|+---------------------------------+|510000|+---------------------------------+
类似的还有LPAD(str,len,padstr) 意思为:返回字符串str,左面用字符串padstr填补直到str是len个字符长。
原创文章,转载请注明: 转载自搞代码
微信 赏一包辣条吧~
支付宝 赏一听可乐吧~