只会做到这步
create table huizong
(A int,B varchar(10),c int)
insert into huizong
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京', 5 union
select 1,'上海', 14 union
select 2,'上海', 12select a.B 单位,
[应收]=sum(case a.A when 1 then a.c end),
[实收]=sum(case a.A when 2 then a.c end)
from huizong a
group by a.a,a.b
drop table huizong单位 应收 实收
---------- ----------- -----------
北京 15 NULL
北京 NULL 25
上海 14 NULL
上海 NULL 12
西安 10 NULL
西安 NULL 20
create table huizong
(A int,B varchar(10),c int)
insert into huizong
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京', 5 union
select 1,'上海', 14 union
select 2,'上海', 12select a.B 单位,
[应收]=sum(case a.A when 1 then a.c end),
[实收]=sum(case a.A when 2 then a.c end)
from huizong a
group by a.a,a.b
drop table huizong单位 应收 实收
---------- ----------- -----------
北京 15 NULL
北京 NULL 25
上海 14 NULL
上海 NULL 12
西安 10 NULL
西安 NULL 20
case a.a when 1 then a.c else 0 end as '应收',
case a.a when 2 then a.c else 0 end as '实收',
isnull((select sum(b.c)-sum(c.c) from tab1 b,tab1 c where b.a=1 and c.a=2 and b.b=c.b and b.b=a.b),'') from tab1 a
insert @tb
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京',5 union
select 1,'上海',14 union
select 2,'上海',12
select *
from
(
select [单位]=A.B,
[应收]=(case when A.A=1 then Convert(varchar(8),A.C) else '' end),
[实收]=(case when A.A=2 then Convert(varchar(8),A.C) else '' end),
[相差]=''
from @tb a
union
select '小计',
convert(varchar(8),sum((case when A=1 then C else 0 end))),
convert(varchar(8),sum((case when A=2 then C else 0 end))),
convert(varchar(8),sum((case when A=1 then C else 0 end))-sum((case when A=2 then C else 0 end)))
from @tb group by B
)T --结果
/*
单位 应收 实收 相差
---------- -------- -------- --------
北京 25
北京 10
北京 5
上海 12
上海 14
西安 20
西安 10
小计 10 20 -10
小计 14 12 2
小计 15 25 -10
*/
[应收]=sum(case a.A when 1 then a.c end),
[实收]=sum(case a.A when 2 then a.c end)
from huizong a
group by a.a,a.b with rollup
( a int,
b varchar(10),
c int)
declare @a table
( id int identity(1,1),
dw varchar(10),
ys int,
ss int,
xc int)
insert @t
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京', 5 union
select 1,'上海', 14 union
select 2,'上海', 12insert @a
select b [单位],
case a when 1 then c else null end [应收],
case a when 2 then c else null end [实收],
null [相差]
from @t
union
select b+'小计' [dw],
sum(case a when 1 then c else 0 end) [ys],
sum(case a when 2 then c else 0 end) [ss],
sum(case a when 1 then c else 0 end) - sum(case a when 2 then c else 0 end) [xc]
from @t
group by b
insert @a
select '合计',
sum(case a when 1 then c else 0 end),
sum(case a when 2 then c else 0 end),
sum(case a when 1 then c else 0 end) - sum(case a when 2 then c else 0 end)
from @tselect * from @a这样写出来顺序是对的,如果只用select 顺序不对
[应收]=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end),
[实收]=sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end),
相差=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end)-sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end)
from QQLL
group by b,A with ROLLUP
[应收]=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end),
[实收]=sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end),
相差=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end)-sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end)
from QQLL
group by b,A with ROLLUP
create table huizong
(A int,B varchar(10),c int)
insert into huizong
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京',5 union
select 1,'上海',14 union
select 2,'上海',12
--查询
(select [单位]=A.B,
[应收]=(case when A.A=1 then A.C else null end),
[实收]=(case when A.A=2 then A.C else null end),
[相差]=null
from huizong a)
union
(select 单位+'小计', isnull(sum(应收), 0),isnull(sum(实收), 0)
,相差=sum(isnull(应收,0)-isnull(实收,0))
from (select [单位]=A.B,
[应收]=(case when A.A=1 then A.C else null end),
[实收]=(case when A.A=2 then A.C else null end)
from huizong a)tt
group by tt.单位
)
--清除
drop table huizong
[应收]=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end),
[实收]=sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end),
相差=CASE WHEN grouping(A)=1 THEN sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end)-sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end) ELSE 0 END
from QQLL
group by b,A with ROLLUP
insert @tb
select 1,'西安',10 union
select 2,'西安',20 union
select 1,'北京',10 union
select 2,'北京',25 union
select 1,'北京',5 union
select 1,'上海',14 union
select 2,'上海',12
select *
from
(
select [单位]=A.B,
[应收]=(case when A.A=1 then Convert(varchar(8),A.C) else '' end),
[实收]=(case when A.A=2 then Convert(varchar(8),A.C) else '' end),
[相差]=''
from @tb a
union
select B+'小计',
convert(varchar(8),sum((case when A=1 then C else 0 end))),
convert(varchar(8),sum((case when A=2 then C else 0 end))),
convert(varchar(8),sum((case when A=1 then C else 0 end))-sum((case when A=2 then C else 0 end)))
from @tb group by B
union all
select '合计',
convert(varchar(8),sum((case when A=1 then C else 0 end))),
convert(varchar(8),sum((case when A=2 then C else 0 end))),
convert(varchar(8),sum((case when A=1 then C else 0 end))-sum((case when A=2 then C else 0 end)))
from @tb
)T --结果
/*单位 应收 实收 相差
-------------- -------- -------- --------
北京 25
北京 10
北京 5
北京小计 15 25 -10
上海 12
上海 14
上海小计 14 12 2
西安 20
西安 10
西安小计 10 20 -10
合计 39 57 -18*/
UNION
select case when grouping(B)=1 then '小计' else ltrim(rtrim(b))+'合计' end,
[应收]=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end),
[实收]=sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end),
相差=sum(case WHEN SUBSTRING(A,1,1)='1' then c ELSE 0 end)-sum(case WHEN SUBSTRING(A,1,1)='2' then c ELSE 0 end)
from QQLL
group by B with ROLLUP
create table #t(A int,B varchar(10),C int)
insert #t
select 1,'xian',10 union
select 2,'xian',20 union
select 1,'beijing',10 union
select 2,'beijing',25 union
select 1,'beijing',5 union
select 1,'shanghai',14 union
select 2,'shanghai',12 方法一:依赖于“合计“排在后面,如果加上其他字符串可能不对,例如“综合总计“为合计的名称可能就排在后面,方法二修改了这个缺点
select b=(case c.dif when 0 then c.b
else case when c.b = '合計' then '合計'
else '小計'
end
end
) ,
sum1=(case c.sum1 when 0 then ''
else convert(varchar(10),c.sum1)
end
),
sum2=(case c.sum2 when 0 then ''
else convert(varchar(10),c.sum2)
end
),
dif=convert(varchar(10),c.dif)
from
(
select t.b as b,sum1=(case t.a when 1 then convert(varchar(10),t.c) else '' end),
sum2 = (case t.a when 2 then convert(varchar(10),t.c) else '' end),
'' as dif
from #t t
union
select t.b as b,sum1=sum(case t.a when 1 then t.c else 0 end),
sum2=sum(case t.a when 2 then t.c else 0 end),
dif =convert(varchar(10),
sum(case t.a when 1 then t.c else 0 end) - sum(case t.a when 2 then t.c else 0 end))
from #t t
group by t.b
union
select '合計' as b,sum1=convert(varchar(10),sum(case t.a when 1 then t.c else 0 end)),
sum2= convert(varchar(10),sum(case t.a when 2 then t.c else 0 end)),
dif=convert(varchar(10),sum(case t.a when 1 then t.c else 0 end) - convert(varchar(10),sum(case t.a when 2 then t.c else 0 end)) )
from #t t
) as c /*
Department sum1 sum2 dif
---------- ---------- ---------- ----------
beijing 25
beijing 5
beijing 10
小計 15 25 -10
shanghai 12
shanghai 14
小計 14 12 2
xian 20
xian 10
小計 10 20 -10
合計 39 57 -18(11 row(s) affected)
*/方法二:个人认为,这么多子查询,还是不要用的,在前台做,很简单,速度也较好
select Department=(
case c.sn when '2' then '合計'
else
(case when c.dif <> 0 then '小計'
else c.b
end)
end),
sum1=(case c.sum1 when 0 then ''
else convert(varchar(10),c.sum1)
end
),
sum2=(case c.sum2 when 0 then ''
else convert(varchar(10),c.sum2)
end
),
dif=convert(varchar(10),c.dif)
from (
select '1' as sn,t.b as b,sum1=(case t.a when 1 then convert(varchar(10),t.c) else '' end),
sum2 = (case t.a when 2 then convert(varchar(10),t.c) else '' end),
'' as dif
from #t t
union
select '1' as sn,t.b as b,sum1=sum(case t.a when 1 then t.c else 0 end),
sum2=sum(case t.a when 2 then t.c else 0 end),
dif =convert(varchar(10),
sum(case t.a when 1 then t.c else 0 end) - sum(case t.a when 2 then t.c else 0 end))
from #t t
group by t.b
union
select '2' as sn,'合計' as b,sum1=convert(varchar(10),sum(case t.a when 1 then t.c else 0 end)),
sum2= convert(varchar(10),sum(case t.a when 2 then t.c else 0 end)),
dif=convert(varchar(10),sum(case t.a when 1 then t.c else 0 end) - convert(varchar(10),sum(case t.a when 2 then t.c else 0 end)) )
from #t t
) as c order by c.b asc
, c.sn desc /*
Department sum1 sum2 dif
---------- ---------- ---------- ----------
beijing 25
beijing 5
beijing 10
小計 15 25 -10
shanghai 12
shanghai 14
小計 14 12 2
xian 20
xian 10
小計 10 20 -10
合計 39 57 -18(11 row(s) affected)
*/