今天遇到了以前项目的查询效率问题,所以决定用存储过程。但是第一次用,没有找到合适的例子。所以请大家帮帮忙。
我有两条查询,需要把结果组合。注意,两条语句必须独立查询,不能把语句组合在一起。因为组合在一起会有效率问题
select id,title,source from content where cid in(9,10)
select id,title,source from content where locate(',9,10,',othercid)>0)
我有两条查询,需要把结果组合。注意,两条语句必须独立查询,不能把语句组合在一起。因为组合在一起会有效率问题
select id,title,source from content where cid in(9,10)
select id,title,source from content where locate(',9,10,',othercid)>0)
select * from (
select id,title,source from content where cid in(9,10)) a,
(select id,title,source from content where locate(',9,10,',othercid)>0)) b
BEGIN
select id,title,source from content where cid in(9,10)
select id,title,source from content where locate(',9,10,',othercid)>0)
END在存储过程中执行两次select,但是这样做是不正确的
DROP PROCEDURE IF EXISTS `pro1`$$
CREATE PROCEDURE `pro1`(IN param VARCHAR(25) CHARSET 'gb2312')
BEGIN
SELECT id,title,source FROM content WHERE cid IN(9,10);
SELECT id,title,source FROM content WHERE LOCATE(',9,10,',othercid)>0;
END$$
DELIMITER ;
union
select id,title,source from content where locate(',9,10,',othercid)>0)
DROP PROCEDURE IF EXISTS `pro1`$$
CREATE PROCEDURE `pro1`(IN param VARCHAR(25) CHARSET 'gb2312')
BEGIN
SELECT id,title,source FROM content WHERE cid IN(9,10)
UNION
SELECT id,title,source FROM content WHERE LOCATE(',9,10,',othercid)>0;
END$$
DELIMITER ;
select id,title,source from content where cid in(9,10)
union
select id,title,source from content where locate(',9,10,',othercid)>0)