下面是我在统计过程当中学习到的一些sql的使用实践经验：
----------------------------------------------------主要使用了一些字符串函数，和表的左连接
select instr("23,2323,23,23,23,23",",")
select * from mt_sms_200804 LIMIT 10
select u.user_id,
u.username,
u.agentid,
sum( length(dest_mobile) +1 - length(replace(dest_mobile,';',''))) as channel_num,
ifnull(x.addNum,0) as add_num
from mt_sms_200804 mt
left join users u on mt.user_id=u.user_id
left join (select name,sum(total_count) addNum from
boss_addmoney
where left(add_date,7)= '2008-04' and total_count >=0 and
name not like '%=%' and name not like '%:%' group by name) x on
u.username= x.name
group by mt.user_id limit 3;
select COUNT(*) from mt_sms_200804 where user_id = NULL
-------------------------------------------------------这里使用了查询语句插入的一种语法，以前很少用到过
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name
,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name
=expr
, ... ]
使用INSERT...SELECT
，您可以快速地从一个或多个表中向一个表中插入多个行。
示例：
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
-- 通道发送
select length(replace("13604903611;13609898885;13604072967",';',''))/11
select mt.user_id,
length(replace(dest_mobile,';',''))/11 channel_id
from mt_sms_200904 mt
WHERE mt.channel_id != 312 and mt.user_id = 39
group by mt.user_id
-- 卡发记录格式： displayCount:1499realSendCount:1499
select SUBSTRING_INDEX(SUBSTRING_INDEX('displayCount:149339realSendCount:123300', 'real', 1),":",-1);
select SUBSTRING_INDEX("displayCount:1499realSendCount:1411",":",-1)
select * from mt_sms_200904 where channel_id = 312 LIMIT 10
select LOCATE(',', '23,2323,23,23,23,23',1);
select "200904",
sum(SUBSTRING_INDEX(SUBSTRING_INDEX(dest_mobile, 'real', 1),":",-1)) as submit_card_num,
sum(SUBSTRING_INDEX(dest_mobile,":",-1)) as real_card_num
from mt_sms_200904 mt
left join users u on mt.user_id=u.user_id
WHERE mt.channel_id = 312
group by mt.user_id limit 10
SELECT count(*) from mt_sms_200904 where channel_id = 312
------------------------------------------------------------- 批量更新，这里是我第一次用到的批量更新，多个表之间更新数据
UPDATE crm_monthRemain cm,crm_sms_stat css SET css.last_remain = cm.R2008_03,css.this_remain = cm.R2008_04
WHERE cm.id = css.user_id and css.months = "200804";
UPDATE crm_sms_stat css left join (select name,sum(total_count) addNum from boss_addmoney
where left(add_date,7)= '2008-04' and total_count >=0 and
name not like '%=%' and name not like '%:%' group by name)
x on css.username= x.name
SET css.add_num = x.addNum
