是否可能将表中的每个记录与查询中的其他记录的平均值进行比较

For a table in MySQL I have two columns

对于MySQL中的表，我有两列

col1   col2
  1     4.5
  1     4.8
  1     4.4
  1     3.9
  1     7.6
  1     4.5
  2     2.9
  2     4.8
  2     5.6
  2     4.5
  3     8.9
  3     9.7
  3     7.8
  3     8.2
  3     4.3

what I want to do is

我想做的是!

calculate the average values of col2 for different values of col1 and this is easy
计算col1不同值的col2的平均值，这很简单。

SELECT col1,AVG(col2) FROM mytable GROUP BY col1

通过col1从mytable组中选择col1、AVG(col2)

1   4.95
2   4.45
3   7.78

2. select the record if its col2 value is less than average of col2 values where col1 is equal to col1-1. I couldn't figure out this part:

2。如果col2值小于col1 = col1-1时col2值的平均值，则选择该记录。我不知道这部分:

 SELECT col1,col2 FROM mytable WHERE col2<AVG(col2 where col1= current col1-1)

The result set should be:

结果集应该是:

Since there is no records where col1=0, there will be no record for col1=1

因为没有col1=0的记录，所以不会有col1=1的记录

2 个解决方案

#1

Having clause and self-join allows you to do this

有子句和自连接允许您这样做

Select a.col1, a.col2, 
   Avg(b.Col2) AvgCol2
From yourTable a
   Join yourTable b 
       On b.col1 = a.col1 - 1     
Group By a.col1, a.col2
Having a.col2 < Avg(b.Col2)

For your sample data, for e.g., it returns

对于你的样本数据，例如，它返回

col1     col2     AvgCol2
-------- -------- --------
2        2.9      4.95
3        4.3      4.45
2        4.5      4.95
2        4.8      4.95

Explanation: One way to think about this is that Having is clause for filtering the result set constructed AFTER Aggregation in a Group By query, whereas Where clause is filter for temporary resultset constructed just BEFORE Aggregation, (or before Sorting in a non Group By Query).

说明:考虑这个问题的一种方法是使用is子句来过滤组中通过查询在聚合之后构造的结果集，而Where子句是过滤在聚合之前构造的临时结果集(或在通过查询对非组进行排序之前)。

#2

Try this:

试试这个:

SELECT t1.*
FROM @yourtable AS t1
INNER JOIN
(
   SELECT col1, AVG(col2) col2avg
   FROM @yourtable
   GROUP BY col1
) AS t2  ON t1.col1 - 1 = t2.col1
        AND t1.col2 < t2.col2avg;

#1