CODEWITHSUNDEEP
mysqlsqldatabase
Jack
Jack

Reputation: 667

Recursively running a MySQL function

I have a function in MySQL that needs to be run about 50 times (not a set value) in a query. the inputs are currently stored in an array such as [1,2,3,4,5,6,7,8,9,10]

when executing the MySQL query individually it's working fine, please see below

column_name denotes the column it's getting the data for, in this case, it's a DOUBLE in the database

The second value in the MOD() function is the input I'm supplying MySQL from the aforementioned array

SELECT id, MOD(column_name, 4) AS mod_output
FROM table
HAVING mod_output > 10

To achieve the output I require* the following code works

SELECT id, MOD(column_name, 4) AS mod_output1, MOD(column_name, 5) AS mod_output2, MOD(column_name, 6) AS mod_output3
FROM table
HAVING mod_output1 > 10 AND mod_output2 > 10 AND mod_output3 > 10

However this obviously is extremely dirty, and when having not 3 inputs, but over 50, this will become highly inefficient.

Appart from calling over 50 individual querys, is there a better way to acchieve the same sort (see below) of output?

In escennce i need to supply MySQL with a list of values and have it run MOD() over all of them on a specified column.

The only data I need returned is the id's of the rows that match the MOD() functions output with the specified input (see value 2 of the MOD() function) where the output is less than 10

Please note, MOD() has been used as an example function, however, the final function required *should* be a drop in replacement

example table layout

id | column_name
1  | 0.234977
2  | 0.957739
3  | 2.499387
4  | 48.395777
5  | 9.943782
6  | -39.234894
7  | 23.49859
.....

(The title may be worded wrong, I'm not quite sure how else you'd explain what I'm trying to do here)

Upvotes: 2

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a join and derived table or temporary table:

SELECT n.n, t.id, MOD(t.column_name, n.n) AS mod_output
FROM table t CROSS JOIN
     (SELECT 4 as n UNION ALL SELECT 5 UNION ALL SELECT 6 . . .
     ) n
WHERE MOD(t.column_name, n.n) > 10;

If you want the results as columns, you can use conditional aggregation afterwards.

Upvotes: 2

Related Questions