MySQL用户定义函数存储查询结果

Goal: I'm trying to store the tables results of a query as a variable in a function. I'd like to be able to do more things in this procedure referencing these tables.

目标:我尝试将查询的表结果存储为函数中的变量。我希望能够在这个引用这些表的过程中做更多的事情。

I've got a scaffold function below:

下面我有一个支架功能:

DELIMITER $$
CREATE FUNCTION getRatio(treatmentA INT, treatmentB INT)
RETURNS FLOAT
BEGIN
    DECLARE answer FLOAT;
    DECLARE countA, countB, countABIntersection;

    # Store patients of treatment A
    SELECT DISTINCT patient
    INTO **Variable**
    FROM Treatment
    WHERE TreatmentID = treatmentA;

    # Store patients of treatment B
    SELECT DISTINCT patient
    INTO **Variable**
    FROM Treatment
    WHERE TreatmentID = treatmentB;

    # Get Intersection of treatment B

    Set answer = count(TreatmentA) * count(TreatmentB) * count(IntersectionTreatmentAB)

    RETURN answer;
END $$
DELIMITER ;

If anyone can help, it would be greatly appreciated. Thanks in advance.

如果有人能帮忙，我将不胜感激。提前谢谢。

1 个解决方案

#1

You only can store or return a value from a function which MySQL have datatypes.

只能存储或返回MySQL具有数据类型的函数的值。

In short if you want to store a result set to variable you have to have a table datatype, unfortunately MySQL doesn't have such.

简而言之，如果要将结果集存储为变量，就必须有表数据类型，不幸的是，MySQL没有这样的数据类型。

Solution: What you can do is, you can create temporary tables with respective columns for each result set to store(or may be in the same temp table in your case).

解决方案:您可以做的是，您可以创建一个临时表，每个结果集的每个结果集都可以存储(或者在您的例子中可能在同一个临时表中)。

Do all operations on the temp table and store the result to a variable(FLOAT in you case), return the variable.

在temp表上执行所有操作并将结果存储到一个变量(在您的例子中是FLOAT)，返回变量。

Let me know if that helps :)

如果有帮助，请告诉我

-Thanks

-谢谢

#1