select spec ,sum(case type when 's' then pcs else 0 end ) as s ..... from table group by spec
type 类型固定还是动态如果动态用存储过程可以实现,如果静态用case语句就可以实现
to sky_blue: 是固定的,但是比较多!有20多种。且每种产品都有相同的规格,即每种产品都有100,120,135,150……等规格。请给出详细的语句好吗?thanks!
select spec, S.pcs, V.pcs, R.pcs ... from (select spec, sum(pcs) pcs from b_stock group by spec where type = 'S') as S full join (select spec, sum(pcs) pcs from b_stock group by spec where type = 'V') as V on S.spec = V.spec full join (select spec, sum(pcs) pcs from b_stock group by spec where type = 'R') as R on S.spec = R.spec full join ......
固定的就按照zqllyh(找感觉) 这么做,用视图也可以select spec ,sum(case type when 'S' then pcs else 0 end ) as S, sum(case type when 'V' then pcs else 0 end ) as V, sum(case type when 'T' then pcs else 0 end ) as T, ..... from yourtable group by spec语句长点没有关系,呵呵。
谢谢各位! 我按上面的语句: select spec ,sum(case type when 'S' then pcs else 0 end ) as S, sum(case type when 'V' then pcs else 0 end ) as V, sum(case type when 'T' then pcs else 0 end ) as T, ..... from yourtable group by spec 在分析器里运行没问题,可以得到正确结果。 可在新建视图里进行SQL语句检查时出现提示,"查询设计器不支持 CASE SQL 构造",强制运行也可以,但是保存视图时,出现"视图定义中没有输出列或FROM子句中没有项目"提示保存不了。 还请各位指教!
from table group by spec
是固定的,但是比较多!有20多种。且每种产品都有相同的规格,即每种产品都有100,120,135,150……等规格。请给出详细的语句好吗?thanks!
(select spec, sum(pcs) pcs from b_stock group by spec where type = 'S') as S full join
(select spec, sum(pcs) pcs from b_stock group by spec where type = 'V') as V on S.spec = V.spec full join
(select spec, sum(pcs) pcs from b_stock group by spec where type = 'R') as R on S.spec = R.spec full join
......
sum(case type when 'V' then pcs else 0 end ) as V,
sum(case type when 'T' then pcs else 0 end ) as T,
.....
from yourtable group by spec语句长点没有关系,呵呵。
我按上面的语句:
select spec ,sum(case type when 'S' then pcs else 0 end ) as S,
sum(case type when 'V' then pcs else 0 end ) as V,
sum(case type when 'T' then pcs else 0 end ) as T,
.....
from yourtable group by spec
在分析器里运行没问题,可以得到正确结果。
可在新建视图里进行SQL语句检查时出现提示,"查询设计器不支持 CASE SQL 构造",强制运行也可以,但是保存视图时,出现"视图定义中没有输出列或FROM子句中没有项目"提示保存不了。
还请各位指教!