SQL查询与AVG和GROUP BY(SQL query with avg and group by

我有一些问题写为MySQL的SQL查询。 我有以下结构的表：

mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 |  44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 |  44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 |  76.1485000000 |
| DA02959106 | 5.0000000000 |  44.4007000000 |
| DA02959106 | 4.0000000000 |  76.6485000000 |

我希望创建一个提取从下表中的信息查询：

id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc

查询的结果应该是这样的：

+------------+---------+---------+---------+---------+---------+---------+---------+
| id         | val_1   | val_2   | val_3   | val_4   | val_5   | val_6   | val_7   |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0       | 0       |
+------------+---------+---------+---------+---------+---------+---------+---------+

与更多的行对于每个唯一的“身份证”，当然。

我已经尝试过类似的一些疑问

SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;

这将返回正确的结果，但我必须与结果的“通行证”的其他可能的值展开（最多7）

我试图内AVG使用嵌套查询，但这并没有工作，因为我并没有弄清楚如何正确地将其限制到当前的“身份证”。

然后我创建的视图来表示每个查询关于“通” = 1，“通” = 2等，但对于大多数IDS关于“通”的最高值的结果是5。当使用JOIN查询来获得从最终结果意见我收到一个空的结果集，因为有些意见是空的/没有值的特定“ID”。

有任何想法吗？

Answer 1:

如果我知道你需要什么，试试这个：

SELECT id, pass, AVG(val) AS val_1 
FROM data_r1 
GROUP BY id, pass;

或者，如果你只想要一个行对每个ID，这样的：

SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 2) as val_2,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 3) as val_3,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 4) as val_4,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 5) as val_5,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 6) as val_6,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id

Answer 2:

据我了解，你想在每遍每个ID的平均值。 解决的办法是

SELECT id, pass, avg(value) FROM data_r1
GROUP BY id, pass;

文章来源: SQL query with avg and group by