计算平均值后，在同一个表中合并两行

I have data in my table named prices as below;

我的表格中的数据名称价格如下;

+--------------------------------------+-------+-------+--------+------------+
| ID                                   | E5    | E10   | DIESEL | DATE       |
+--------------------------------------+-------+-------+--------+------------+
| a1978958-a6c4-447b-a90c-3d189da0f831 | 1.289 | 1.269 |  1.059 | 07/22/2017 |
| a1978958-a6c4-447b-a90c-3d189da0f831 | 1.259 | 1.239 |  1.029 | 07/22/2017 |
+--------------------------------------+-------+-------+--------+------------+

I want to calculate average values of E5, E10, DIESEL. Then I want to merge two columns with ID and DATE inside same table. (Column name and order will remain same)

我想计算E5，E10，DIESEL的平均值。然后我想在同一个表中合并两个ID和DATE列。（列名和顺序将保持不变）

select ID, DATE, avg(E5) as E5, avg(E10) as E10, avg(DIESEL) as DIESEL from prices group by ID, DATE;

Then I inserted the following query.

然后我插入了以下查询。

select t1.ID, t2.ID, t1.DATE, t2.DATE from prices as t1 inner join prices as t2 on t1.ID = t2.ID and t1.DATE = t2.DATE;

I couldn't achieve merging two rows with two above-mentioned queries. Result will be the following figure in all row values.

我无法通过两个上述查询来合并两行。结果将是所有行值中的下图。

  +--------------------------------------+-------+-------+--------+------------+
    | ID                                   | E5    | E10   | DIESEL | DATE       |
    +--------------------------------------+-------+-------+--------+------------+
    | a1978958-a6c4-447b-a90c-3d189da0f831 | 1.274 | 1.254 |  1.044 | 07/22/2017 |
    +--------------------------------------+-------+-------+--------+------------+

Is there any idea how to do?

有什么想法怎么办？

Thanks in advance,

提前致谢，

2 个解决方案

#1

You seem to be asking for:

你似乎要求：

select ID, avg(E5) as E5, avg(E10) as E10, avg(DIESEL) as DIESEL
from prices
group by ID;

Does this do what you want?

这样做你想要的吗？

#2

is this the thing that you are looking for: kept the result on temp table, deleted the actual values, and inserted the avg values to the same table?

这是你要找的东西：将结果保存在临时表中，删除实际值，并将avg值插入到同一个表中？

SELECT ID, avg(E5) as E5, avg(E10) as E10, avg(DIESEL) as DIESEL, DATE INTO #TEMP from prices group by ID, DATE
DELETE FROM prices
INSERT INTO prices SELECT * FROM #TEMP
DROP TABLE #TEMP

#1