MySQL: сделать вычисляемый столбец из нескольких таблиц со сложным уравнением
Я написал формулу в PHP, которая использует поля из разных таблиц для расчета процента. То, что я хочу попробовать сделать, это перевести его на SQL, если это возможно.
Я понимаю, что могу использовать SET для создания локальных переменных, затем ВЫБРАТЬ эту переменную для отображения в моей финальной таблице, но установка этих переменных в качестве уравнений доставляет мне некоторые хлопоты.
Вот PHP. Я старался сделать это как можно проще, не путая.
/*Setting fields to variables for ease*/
$PU = {table1.PU}*60;
$PD = {table1.PD};
$UD = {table3.UD};
$GP = {table1.GP};
$S = {table2.S};
$R = {table4.R};
$PR = {table1.CT}/60;
/*Combining variables*/
$TRT = $PU - $PD - $UD;
$PT = $TRT * $PR;
$TP = $GP + $S + $R;/*Checking and correcting for zero division plus final calculation*/
if ($PU == 0 || $PT == 0 || $TP == 0){
{complete} = "Error!";
}
else {
$a = $TRT/$PU;
$b = $TP/$PT;
$c = $GP/$TP;
{complete} = 100 * ($a * $b * $c);
}
И MySQL, который я начал. Мой план атаки состоял в том, чтобы назначить формулы переменным, используя имена столбцов, включая проверку на нулевое деление. Выполнение этого через SQL Maestro, выдающее ошибку на недопустимом токене SET. Я попытался обернуть все это в двойные кавычки (и проверил переменную @variable $, =,: = все без удачи) и проверил несколько источников, моя команда SET должна быть правильной. Мои закрытые ключи и внешние ключи установлены правильно.
SET @a = (((table1.PU*60) - table1.PD - table3.UD) / (table1.PU*60));
SET @b = (table1.GP + table2.S + table4.R)/(((table1.PU*60) - table1.PD - table3.UPD)*(table1.CT/60));
SET @c = table1.GP/(table1.GP + table2.S + table4.R);
SET @complete = 100*(@a*@b*@c);
IF(table1.PU = 0, 'Error!',@a),
IF(table1.CT = 0, 'Error!',@p),
SELECT
table1.PU,
table1.PD,
table1.CT,
table2.S,
table3.UD,
table4.R,
@complete
FROM
table1
LEFT JOIN table2
LEFT JOIN table3
LEFT JOIN table4
Окончательный результат, который я ищу, состоит в том, чтобы отобразить все отдельные поля, которые я выбираю, а также вычисленное поле. Я посмотрел в ПРОЦЕДУРУ, и я думаю, что это может быть полезно, но я не уверен, как включить его полностью.
Решение
Вам не нужно SET для этого. Вот простой SELECT, который делает все вычисления:
SELECT
table1.PU * 60 AS PU,
table1.PD AS PD,
table3.UPD AS UPD,
table1.GP AS GP,
table2.S AS S,
table4.R AS R,
table1.CT/60 AS PR,
(table1.PU * 60) - table1.PD - table3.UPD AS TRT,
(table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60) AS PT,
table1.GP + table2.S + table4.R AS TP,
((table1.PU * 60) - table1.PD - table3.UPD) / (table1.PU * 60) AS a,
(table1.GP + table2.S + table4.R) / ((table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60)) AS b,
table1.GP / (table1.GP + table2.S + table4.R) AS c,
100 * (((table1.PU * 60) - table1.PD - table3.UPD) / (table1.PU * 60)) * ((table1.GP + table2.S + table4.R) / ((table1.PU * 60 - table1.PD - table3.UPD) * (table1.CT/60))) * (table1.GP / (table1.GP + table2.S + table4.R)) AS complete
FROM
table1,
table2,
table3,
table4
Предполагая, что все ваши таблицы содержат только одну строку. В противном случае вам нужно указать некоторые условия JOIN.
Другие решения
Я думаю, что SET работает только с запросами выбора: Как установить переменную из запроса SQL?
Кроме того, я предположил, что таблица 1-4 содержит только одну строку. В противном случае SET не работает.
SET @a = (SELECT
(((table1.PU*60) - table1.PD - table3.UPD) / (table1.PU*60))
FROM table1, table3);
SET @b = (SELECT
(table1.GP + table2.S + table4.R)/(((table1.PU*60) - table1.PD - table3.UPD)*(table1.CT/60))
FROM table1, table2, table3, table4);
SET @c = (SELECT
table1.GP/(table1.GP + table2.S + table4.R)
FROM table1, table2, table4);
SET @complete = 100*(@a*@b*@c);
IF(table1.PU = 0, 'Error!',@a),
IF(table1.CT = 0, 'Error!',@p),
SELECT
table1.PU,
table1.PD,
table1.CT,
table2.S,
table3.UPD,
table4.R,
@complete // or perhaps (SELECT @complete as complete)
FROM
table1, table2, table3, table4