SQL - AVG 和分组依据

Question

我有下表:

Date      |   Product   |   Price 
06-12-17  |    1.1      |   10
06-12-17  |    1.2      |   20
06-12-17  |    1.3      |   30
06-12-17  |    1.4      |   40
05-12-17  |    1.1      |   20
05-12-17  |    1.2      |   20
05-12-17  |    1.3      |   40
05-12-17  |    1.4      |   40

我很难在 SQL Server 中找到可以给我这个结果的查询:

Date      |   Product |   Price 
06-12-17  |    1      |   25
05-12-17  |    1      |   30

我想要每天每种产品的平均价格

产品从 1.1 到 24.4

Answer 1

如果你只需要 product 的剩余部分，cast 到 int 然后使用结果值和 date 聚合>.

select date, 
       cast(product as int) as product, 
       avg(price) as Price
from table1
group by date, cast(product as int)

结果:

date        product Price
--------------------------
05-12-17    1       30
06-12-17    1       25

DEMO

---

更新:

如果产品是 varchar 数据类型，则使用 cast 两次。

select date, 
       cast(cast(product as dec(3,1)) as int) as product, 
       avg(price) as Price
from table1
group by date, cast(cast(product as dec(3,1)) as int)

Varchar() Datatype DEMO