今天要做一个统计报表，想到使用存储过程来做。

mysql的存储过程以前没有用过，这次想试试，在编写的过程中遇到了不少问题：

首先，看了官方的文档 ，不过对于存储过程，还是要使用才知道。文档是死的，人是活的。

1.怎么创建存储过程，自然也是使用一定语法，然后运行，如果成功，会有一个对应的文

件产生，并可以通过 call 来调用。

2.设定变量要注意，不是全局变量（@）时，要先定义（declare year_months varchar(10)）

3.设置变量时的语法，在变量前面加 set 关键字，不然报错

4.直接执行sql,是可以的，但是如果我们的sql是动态的，比如我们表名是通过参数传进来的。

这时间要另外一种方式了，要将sql，使用concat(,,,),来讲sql连接起来。还有就是字符串拼接时，

最好使用concat(见下面的)函数，不然会有问题。

下面是我写的一个实例：

drop   procedure  if   exists   `crm_sms_stat`;

create definer=`boss`@`%` procedure `crm_sms_stat`(in years varchar(10),in m varchar(10),in last_months varchar(10))
begin
     declare year_months varchar(10);
     declare year_m varchar(10);
     declare mt_sms_yearmonth varchar(20);
     declare r_year_month varchar(20);
     declare last_r_year_month varchar(20);
    
    
     set year_months = concat(years,m);
     set year_m = concat(years,'-',m);
     set mt_sms_yearmonth = concat("mt_sms_",year_months);
     set r_year_month = concat(years,"_",m);
    
     if m = "01" then
        set years = years - 1;
        set last_months = "12";
     end if;
     set last_r_year_month = concat(years,"_",last_months);
    
     if years = "2008" then
        set r_year_month = concat("r",r_year_month);
        set last_r_year_month = concat("r",last_r_year_month);
     end if;
    
    
     /*---------------------------------------------------------------- */
     set @insertstat = concat(' insert into crm_sms_stat(months,user_id,username,agentid) ',
                              ' select "',year_months,'",u.user_id,u.username,u.agentid from ',mt_sms_yearmonth,
                              ' mt inner join users u on mt.user_id=u.user_id group by mt.user_id ');
     prepare inserts from @insertstat;
     execute inserts;
    
    
     set @tempupdateremain = concat(' update crm_monthremain cm,crm_sms_stat css set css.last_remain = ifnull(',
                                     last_r_year_month,',0),css.this_remain = ifnull(', r_year_month,',0) ',
                                     ' where cm.id = css.user_id and css.months = "',year_months,'" ');
     prepare remain from @tempupdateremain;
     execute remain;
   
     -- 更新每个月用户所冲条数
     set @tempadd = concat(' update crm_sms_stat css inner join (select name,sum(total_count) addnum from boss_addmoney  ',
                           ' where left(add_date,7)= "',year_m,'" and total_count >=0 and name not like "%=%" and name not like "%:%" group by name) ',
                           ' x on css.username= x.name and css.months = "',year_months ,'" ',
                           ' set css.add_num = ifnull(x.addnum,0) ');
     prepare addnum from @tempadd;
     execute addnum;
            
                  
            
       
     if years = "08" then
        -- 更新通道发送数（200901之前是不分卡发和通道的）
        set @tempchannel08 = concat('update crm_sms_stat css inner join (select mt.user_id,',
                                       ' sum(length(replace(mt.dest_mobile,";","")))/11  chennel_num',
                                       ' from ',mt_sms_yearmonth,' mt ',
                                       ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                       ' set css.channel_num = ifnull(x.chennel_num,0) ');
       
        prepare channel08 from @tempchannel08;
        execute channel08; 
     else
         -- channel send number
         set @tempchannel = concat('update crm_sms_stat css inner join (select mt.user_id,',
                                       ' sum(length(replace(mt.dest_mobile,";","")))/11  chennel_num',
                                       ' from ',mt_sms_yearmonth,' mt where mt.channel_id != 312 ',
                                       ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                       ' set css.channel_num = ifnull(x.chennel_num,0) ');
       
         prepare channel from @tempchannel;
         execute channel; 
         -- card send number    
         set @tempcard = concat('update crm_sms_stat css inner join (select mt.user_id,',
                                   ' sum(substring_index(substring_index(mt.dest_mobile, "real", 1),":",-1)) as submit_card_num,',
                                   ' sum(substring_index(mt.dest_mobile,":",-1)) as real_card_num',
                                   ' from ',mt_sms_yearmonth,' mt where mt.channel_id = 312 ',
                                   ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                   ' set css.submit_card_num = ifnull(x.submit_card_num,0),css.real_card_num = ifnull(x.real_card_num,0) ');
         prepare card from @tempcard;
         execute card; 
     end if;    
    
end;

select * from company
where username in (select  username  from  company  group  by  username  having  count(username) > 1)

212397

select * from company
where username ='212397'

-----------------------------

select username,count(*) from company group by username having count(*) > 1

delete from company
where username  in (select  username  from company  group  by  username   having  count(username) > 1)
and id not in (select min(id) from  company  group by username  having count(username )>1)

delete from company
where (username in
          (select username
         from company
         group by username
         having count(username) > 1)) and (id not in
          (select min(id)
         from company
         group by username
         having count(username) > 1))

--------------------------------------------------------------

select distinct * into #tmp from tablename

drop table tablename

select * into tablename from #tmp

drop table #tmp

=============================================
 操作以下语句删除重复公司
create table tmp as select min(id) as col1 from company group by username;
delete from company where id not in (select col1 from tmp);
drop table tmp;

-----------------------------------------

我是一个招聘网站。
有很多招聘的用户名重复，但有的用户名重复，只是同一个用户名但又不是同一个职位。

能不能写一条语句，把用户名和职位都重复的列出来。

select
  a.*
from
  job_list  a,
  (select username,jobtitle from job_list group by username,jobtitle having count(1)>1) b
where
  a.username =b.username  and a.jobtitle=b.jobtitle

-----------------------------------------

alter table job_list add autoid int auto_increment not null;

create table tmp select min(autoid) as autoid from job_list group by username,jobtitle;

create table tmp2 select job_list.* from job_list,tmp where job_list.autoid = tmp.autoid;

drop table job_list;

rename table tmp2 to job_list;

----------------------------------------

create table tmp select min(id) as id from job_list group by username,jobtitle;

create table tmp2 select job_list.* from job_list,tmp where job_list.id = tmp.id;

drop table job_list;

rename table tmp2 to job_list;

现有一个库存表
一个字段是销售代码sales_code
另一个字段是库存数量 quantity
想实现这样的功能，每次买回来的物品输入该表 相应的物品库存数目会自动加上新买的数量

update
goods_store
set
`store_quantity`=(`store_quantity`+{$qty})
where
`goods_salescode`='$goods_salescode'

高级应用:

<?
session_start();
include ('session.php');
require_once("../conn.php");
require_once("../include/function.php");
$id=$_get["id"];
$status = $_get['status']; 
$greennum= $_get['greennum'];
$shopnum= $_get['shopnum'];
$service_prize= $_get['service_prize']*get_star_info($greennum)/100;
$shop_prize=$service_prize*get_centerstar_info($greennum)/100;
//echo $status;
//echo "<pre>";
//echo $id;

mysql_query($exec="update shop_greencard  set status=".$status." where orderid=".$id.""); //sql语句，更新表
if ($status==1){
mysql_query($exec="update servicer set kefubi=kefubi+".$service_prize." where greennum=".$greennum.""); //sql语句，更新表
mysql_query($exec="update shopcenter  set kefubi=kefubi+".$shop_prize." where shopnum=".$shopnum.""); //sql语句，更新表
echo $service_prize;
echo "<br>";
echo $greennum;
echo "<br>";
echo $exec;
}
else
{
mysql_query($exec="update servicer   set kefubi=kefubi-".$service_prize."  where greennum=".$greennum.""); //sql语句，更新表
mysql_query($exec="update shopcenter  set kefubi=kefubi-".$shop_prize." where shopnum=".$shopnum.""); //sql语句，更新表
}
echo "<script>alert('修改成功！');self.location='pay_list.php';</script>";
?>

by agang

这里记录了目前书中发现的各类错误，欢迎广大读者能将发现的问题写在评论里，我会及时进行更新。
P59 两个例子中间文字的第三行，”这是“应该为”这时“
P73 上半部分步骤（3）中的表名应该将“t10”改为“t”
P87.P88  函数CANCAT应该为CONCAT
P98页，最下面例子结果应该是6行，如下所示：

阅读全文……