在存储过程中使用临时表

Richard118 2013-12-19

展开全文

有时候需要在适当的时候，使用临时表，来建立我们的存储过程，对于提高效率有一定的帮助。如有时候我们需要从其他表当中查出结果，然后再对其结果集进行处理，最后再得到到我们想要的数据。

比如有两个表一个Article(ArticleID,UserID,ArticleName,ArticleType)文章类型有通过，或者未通过，,User(UserID,UserName)。现在我们要统计每个用户通过与未通过的文章的数目。

现建两个表并且插入初始数据

用户表

Create Table Users (UserID int primary key,UserName char(20));
insert Users value(1,'zeng');
insert Users value(2,'wang');

文章表

Create Table Article (ArticleID int primary key,UserID int,ArticleName char(40),ArticleType int);
insert Article value(1,1,'How Create procedure',1);
insert Article value(2,1,'Create',0);
insert Article value(3,2,'temporary table',1);

下面我们建立一个存储过程来实现这个题目，并且建立了三个临时表

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`ProGetCount` $$
CREATE PROCEDURE `ProGetCount`()
BEGIN
     create Temporary table t (UserID int,UserName varchar(100),total int);
     create Temporary table t1(UserID int,UserName varchar(100),UnPass int);
     create Temporary table t2 (UserID int,UserName varchar(100),Pass int);
       /*find all count for every User and insert into t*/
      Insert into t
     select A.UserID,UserName,count(A.UserID) from Article a,users u
     where A.UserID = U.UserID
     group by A.UserID;

      /*find UnPass count for every user and insert into t1*/
      insert into t1
      select A.UserID,UserName,count(A.UserID) from Article a,users u
     where A.UserID = U.UserID and ArticleType = 0
     group by A.UserID,UserName;

      /*find pass count for every user and insert into t2*/
      insert into t2
      select A.UserID,UserName,count(A.UserID) from Article a,users u
     where A.UserID = U.UserID and ArticleType = 1
     group by A.UserID,UserName;

      /*show dataset for all the Stat*/
      select t.UserID,t.UserName,Pass,UnPass,total
     from t t
     left join t1 t1 on t.UserID = t1.UserID
     left join t2 t2 on t.UserID = t2.UserID;
END $$

DELIMITER ;

最后只要执行

call ProGetCount()

就会得到如下结果。