一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数,用SQL语句及视图、存储过程分别实现。
mysql
存储过程:
DELIMITER //
create procedure proc_countNum(in columnIdint,out rowsNo int)
begin
select count(*) into rowsNo from memberwhere member_id=columnId;
end
call proc_countNum(1,@no);
select @no;
视图:
create view v_countNum as selectmember_id,count(*) as countNum from member group by member_id
select countNum from v_countNum wheremember_id=1