解决方案 »
- 请教关于GridLookUpEdit的问题
- 大家帮估算下这样一个论坛值多少钱
- 如何让.aspx的url后面可以跟封号?
- Page.IsPostBack到底是什么东西?
- FCKeditor里输入内容超过数据库里设定的值时,点保存时怎么给出提示?
- 通过ODBC来调用SQL-SERVER的存储过程的问题!!!
- 寻团队合作伙伴
- 各位大牛:关于.net与word的问题。100分,分少再加
- e.item.itemindex是什么意思?谁能给我详细解释一下吗?
- radiobutton和gridview怎么一起使用?
- 登录页面添加.NET AD验证的方法
- QuickPager分页控件源码和Demo已经分离出来了!
你可以参考下这句话、看看能不能够明白..
部门就是你的table1中部门列 的 列名
(select sum(convert( float,分值1)) as 合计分值1 from table1 where 确定='true' )合计分值1,
(select sum(convert( float,分值2)) as 合计分值2 from table1 where 确定='true' and 是否='true' )合计分值2
from Table1 group by 部门
可是结果却不是我想要的
select 部门, sum(分值1) as '合计分值1',
(select sum(分值2) from Table1 b where b.部门=a.部门 and b.确定='true' and b.是否='true') as '合计分值2'
from Table1 a where 确定='true' group by 部门
(select 部门, sum(分值1)as 分值11 from Table_1 where [确定]='true' group by 部门)as aa,
(select 部门, sum(分值2)as 分值22 from Table_1 where [确定]='true' and [是否]='true' group by 部门)as bb
where aa.部门=bb.部门
(select sum(分值2) from Table1 b where b.部门=a.部门 and b.确定=1 and b.是否=1) as '合计分值2'
from Table1 a where 确定=1 group by 部门
(select 部门, sum(分值1)as 分值11 from Table_1 where [确定]='true' group by 部门)as aa,
(select 部门, sum(分值2)as 分值22 from Table_1 where [确定]='true' and [是否]='true' group by 部门)as bbwhere aa.部门=bb.部门
这个是否可称之为临时表?
(select sum(分值2) from Table1 b where b.部门=a.部门 and b.确定='true' and b.是否='true') as '合计分值2'
from Table1 a where 确定='true' group by 部门
如果我再加一列合计分值3,条件为[确定]='true' and [是否]='true' and [NN]='true'
这个如何再往上加呢?
select aa.部门, 分值11,分值22,分值33 from
(select 部门, sum(convert( float,分值1))as 分值11 from Table1 where [确定]='true' group by 部门)as aa,
(select 部门, sum(convert( float,分值2))as 分值22 from Table1 where [确定]='true' and [是否]='true' group by 部门)as bb
(select 部门, sum(convert( float,分值2))as 分值33 from Table1 where [确定]='true' and [是否]='true'and [NN]='true' group by 部门)as cc
where aa.部门=bb.部门 and bb.部门=cc.部门
这样加的话就会丢失两个部门,难道没有count(*)不符合条件的为什么不等于0,而是没有这条记录?
我抽了下时间帮你写了下.记得个分.
很简单的.
select 部门,
sum(case 确定 when 'True' then 分值1 else 0 end) as True分值1,
sum(case 确定 when 'True' then 分值2 else 0 end) as True分值2
from table8 group by table8.部门
sum(case when 确定= 'True' then 分值1 else 0 end) as True分值1,
sum(case when 确定 ='True' and 是否='True' then 分值2 else 0 end) as True分值2
from table1 group by table1.部门
sum(case 确定 when 1 then 分值1 else 0 end) as True合计分值1,
sum(case 确定 when 1 then (case 是否 when 1 then 分值2 else 0 end) else 0 end) as True合计分值2
from table1 group by 部门 order by 部门
(select 部门, sum(convert( float,分值1))as 分值11 from Table1 where [确定]='true' group by all 部门)as aa,
(select 部门, sum(convert( float,分值2))as 分值22 from Table1 where [确定]='true' and [是否]='true' group by all 部门)as bb
(select 部门, sum(convert( float,分值2))as 分值33 from Table1 where [确定]='true' and [是否]='true'and [NN]='true' group by all 部门)as cc
where aa.部门=bb.部门 and bb.部门=cc.部门
加了个all就好了
效率不如这句吧select 部门,
sum(case 确定 when 'True' then 分值1 else 0 end) as True分值1,
sum(case 确定 when 'True' then 分值2 else 0 end) as True分值2
from table8 group by table8.部门
------------------------------------------select 部门,
sum(case when 确定=1 then 分值1 else 0 end) as True合计分值1,
sum(case when 确定=1 and 是否=1 then 分值2 else 0 end) as True合计分值2
from table1 group by 部门 运行结果:
isNull(sum(case 确定 when 'true' then 分值1 else 0 end)) as 合计分值1,
isNull(sum(case when 确定='true' and 是否=‘true’ then 分值2 else 0 end )) as 合计分值2
from table1 group by dept
应该这样吧!
Case
When 确定=1 Then 分值1
Else 0
End
) As 分值1,sum(
Case
When 是否=1 Then 分值2
Else 0
End
) As 分值2 From Table_1
Group By 部门