我是存储过程的新手
select distinct(movie_id) from movielist where mdate = ?
这个返回movie_id,我需要为每个movie_id运行 3 个 sql
foreach(movie_id){
select count(*) as total_viewed from movielist where mdate = ? and movie_id = ? limit 1 //return only one value
select count(*) as total_viewed from movielist where mdate like ? and movie_id = ? limit 1 //return only one value
select count(*) as total_viewed from movielist where mdate like ? and movie_id = ? limit 1 //return only one value
//Then all the 3 value with movie_id and mdate insert at another table
}
现在如何为此编写存储过程...
我想你想根据每部电影显示观看次数
select count(*) as total_viewed from movielist group by movie_id
这将显示基于movie_id的所有计数
select count(*) as total_viewed from movielist group by mdate,movie_id
将显示基于日期和电影的所有计数。
您可以使用 UNION ALL 合并结果(只是一个提示,可以使用,不知道您真正想要什么)。
您可以使用游标,但根本不需要。
我不确定您的存储过程应该做什么,但看起来要给出特定日期观看次数前 3 部的电影?如果是这样,就去做
select movie_id, count(*) as total_viewed from movielist
where mdate = ?
group by movie_id
order by total_viewed desc
limit 0,3;