最近在统计一些数据（SQL）

下面是我在统计过程当中学习到的一些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