求助:如何查这样的数据!!!难啊 求高人指点
一张表 content
字段 tid ,unitid,pa(int),fe(int)
具体 tid unitid pa fe
100 1 12 13
100 2 14 12
100 1 21 17
101 1 32 45
101 3 34 12
101 2 56 89
要求：按unitid分组 算出pa+fe的值
结果如这样 unitid zf
1 pa+pe的值
2 pa+pe的值
3 pa+pe的值
希望各位大哥帮忙 100分不吝啬
[解决办法]
select unitid,sum(pa+pe) as zf
from content
group by unitid
[解决办法]
select unitid,sum(isnull(pa,0)+isnull(pe,0)) as zf
from content
group by unitid
[解决办法]
--楼主的字段名是fe,还是pe?
select unitid,sum(isnull(pa,0)+isnull(fe,0)) as zf
from content
group by unitid
[解决办法]
select unitid,sum(pa+pe) as zf
from content
group by unitid
order by unitid
[解决办法]
--楼主的字段名是fe,还是pe?
select unitid,sum(isnull(pa,0)+isnull(fe,0)) as zf
from content
group by unitid
--或
select unitid,isnull(sum(pa),0) + isnull(sum(fe),0) as zf
from content
group by unitid
[解决办法]
select unitid,sum(pa+pe) zf
from content
group by unitid
[解决办法]
--创建测试环境
create table content(tid int,unitid int ,pa int,pe int)
--插入测试数据
insert content(tid,unitid,pa,pe)
select '100 ', '1 ', '12 ', '13 ' union all
select '100 ', '2 ', '14 ', '12 ' union all
select '100 ', '1 ', '21 ', '17 ' union all
select '101 ', '1 ', '32 ', '45 ' union all
select '101 ', '3 ', '34 ', '12 ' union all
select '101 ', '2 ', '56 ', '89 '
--求解过程
select unitid,sum(case tid when 100 then pa else pe end) as zf
from content
group by unitid
--删除测试环境
drop table content
/*--测试结果
unitid zf
----------- -----------
1 78
2 103
3 12
（所影响的行数为 3 行）
*/
[解决办法]
这样?
select unitid,sum(case when tid <=100 then pa else pe end) as zf
from content
group by unitid
[解决办法]
用sum(case tid when 100 then pa else pe end)判断就行了
[解决办法]
select unitid,pa+pe from content group by unitid
[解决办法]
来晚了
