MySQL函数从多个表中添加价格 [英] MySQL Function Add prices from multiple tables

问题描述

该数据库有几个表，tblPizza，然后是其表头的表tblCheese，tblMeat和tblVeggie.我想编写一个将使用VARCHAR参数的MySQL函数，在每个表中查找priceFactor，将它们加在一起，然后返回该十进制数.在谷歌搜索并搜索之后，我将其拼凑在一起.我在这里或在Google上找不到的结果都没有试图与我想做的答案足够接近.

The DB has a few tables, tblPizza and then tables for its toppings, tblCheese, tblMeat, and tblVeggie. I want to write a MySQL function that'll take VARCHAR params, look up the priceFactor in each table, add them together, and return that decimal. After Googling and searching here, I've cobbled together this. None of the results I've found here or on the Googles are trying to do close enough to what I want to do to be an answer.

DELIMITER $$

CREATE FUNCTION `dbname`.`calculatePrice` (cheese VARCHAR(50), meat VARCHAR(50), veg VARCHAR(50))
RETURNS DECIMAL
BEGIN
DECLARE price DECIMAL;
SET price = SUM(SELECT priceFactor AS cheesePrice UNION ALL SELECT priceFactor AS meatPrice FROM tblMeat WHERE meatName = meat UNION ALL SELECT priceFactor AS vegPrice FROM tblVeggie WHERE veggieName = veg) FROM tblCheese WHERE cheeseName = cheese);

RETURN price;
END

MySQL不喜欢SUM之后的SELECT，但是我不知道如何在没有子查询的情况下获得想要的东西.每个表中都有重复的列名，并且表之间没有关联以证明联接是正确的.

MySQL doesn't like the SELECT after SUM, but I don't know how to get what I want without subqueries. There's duplicate column names accross each table and no correlation between the tables to justify a JOIN.

结果将被踢回PHP，PHP将其与JSON一起传递给jQuery.

The result will be kicked back to PHP, which will JSON it along to jQuery.

谢谢！ 99％的时间，我都能在这里找到我真正需要的东西^ _ ^

Thanks! 99% of the time, I can find exactly what I need here ^_^

编辑以注意，可能有一个(但只有一个)参数可以为null.用户只需要选择两个比萨饼浇头，而不能选择其他任何一个.

Edited to note that it's possible one (but only one) of the params may be null. The user is only required to select two pizza toppings, but cannot select any less than that.

推荐答案

尝试一下，

SET price = 
(
    SELECT  SUM(x.Price) 
    FROM
    (
        SELECT priceFactor AS Price FROM tblCheese WHERE cheeseName = cheese
        UNION ALL 
        SELECT priceFactor AS Price FROM tblMeat WHERE meatName = meat 
        UNION ALL 
        SELECT priceFactor AS Price FROM tblVeggie WHERE veggieName = veg
    ) x
);

这篇关于MySQL函数从多个表中添加价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！