mysql - 为 MySQL 和 SQL Server 编写 SQL 查询

Question

我有一个如下结构的表

id name field1 field2 field3
1  aaa   20     30     40
2  aaa   40     50     60
3  bbb   40     50     60
4  aaa   75     55     60
5  bbb   40     50     60
6  bbb   40     50     60

我要的结果是

1 aaa (sum of field 1) (product of field 2) (average of field3)
2 bbb (sum of field 1) (product of field 2) (average of field3)

我不知道如何做到这一点。我试过了

select SUM(field1),PROD(field2),AVG(field3) from table GROUP BY name 

但它不起作用。我还需要了解如何在 sql server 2005 中执行此操作。

Answer 1

对于 SQL Server 你可以使用

SELECT SUM(field1),
       CASE
         WHEN COUNT(CASE
                      WHEN field2 = 0 THEN 1
                    END) > 0 THEN 0
         ELSE 1
       END * CASE COUNT(CASE WHEN SIGN(field2) = -1 THEN 1 END )%2
               WHEN 0 THEN 1
               ELSE -1
             END * EXP(SUM(LOG(ABS(NULLIF(field2, 0))))),
       AVG(field3)
FROM   T
GROUP  BY name