Parent sName iQty
A AA 1
A AA 2
A AB 1
A AB 2
B BA 1
B BA 4
求
AA 1
AA 2
AA 3(合计)
AB 1
AB 2
AB 3(合计)
A 6
BA 1
BA 4
BA 5(合计)
B 5
A AA 1
A AA 2
A AB 1
A AB 2
B BA 1
B BA 4
求
AA 1
AA 2
AA 3(合计)
AB 1
AB 2
AB 3(合计)
A 6
BA 1
BA 4
BA 5(合计)
B 5
解决方案 »
- 这样的数据库结构,如何排列,同时排列第一条记录和最后一条记录
- 困扰很长时间:如何解决已确认的出入库单要修改的问题?
- 如何为一张表设置多个KEY?
- 关于VB程序运行过程中数据库数据存储的问题
- 帮帮忙啊,如何把Syback的数据库转成sql server 2000的数据库
- 如何去除表中的标识列(ID),改为常用的 ID int not null???
- 请教高手sqlserver2000一个触发器的写法?
- 修改表问题~
- 高手是不是都睡着了?关于SQL SERVER 2000 建表和建库的问题.(两天前就发了此问题,但无一个人回答,真的有那么难吗!?抱歉,我只留下35分了.)
- 事务锁 , 锁那些内容。
- 求一条统计的SQL语句!
- 数据库如何获取当前记录的下一条最优算法征集
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
情况一:只有一个分类汇总列时,只需要一个合计。只需要增加with rollup即可。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情况二:有多个分类汇总列,只需要一个合计.增加rollup之后,需要增加判断。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
sum(c),sum(d),sum(e) from #t
group by a,b with rollup
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(c)=0 or grouping(a)=1
情况三:有多个分类汇总列,需要全部的小计和合计。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
另外一种显示小计的方式
select case when grouping(a)=1 then '合计'
when grouping(b)=1 then cast(a as varchar)+'小计'
else cast(a as varchar) end a,
case when grouping(b)=0 and grouping(c)=1
then cast(b as varchar)+'小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0
then '' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
情况四:有多个分类汇总列,需要部分的小计和合计
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=0
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=
union all
select sName,sum(iQty) from tb group by sName
union all
select Parent,sum(iQty) from tb group by Parent
order by
left(sname,1),
len(sname) desc
group by Parent,sName
with rollup
order by Parent,sName
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Parent] varchar(1),[sName] varchar(2),[iQty] int)
insert [tb]
select 'A','AA',1 union all
select 'A','AA',2 union all
select 'A','AB',1 union all
select 'A','AB',2 union all
select 'B','BA',1 union all
select 'B','BA',4
---查询---
select *
from
(
select sName,iQty from tb
union all
select sName,sum(iQty) from tb group by sName
union all
select Parent,sum(iQty) from tb group by Parent
) t
order by
left(sname,1),
len(sname) desc,
sname,
iqty---结果---
sName iQty
----- -----------
AA 1
AA 2
AA 3
AB 1
AB 2
AB 3
A 6
BA 1
BA 4
BA 5
B 5(所影响的行数为 11 行)
(1)with rollup
(2)union all
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( Parent varchar(10), sName varchar(10) , iQty int)
go
insert tb SELECT 'A' , 'AA' , 1 UNION ALL SELECT
'A' , 'AA' , 2 UNION ALL SELECT
'A' , 'AB' , 1 UNION ALL SELECT
'A' , 'AB' , 2 UNION ALL SELECT
'B' , 'BA' , 1 UNION ALL SELECT
'B' , 'BA' , 4
go
select * from (
select sName ,iQty from tb
union all
select sName ,sum(iQty)
from tb
group by sName with cube
having GROUPING(sName)=0
union all
select LEFT(sname,1) ,SUM(iqty) from tb group by LEFT(sname,1)
) t order by sName ,iQtygo
/*
(6 行受影响)
sName iQty
---------- -----------
A 6
AA 1
AA 2
AA 3
AB 1
AB 2
AB 3
B 5
BA 1
BA 4
BA 5
*/
insert @tb select 'A', 'AA', 2
insert @tb select 'A', 'AB', 1
insert @tb select 'A', 'AB', 2
insert @tb select 'B', 'BA', 1
insert @tb select 'B', 'BA', 4 select sname,iqty from (
select Parent,sname,iqty from @tb
union all
select Parent,sName,iQty=sum(iQty) from @tb
group by Parent,sName
union all
select Parent,Parent+'合计',iQty=sum(iQty) from @tb
group by Parent) b
order by left(sname,1),sname,iqty
/*
sname iqty
------ -----------
AA 1
AA 2
AA 3
AB 1
AB 2
AB 3
A合计 6
BA 1
BA 4
BA 5
B合计 5(所影响的行数为 11 行)
*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( Parent varchar(10), sName varchar(10) , iQty int)
go
insert tb SELECT 'A' , 'AA' , 1 UNION ALL SELECT
'A' , 'AA' , 2 UNION ALL SELECT
'A' , 'AB' , 1 UNION ALL SELECT
'A' , 'AB' , 2 UNION ALL SELECT
'B' , 'BA' , 1 UNION ALL SELECT
'B' , 'BA' , 4
go
select * from (
select sName ,iQty from tb
union all
select sName ,sum(iQty)
from tb
group by sName with cube
having GROUPING(sName)=0
union all
select LEFT(sname,1) ,SUM(iqty) from tb group by LEFT(sname,1)
) t
order by
left(sname,1),
len(sname) desc,
sname,
iqtygo
/*(6 行受影响)
sName iQty
---------- -----------
AA 1
AA 2
AA 3
AB 1
AB 2
AB 3
A 6
BA 1
BA 4
BA 5
B 5(11 行受影响)
*/
sName的内容是没有规律的
按1楼的方法搞定