--那不就是这样就行了吗? select * ,[+]=case isnull([1],'') when '+' then 1 else 0 end +case isnull([2],'') when '+' then 1 else 0 end +case isnull([3],'') when '+' then 1 else 0 end ....... +case isnull([12],'') when '+' then 1 else 0 end ,[-]=case isnull([1],'') when '-' then 1 else 0 end +case isnull([2],'') when '-' then 1 else 0 end +case isnull([3],'') when '-' then 1 else 0 end ....... +case isnull([12],'') when '-' then 1 else 0 end from 表 union all select '合计' ,[1]=sum(case isnull([1],'') when '+' then 1 when '-' then -1 else 0 end) ,[2]=sum(case isnull([2],'') when '+' then 1 when '-' then -1 else 0 end) ,[3]=sum(case isnull([3],'') when '+' then 1 when '-' then -1 else 0 end) .... ,[12]=sum(case isnull([12],'') when '+' then 1 when '-' then -1 else 0 end) ,[+]=sum( case isnull([1],'') when '+' then 1 else 0 end +case isnull([2],'') when '+' then 1 else 0 end +case isnull([3],'') when '+' then 1 else 0 end ....... +case isnull([12],'') when '+' then 1 else 0 end) ,[-]=sum( case isnull([1],'') when '-' then 1 else 0 end +case isnull([2],'') when '-' then 1 else 0 end +case isnull([3],'') when '-' then 1 else 0 end ....... +case isnull([12],'') when '-' then 1 else 0 end) from 表
-- select *, len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))as '+' , len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))as '-' from tn union all select '合计',cast(sum(case when [1]='+' then 1 end)as varchar(2))as [1] , cast(sum(case when [2]='+' then 1 else 0 end) as varchar(2)) as [2], cast(sum(case when [3]='+' then 1 else 0 end)as varchar(2)) as [3], cast(sum(case when [4]='+' then 1 else 0 end)as varchar(2))as [4], cast(sum(case when [5]='+' then 1 else 0 end)as varchar(2))as [5], cast(sum(case when [6]='+' then 1 else 0 end)as varchar(2))as [6], cast(sum(case when [7]='+' then 1 else 0 end)as varchar(2))as [7], cast(sum(case when [8]='+' then 1 else 0 end)as varchar(2))as [8], cast(sum(case when [9]='+' then 1 else 0 end)as varchar(2))as [9], cast(sum(case when [10]='+'then 1 else 0 end)as varchar(2))as [10], cast(sum(case when [11]='+' then 1 else 0 end)as varchar(2))as [11], cast(sum(case when [12]='+' then 1 else 0 end)as varchar(2))as [12], (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) , (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn) from tn
select '合计',cast(sum(case when [1]='+' then 1 end)as varchar(2))as [1] , ---------------------------- 这个改一下: select '合计',cast(sum(case when [1]='+' then 1 else 0 end)as varchar(2))as [1] ,
--最后一行的查询: select '合计',cast(sum(case when [1]='+' then 1 else 0 end)as varchar(2))as [1] , cast(sum(case when [2]='+' then 1 else 0 end) as varchar(2)) as [2], cast(sum(case when [3]='+' then 1 else 0 end)as varchar(2)) as [3], cast(sum(case when [4]='+' then 1 else 0 end)as varchar(2))as [4], cast(sum(case when [5]='+' then 1 else 0 end)as varchar(2))as [5], cast(sum(case when [6]='+' then 1 else 0 end)as varchar(2))as [6], cast(sum(case when [7]='+' then 1 else 0 end)as varchar(2))as [7], cast(sum(case when [8]='+' then 1 else 0 end)as varchar(2))as [8], cast(sum(case when [9]='+' then 1 else 0 end)as varchar(2))as [9], cast(sum(case when [10]='+'then 1 else 0 end)as varchar(2))as [10], cast(sum(case when [11]='+' then 1 else 0 end)as varchar(2))as [11], cast(sum(case when [12]='+' then 1 else 0 end)as varchar(2))as [12], (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) , (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn) from tn--不知道还有没有简单的
--还可以更简单,不过不是你要求的简单,呵呵 select '合计' as 合计 ,sum(case when [1]='+' then 1 else 0 end)as [1] , sum(case when [2]='+' then 1 else 0 end )as [2], sum(case when [3]='+' then 1 else 0 end )as [3], sum(case when [4]='+' then 1 else 0 end)as [4], sum(case when [5]='+' then 1 else 0 end)as [5], sum(case when [6]='+' then 1 else 0 end)as [6], sum(case when [7]='+' then 1 else 0 end)as [7], sum(case when [8]='+' then 1 else 0 end)as [8], sum(case when [9]='+' then 1 else 0 end)as [9], sum(case when [10]='+'then 1 else 0 end)as [10], sum(case when [11]='+' then 1 else 0 end)as [11], sum(case when [12]='+' then 1 else 0 end)as [12], (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) as '+' , (select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn) as '-' from tn
select *
,[+]=case isnull([1],'') when '+' then 1 else 0 end
+case isnull([2],'') when '+' then 1 else 0 end
+case isnull([3],'') when '+' then 1 else 0 end
.......
+case isnull([12],'') when '+' then 1 else 0 end
,[-]=case isnull([1],'') when '-' then 1 else 0 end
+case isnull([2],'') when '-' then 1 else 0 end
+case isnull([3],'') when '-' then 1 else 0 end
.......
+case isnull([12],'') when '-' then 1 else 0 end
from 表
union all
select '合计'
,[1]=sum(case isnull([1],'')
when '+' then 1
when '-' then -1
else 0 end)
,[2]=sum(case isnull([2],'')
when '+' then 1
when '-' then -1
else 0 end)
,[3]=sum(case isnull([3],'')
when '+' then 1
when '-' then -1
else 0 end)
....
,[12]=sum(case isnull([12],'')
when '+' then 1
when '-' then -1
else 0 end)
,[+]=sum(
case isnull([1],'') when '+' then 1 else 0 end
+case isnull([2],'') when '+' then 1 else 0 end
+case isnull([3],'') when '+' then 1 else 0 end
.......
+case isnull([12],'') when '+' then 1 else 0 end)
,[-]=sum(
case isnull([1],'') when '-' then 1 else 0 end
+case isnull([2],'') when '-' then 1 else 0 end
+case isnull([3],'') when '-' then 1 else 0 end
.......
+case isnull([12],'') when '-' then 1 else 0 end)
from 表
select *,
len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))as '+' ,
len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))as '-'
from tn
union all
select '合计',cast(sum(case when [1]='+' then 1 end)as varchar(2))as [1] ,
cast(sum(case when [2]='+' then 1 else 0 end) as varchar(2)) as [2],
cast(sum(case when [3]='+' then 1 else 0 end)as varchar(2)) as [3],
cast(sum(case when [4]='+' then 1 else 0 end)as varchar(2))as [4],
cast(sum(case when [5]='+' then 1 else 0 end)as varchar(2))as [5],
cast(sum(case when [6]='+' then 1 else 0 end)as varchar(2))as [6],
cast(sum(case when [7]='+' then 1 else 0 end)as varchar(2))as [7],
cast(sum(case when [8]='+' then 1 else 0 end)as varchar(2))as [8],
cast(sum(case when [9]='+' then 1 else 0 end)as varchar(2))as [9],
cast(sum(case when [10]='+'then 1 else 0 end)as varchar(2))as [10],
cast(sum(case when [11]='+' then 1 else 0 end)as varchar(2))as [11],
cast(sum(case when [12]='+' then 1 else 0 end)as varchar(2))as [12],
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) ,
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn)
from
tn
----------------------------
这个改一下:
select '合计',cast(sum(case when [1]='+' then 1 else 0 end)as varchar(2))as [1] ,
select '合计',cast(sum(case when [1]='+' then 1 else 0 end)as varchar(2))as [1] ,
cast(sum(case when [2]='+' then 1 else 0 end) as varchar(2)) as [2],
cast(sum(case when [3]='+' then 1 else 0 end)as varchar(2)) as [3],
cast(sum(case when [4]='+' then 1 else 0 end)as varchar(2))as [4],
cast(sum(case when [5]='+' then 1 else 0 end)as varchar(2))as [5],
cast(sum(case when [6]='+' then 1 else 0 end)as varchar(2))as [6],
cast(sum(case when [7]='+' then 1 else 0 end)as varchar(2))as [7],
cast(sum(case when [8]='+' then 1 else 0 end)as varchar(2))as [8],
cast(sum(case when [9]='+' then 1 else 0 end)as varchar(2))as [9],
cast(sum(case when [10]='+'then 1 else 0 end)as varchar(2))as [10],
cast(sum(case when [11]='+' then 1 else 0 end)as varchar(2))as [11],
cast(sum(case when [12]='+' then 1 else 0 end)as varchar(2))as [12],
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) ,
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn)
from
tn--不知道还有没有简单的
select '合计' as 合计 ,sum(case when [1]='+' then 1 else 0 end)as [1] ,
sum(case when [2]='+' then 1 else 0 end )as [2],
sum(case when [3]='+' then 1 else 0 end )as [3],
sum(case when [4]='+' then 1 else 0 end)as [4],
sum(case when [5]='+' then 1 else 0 end)as [5],
sum(case when [6]='+' then 1 else 0 end)as [6],
sum(case when [7]='+' then 1 else 0 end)as [7],
sum(case when [8]='+' then 1 else 0 end)as [8],
sum(case when [9]='+' then 1 else 0 end)as [9],
sum(case when [10]='+'then 1 else 0 end)as [10],
sum(case when [11]='+' then 1 else 0 end)as [11],
sum(case when [12]='+' then 1 else 0 end)as [12],
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'-',''))) from tn) as '+' ,
(select sum(len(replace([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12],'+',''))) from tn) as '-'
from
tn