mysql 存储过程

drop procedure if exists queryLive;

delimiter //

CREATE  PROCEDURE queryLive(

IN contentType int ,

IN offset int ,

IN pageSize int ,

IN userId char(20)

)

begin

SELECT  c.id AS id,

c.type,c.title,

comment.summary,

comment.time,

comment.images,

user.loginName AS authorId,

user.nickName AS authorName,

user.heat AS subscribeNum,

user.favicon AS authorIcon,

c.price,

userroles.loginName AS authorIsTeacher,

teachersubs.teacherId AS isSubscribe,

ulike.contentId AS isLike,

c.likeNum AS likeNum,

c.commentNum AS commentNum

FROM (select * from content  WHERE type = 5 ORDER BY content.id DESC LIMIT offset,pagesize) as c

LEFT JOIN user ON c.userId = user.loginName

LEFT JOIN userroles ON c.userId = userroles.loginName AND userroles.roleName = 3

LEFT JOIN ulike ON c.id = ulike.contentId

AND c.userId = userId

LEFT JOIN teachersubs ON c.userId = teachersubs.teacherId AND teachersubs.userId = userId

left join new_live on c.id = new_live.liveId

LEFT JOIN comment ON comment.id = new_live.commentId;

end//

call queryLive();