有时候需要在适当的时候,使用临时表,来建立我们的存储过程,对于提高效率有一定的帮助。 如有时候我们需要从其他表当中查出结果,然后再对其结果集进行处理,最后再得到到我们想要的数据。
比如有两个表一个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()
就会得到如下结果。
UserID | UserName | Pass | UnPass | Total |
1 | zeng | 1 | 1 | 2 |
2 | wang | 1 | null | 1 |