字段如何实现这样分类求和?(100)

胡鸣 · 2009-05-15

现有一个Access表，表如下：lb je白纸 100红纸 150黄纸 200铅笔 2钢笔 10园珠笔 5;;;;;现在想分别求出纸和笔的合计金额，用SQL语句如何写？

Supermay · 2009-05-15

Select Sum(je) From table Group By RightStr(lb,1)

胡鸣 · 2009-05-15

to Supermay：我上面的举例太简单了，实际上表中的“笔”或“纸”的位置是不固定的，前后中间都有，也就是说想求所有包含这两个关键字的记录。

zkktom · 2009-05-15

看来你只能再增加一个字段了否则好像很难实现。

znxia · 2009-05-15

select Sum( Case when PatIndex('%纸%',lb)>0 then je else 0 end) as A, Sum( Case when PatIndex('%笔%',lb)>0 then je else 0 end) as BFrom table

胡鸣 · 2009-05-21

来自：znxia, 时间：2009-5-15 8:53:04, ID：3958450 select Sum( Case when PatIndex('%纸%',lb)>0 then je else 0 end) as A, Sum( Case when PatIndex('%笔%',lb)>0 then je else 0 end) as BFrom table 语法错误

李翔鹏 · 2009-05-21

select Sum( IIF(INSTR('*纸*',lb)>0, je , 0 )) as A, Sum( IIF( INSTR('*笔*',lb)>0, je , 0 )) as BFrom table

WilliamGui · 2009-05-21

select f1, sum(je)from ( select t.*, (case when PatIndex('%纸%',lb)>0 then '纸' when PatIndex('%笔%',lb)>0 then '笔' else '其它' end) f1 From tablename t) agroup by f1

znxia · 2009-05-21

呵呵，忘了你说的是access，还以为是sql server.

勇者 · 2009-05-21

select '笔' as lb,sum(je) as je from tablename where lb like '%笔%'unionselect '纸' as lb,sum(je) as je from tablename where lb like '%纸%'

逍遥小鱼 · 2009-06-02

select sum(je) as 'bi' from tablename where lb like '%笔%'select sum(je) as 'zhi' from tablename where lb like '%纸%'

胡鸣 · 2009-09-22

多人接受答案了。

字段如何实现这样分类求和?(100)

胡鸣

Unregistered / Unconfirmed

Supermay

Unregistered / Unconfirmed

胡鸣

Unregistered / Unconfirmed

zkktom

Unregistered / Unconfirmed

znxia

Unregistered / Unconfirmed

胡鸣

Unregistered / Unconfirmed

李翔鹏

Unregistered / Unconfirmed

WilliamGui

Unregistered / Unconfirmed

znxia

Unregistered / Unconfirmed

勇者

Unregistered / Unconfirmed

逍遥小鱼

Unregistered / Unconfirmed

胡鸣

Unregistered / Unconfirmed

Similar threads