我有三条查询语句返回的结果都叫money假设(分别是money1,money2和money3)
我传入的参数为$p_id,$sort和$money
现在我想创建一个存储过程
满足 if($money1-$money2-$money3-$money>=0){
echo "1";
}
if($money1-$money2-$money>=0 && $money1-$money2-$money3-$money<0){
echo "2";
}
if($money1-$money2-$money<0){
echo "3";
}我的三条查询语句如下$sql_1="select money from budget where p_id='$p_id' and sort='$sort'";
$sql_2="select sum(money) as money from spend where p_id='$p_id' and sort='$sort'";
$sql_3="select sum(money) as money from expense where p_id='$p_id' and sort='$sort' and (status='1' or status='0')";sql_2,sql_3的结果有可能是NULL,做运算的时候是否需要if($money2==null){$money2=0;}强制置0?我虽然用程序实现了该功能,但是效率不高。希望大家给出一个存储过程版本
我传入的参数为$p_id,$sort和$money
现在我想创建一个存储过程
满足 if($money1-$money2-$money3-$money>=0){
echo "1";
}
if($money1-$money2-$money>=0 && $money1-$money2-$money3-$money<0){
echo "2";
}
if($money1-$money2-$money<0){
echo "3";
}我的三条查询语句如下$sql_1="select money from budget where p_id='$p_id' and sort='$sort'";
$sql_2="select sum(money) as money from spend where p_id='$p_id' and sort='$sort'";
$sql_3="select sum(money) as money from expense where p_id='$p_id' and sort='$sort' and (status='1' or status='0')";sql_2,sql_3的结果有可能是NULL,做运算的时候是否需要if($money2==null){$money2=0;}强制置0?我虽然用程序实现了该功能,但是效率不高。希望大家给出一个存储过程版本
$sql_2="select ifnull(sum(money),0) as money from spend where p_id='$p_id' and sort='$sort'";
$sql_3="select ifnull(sum(money),0) as money from expense where p_id='$p_id' and sort='$sort' and (status='1' or status='0')";
老大,我想建立一个存储过程,将这三个SQL的返回结果做四则运算,
输出的结果应该是1或2或3
我传入存储过程的参数为$p_id,$sort和$money我想使得这个存储过程满足以下条件
money1-money2-money3-money>=0时输出1
money1-money2-money>=0 && money1-money2-money3-money<0时输出2
money1-money2-money<0时输出3
CREATE PROCEDURE qq(ap_id CHAR(10),asort CHAR(10),amoney DECIMAL)
BEGIN
SELECT money INTO @m1 FROM budget WHERE p_id=ap_id AND sort=asort;
SELECT IFNULL(SUM(money),0) INTO @m2 FROM spend WHERE p_id=ap_id AND sort=asort;
SELECT IFNULL(SUM(money),0) INTO @m3 FROM expense WHERE p_id=ap_id AND sort=asort AND (`status`='1' OR `status`='0');
SET @aa=
CASE
WHEN @m1-@m2-@m3-amoney>=0 THEN 1
WHEN @m1-@m2-amoney<0 THEN 3
ELSE 2
END ;
SELECT @aa;
END$$
DELIMITER ;
case
when m1.money-m2.money-m3.money>=$money then 1
when m1.money-m2.money>=$money then 2
else 3
end
from
(select money from budget where p_id='$p_id' and sort='$sort') m1,
(select sum(money) as money from spend where p_id='$p_id' and sort='$sort') m2,
(select sum(money) as money from expense where p_id='$p_id' and sort='$sort' and (status='1' or status='0')) m3