使用php在另一个表中存储mysql查询

This is my MySQL table feedback.

这是我的MySQL表反馈。

I want to calculate average of every column and store the result to table average as below structure using a PHP file.

我想计算每列的平均值,并使用PHP文件将结果存储到表平均值,如下面的结构。

I am using query SELECT AVG (waiting) FROM feedback to calculate average of waiting column. But I do not have any idea of how to put this query result to another table as the above structure.

我正在使用查询SELECT AVG(等待)FROM反馈来计算等待列的平均值。但我不知道如何将此查询结果作为上述结构放到另一个表中。

When using query INSERT INTO average (average) SELECT AVG (waiting) FROM feedback **it only updating the average column.

当使用查询INSERT INTO平均(平均)SELECT AVG(等待)FROM反馈**它只更新平均列。

Please help me with a sample code.

请帮我一个示例代码。

Thanks

3 个解决方案

#1

If understand you correctly you can do it like this

如果你理解正确,你可以这样做

INSERT INTO average (data, average)
SELECT 'waiting',      AVG (waiting)      FROM feedback UNION ALL
SELECT 'consultation', AVG (consultation) FROM feedback UNION ALL
SELECT 'preoperative', AVG (preoperative) FROM feedback 

Sample output:

|         DATA | AVERAGE |
--------------------------
|      waiting |       3 |
| consultation |       2 |
| preoperative |       3 |

Here is SQLFiddle demo

这是SQLFiddle演示

---

Now if you don't have lots of data in feedback table you can ditch average table and create a view with the same way. As a result instead of maintaining a table of averages they will be calculated on the fly when you select from it

现在,如果反馈表中没有大量数据,则可以抛弃平均表并以相同的方式创建视图。因此,当您从中选择时,它们将立即计算,而不是维护平均表

CREATE VIEW average AS 
SELECT 'waiting',   ROUND(AVG (waiting)) average FROM feedback UNION ALL
SELECT 'consultation', ROUND(AVG (consultation)) FROM feedback UNION ALL
SELECT 'preoperative', ROUND(AVG (preoperative)) FROM feedback

And use it

并使用它

SELECT * FROM average

Here is SQLFiddle demo

这是SQLFiddle演示

#2

To get average values for those three columns:

要获得这三列的平均值:

SELECT AVG(waiting),AVG(consultation),AVG(preoperative) FROM feedback;

To insert to average table, if column names are the same:

要插入平均表,如果列名相同:

INSERT INTO average(waiting,consultation,preoperative) 
SELECT AVG(waiting),AVG(consultation),AVG(preoperative) FROM feedback

#3

You can grab the result from the average of each column and then INSERT that into a separate database.

您可以从每列的平均值中获取结果,然后将其插入到单独的数据库中。

What you are doing now inserts it into the same column in the same database, not a different one, which is why it's just updating that column and not a different one.

你现在正在做什么将它插入同一个数据库中的同一列,而不是另一个,这就是为什么它只是更新该列而不是另一个列。

Example of an INSERT query:

INSERT查询的示例:

$query = "INSERT INTO tableName (columnName) VALUES ($averageOfColumn)";

Replace 'tableName' with the name of the new table you are connecting to.

将'tableName'替换为要连接的新表的名称。

Replace 'columnName' with the name of the column in the other database you are using.

将'columnName'替换为您正在使用的其他数据库中的列的名称。

Replace '$averageOfColumn' with your variable for the average of the waiting column.

将'$ averageOfColumn'替换为您的变量,用于等待列的平均值。

Does this help?

这有帮助吗?