计算除当前类型以外的所有其他类型

您可以执行一个查询来获取不同的类型,然后 LEFT JOIN

SELECT t1.type,
       SUM(t2.some_value) / COUNT(t2.type)
FROM ( SELECT DISTINCT type FROM temptable ) t1
LEFT JOIN temptable t2 ON ( t1.type <> t2.type )
GROUP BY t1.type

因为你只想要平均值,所以你可以替换线

FROM ( SELECT DISTINCT type FROM temptable ) t1

通过

FROM temptable t1

这里的出发点是在您的类型和您的表之间建立一个笛卡尔连接(假设您的表结构是:type(id,type),valueTable(id,type,id,some\ u value))

将(*)计数为t型的和, valueTable vt其中vt.type\u id!=t、 身份证

应该会成功的。

这能满足你的需要吗?

CASE 语句以避免被零除的错误如果有可能任何类型都不会返回,我也没有明确说明 type (为空)

SELECT

SUM(CASE WHEN TYPE <> 'Type1' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type1' THEN 1 ELSE 0 END) AS T1,

SUM(CASE WHEN TYPE <> 'Type2' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type2' THEN 1 ELSE 0 END) AS T2,

SUM(CASE WHEN TYPE <> 'Type3' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type3' THEN 1 ELSE 0 END) AS T3,

SUM(CASE WHEN TYPE <> 'Type4' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type4' THEN 1 ELSE 0 END) AS T4
FROM TEMPTABLE

SELECT type, avg(col_01)
FROM myTable
GROUP BY type

也应该在Sybase上工作:

SELECT
   SUM(some value) / SUM(CASE WHEN TYPE = 1 THEN 1 ELSE 0 END)
FROM TEMPTABLE
GROUP BY TYPE