select
f1 = case when a.f1 is null then '合计' else rtrim(a.f1) end,
f2 = case when a.f1 is null then null else a.f2 end,
a.f3
from
(select f1,f2,sum(f3) f3 from table1 group by f2,f1 with rollup) a
where
a.f2 is not null
f1 = case when a.f1 is null then '合计' else rtrim(a.f1) end,
f2 = case when a.f1 is null then null else a.f2 end,
a.f3
from
(select f1,f2,sum(f3) f3 from table1 group by f2,f1 with rollup) a
where
a.f2 is not null
f1 = case when a.f1 is null then '合计' else rtrim(a.f1) end,
f2 = case when a.f1 is null then '' else a.f2 end,
a.f3
from
(select f1,f2,sum(f3) f3 from table1 group by f2,f1 with rollup) a
where
a.f2 is not null
Select
Case When Grouping(f2)=1 Then N'总计'
When Grouping(f1)=1 Then N'合计'
Else Rtrim(f1) End As f1,
Case When Grouping(f1)=1 Then '' Else f2 End As f2,
SUM(f3) As f3
from table1
Group By f2,f1
With Rollup如果只要f2分组的统计Select * from
(Select
Case When Grouping(f2)=1 Then N'总计'
When Grouping(f1)=1 Then N'合计'
Else Rtrim(f1) End As f1,
Case When Grouping(f1)=1 Then '' Else f2 End As f2,
SUM(f3) As f3
from table1
Group By f2,f1
With Rollup) A
Where f1<>N'总计'
select
b.f1,a.f2,a.f3
from
(select f1,f2,f3 = sum(f3) from table1 group by f2,f1 with rollup) a
left join
table1 b
on a.f1 = b.f1
where a.f2 is not null
order by a.f2
SELECT prostock.id, prostock.nonewt,prokind.cname,
CASE
WHEN prokind.pian = 0 THEN '无'
WHEN prokind.pian <> 0 AND p_s <> 0 THEN CONVERT(char, CONVERT(int, prorealno / p_s))
WHEN prokind.pian <> 0 AND p_s = 0 THEN CONVERT(char, CONVERT(int,prorealno / pian)) END AS pianno,
round(prostock.prorealno, 2, 0) AS reno,
qy.enn,
CONVERT(varchar(11), proupdate, 121)
From prokind, prostock, qy
WHERE (prostock.sell_Note = 0) AND prostock.pro_id = prokind.id AND prostock.qyid = qy.id
ORDER BY enn, pro_id,prokind.id DESC
其实我的prostock.id是不重复的
我想按照prostock.cname分组来计算
pianno 和 reno 的合计
谢谢
以f2分组,f3,f6合计。create table table1(f1 int,f2 char(1),f3 int,f4 char(10),f5 char(10),f6 int)
insert table1 select 1,'A',10,'e65rt','tr54e',43
union all select 2,'A',20,'gfert','tr543e',435
union all select 3,'A',30,'egdrt','trtree',453
union all select 4,'A',20,'jhgert','t545re',436
union all select 5,'A',20,'56ert','tr54e',453
union all select 6,'B',30,'654ert','trrte',4783
union all select 7,'B',20,'uert','trde',543
union all select 8,'B',20,'iyutert','trtrete',4573
union all select 9,'B',30,'ytert','trree',443
union all select 10,'B',20,'ytuert','trere',463
union all select 11,'B',20,'euytrt','trfde',473
union all select 12,'C',30,'euyt76rt','tfdre',493
union all select 13,'C',20,'e76rt','trtye',430
union all select 14,'C',20,'e546rt','t543re',434
union all select 15,'C',30,'e766rt','t65re',432
union all select 16,'C',20,'e554rt','tr56e',434
以f2分组,f3,f6合计。create table table1(f1 int,f2 char(1),f3 int,f4 char(10),f5 char(10),f6 int)
insert table1 select 1,'A',10,'e65rt','tr54e',43
union all select 2,'A',20,'gfert','tr543e',435
union all select 3,'A',30,'egdrt','trtree',453
union all select 4,'A',20,'jhgert','t545re',436
union all select 5,'A',20,'56ert','tr54e',453
union all select 6,'B',30,'654ert','trrte',4783
union all select 7,'B',20,'uert','trde',543
union all select 8,'B',20,'iyutert','trtrete',4573
union all select 9,'B',30,'ytert','trree',443
union all select 10,'B',20,'ytuert','trere',463
union all select 11,'B',20,'euytrt','trfde',473
union all select 12,'C',30,'euyt76rt','tfdre',493
union all select 13,'C',20,'e76rt','trtye',430
union all select 14,'C',20,'e546rt','t543re',434
union all select 15,'C',30,'e766rt','t65re',432
union all select 16,'C',20,'e554rt','tr56e',434
f1 = case when a.f1 is null then '合计' else rtrim(a.f1) end,
f2 = case when a.f1 is null then '' else a.f2 end,
a.f3,
a.f4,
a.f5,
a.f6
from
(select
f1,f2,f3=sum(f3),f4,f5,f6=sum(f6)
from table1
group by f2,f1,f4,f5 with rollup) a
where
(a.f1 is null and a.f2 is not null) or a.f5 is not null
------------------------------------------------------------f1 f2 f3 f4 f5 f6
------------------------------------------------------------
1 A 10 e65rt tr54e 43
2 A 20 gfert tr543e 435
3 A 30 egdrt trtree 453
4 A 20 jhgert t545re 436
5 A 20 56ert tr54e 453
合计 100 NULL NULL 1820
6 B 30 654ert trrte 4783
7 B 20 uert trde 543
8 B 20 iyutert trtrete 4573
9 B 30 ytert trree 443
10 B 20 ytuert trere 463
11 B 20 euytrt trfde 473
合计 140 NULL NULL 11278
12 C 30 euyt76rt tfdre 493
13 C 20 e76rt trtye 430
14 C 20 e546rt t543re 434
15 C 30 e766rt t65re 432
16 C 20 e554rt tr56e 434
合计 120 NULL NULL 2223
(Select
Case When Grouping(f2)=1 Then N'总计'
When Grouping(f1)=1 Then N'合计'
Else Rtrim(f1) End As f1,
Case When Grouping(f1)=1 Then '' Else f2 End As f2,
SUM(f3) As f3,
Case When Grouping(f1)=1 Then '' Else f4 End As f4,
Case When Grouping(f1)=1 Then '' Else f5 End As f5,
SUM(f6) As f6
from test1
Group By f2,f1,f4,f5
With Rollup) A
Where f1<>N'总计' And f5 Is Not Null
f1 f2 f3 f4 f5 f6
1 A 10 e65rt tr54e 43
2 A 20 gfert tr543e 435
3 A 30 egdrt trtree 453
4 A 20 jhgert t545re 436
5 A 20 56ert tr54e 453
合计 100 1820
6 B 30 654ert trrte 4783
7 B 20 uert trde 543
8 B 20 iyutert trtrete 4573
9 B 30 ytert trree 443
10 B 20 ytuert trere 463
11 B 20 euytrt trfde 473
合计 140 11278
12 C 30 euyt76rt tfdre 493
13 C 20 e76rt trtye 430
14 C 20 e546rt t543re 434
15 C 30 e766rt t65re 432
16 C 20 e554rt tr56e 434
合计 120 2223
f1 = case
when a.f2 is null then '总计'
when a.f1 is null then '合计'
else rtrim(a.f1)
end,
f2 = case when a.f1 is null then NULL else a.f2 end,
a.f3,
a.f4,
a.f5,
a.f6
from
(select
f1,f2,f3=sum(f3),f4,f5,f6=sum(f6)
from table1
group by f2,f1,f4,f5 with rollup) a
where
a.f1 is null or a.f5 is not null