存储过程可以实现以下功能吗?需要区从2009-10-01起到现在每天的最多访问user表次数的用户。例如求2009-10-01 这天多访问user表次数的用户为
select id,count(*) as cnt from user where date(createDate)='2009-10-01' group by id limit 1.
现在考虑如何用存储过程的游标来实现循环90次?user表结构 (id,name (id为整形,重复很多))
如果要在存储过程里作,可以每次把id 和cnt into到1个临时表 a—cac (id,cnt )
这样来
insert into a—cac
select id,count(*) as cnt from user where date(createDate)='2009-10-01' group by id limit 1.
关键是日期循环和游标控制不知道如何作?
select id,count(*) as cnt from user where date(createDate)='2009-10-01' group by id limit 1.
现在考虑如何用存储过程的游标来实现循环90次?user表结构 (id,name (id为整形,重复很多))
如果要在存储过程里作,可以每次把id 和cnt into到1个临时表 a—cac (id,cnt )
这样来
insert into a—cac
select id,count(*) as cnt from user where date(createDate)='2009-10-01' group by id limit 1.
关键是日期循环和游标控制不知道如何作?
set dt='2009-10-01';
WHILE dt<'2009-12-12' DO
...
SET dt = dt+ interval 1 day;
END WHILE;
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
CLOSE cur2;
END
日期循环declare dt date;
set dt='2009-10-01';
WHILE dt <'2009-12-12' DO
...
--我这里加上我的sql
insert into a—cac
select id,count(*) as cnt from user where date(createDate)='dt‘
group by id limit 1.
SET dt = dt+ interval 1 day;
END WHILE; 唯一的疑问是 是否可以把变量dt直接传入到sql里?()?????????? (如何不对 该如何作)insert into a—cac
select id,count(*) as cnt from user where date(createDate)='dt‘
group by id limit 1.
引号不加。
select id,count(*) as cnt from user where date(createDate)=dt
如果是int date这样 可以直接使用 date(。。)=dt
如果是字符型 ,可以这样用??? (如果不是 该如何传入参数)
create procedure login( name varchar(10),in pwd varchar(10))
begin
select * from tableLogin where username=name and password=pwd;
end$$
delimiter ; 这个是对的
select * from tableLogin where username=name and password=pwd;
后面的;把存储过程截断了
begindeclare a varchar(20);select userName into a from User where date(createdDate)=name limit 1;set name=name+1;select a;end;
看不懂你的逻辑功能。一个日期型和一个name 去比什么呢?