使用avg和group by的SQL查询 [英] SQL query with avg and group by
问题描述
在为MySQL编写SQL查询时遇到一些问题。我有一个具有以下结构的表：
mysql>从data_r1中选择id，pass，val limit 10;
+ ------------ + -------------- + ---------------- +
| id |传递| 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，'val'为'pass'= 1的AVG，'val'为'pass'= 2的AVG等
查询的结果应该如下所示：
+ ------------ + --------- + --------- + -------- - + --------- + --------- + --------- + --------- +
| 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 |
+ ------------ + --------- + --------- + --------- + - ------- + --------- + --------- + --------- +
当然，每个唯一的'id'有更多的行。
我已经尝试了一些查询，比如
pre $ SELECT $ id，pass， AVG（val）AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;
这会返回正确的结果，但我必须将结果扩展为其他可能的值'通过'（最多7）
我试图在AVG中使用嵌套的SELECT，但这不起作用，因为我没有弄清楚如何正确地限制它当前的'id'。
然后我创建了Views来表示每个查询的结果，例如'pass'= 1，'pass'= 2等等。但是对于大多数ids'pass'的最高值是5.当使用JOIN查询从视图中获得最终结果时，我收到一个空的结果集，因为某些视图是空的/没有特定'id'的值。
有什么想法？
如果我明白你需要什么，试试这个： SELECT id，pass，AVG（val）AS val_1
FROM data_r1
GROUP BY身份证，通行证;
或者，如果您只想为每个ID添加一行，请执行以下操作：
SELECT d1.id，
（SELECT IFNULL（ROUND（AVG（d2.val），4），0）FROM data_r1 d2 $ b $ （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通过= 3）作为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 SELECT IFNULL（ROUND（AVG（d2.val），4），0）FROM data_r1 d2
WHERE d2.id = d1.id AND pass = 6）as val_6，
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
GROU P BY d1.id
I have some problems with writing a SQL query for MySQL. I have a table with the following structure:
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 |
I want to create a query that extracts the following information from the table:
id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc
The result of the query should look like this:
+------------+---------+---------+---------+---------+---------+---------+---------+
| 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 |
+------------+---------+---------+---------+---------+---------+---------+---------+
with more rows for each unique 'id', of course.
I already tried some queries like
SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;
This returns the correct result, but I have to expand it with results for the other possible values of 'pass' (up to 7)
I tried to use a nested SELECT within AVG but this didn't work because I didn't figure out how to correctly limit it to the current 'id'.
I then created Views to represent the result of each query for 'pass' = 1, 'pass' = 2, etc. But for most ids the highest value for 'pass' is 5. When using JOIN queries to get the final result from the views I received an empty result set, because some of the Views are empty / don't have values for a specific 'id'.
Any ideas?
If I understand what you need, try this:
SELECT id, pass, AVG(val) AS val_1
FROM data_r1
GROUP BY id, pass;
Or, if you want just one row for every id, this:
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
这篇关于使用avg和group by的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
