select distinct
a,
b,
c,
(select sum(g) from mytable where pk=t.pk and mytime='2005') as d,--今年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2005') as e,--去年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2005')/
(select sum(g) from mytable where pk=t.pk and mytime='2005') as f--今年销量 比 去年销量
from mytable t不会每次用求和函数时都得用一个select吧,那么用d或e作运算的时候数据库是不是做了不少重复运算
能不能换个方法解决
a,
b,
c,
(select sum(g) from mytable where pk=t.pk and mytime='2005') as d,--今年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2005') as e,--去年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2005')/
(select sum(g) from mytable where pk=t.pk and mytime='2005') as f--今年销量 比 去年销量
from mytable t不会每次用求和函数时都得用一个select吧,那么用d或e作运算的时候数据库是不是做了不少重复运算
能不能换个方法解决
select distinct
a,
b,
c,
(select sum(g) from mytable where pk=t.pk and mytime='2005') as d,--今年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2004') as e,--去年的销量,
(select sum(g) from mytable where pk=t.pk and mytime='2005')/
(select sum(g) from mytable where pk=t.pk and mytime='2004') as f--今年销量 比 去年销量
from mytable t
a,
b,
c,
sum( case when pk=t.pk and mytime='2005' then g else 0 end ) as d,--今年的销量,
sum( case when pk=t.pk and mytime='2004' then g else 0 end ) as e,--去年的销量,
sum( case when pk=t.pk and mytime='2005' then g else 0 end)/
sum( case when pk=t.pk and mytime='2004' then g else 0 end ) as f--今年销量 比 去年销量
from mytable t
sum( case when pk=t.pk and mytime='2005' then g else 0 end ) as d,--今年的销量,
中pk=t.pk 不知道有没有问题,好像前面的pk并没有指定是哪个表的?
sum( case when pk=t.pk and mytime='2005' then g else 0 end)/
sum( case when pk=t.pk and mytime='2004' then g else 0 end ) as f--今年销量 比 去年销量
中也重复计算了分子和分母吧?