如何用数据透视表计数谢谢大家

对对碰 · 发表于 2010-5-27 15:38

请教各位如何在数据透视表中计数

whsfhwm · 发表于 2010-5-27 16:31

我来试试看，能否满足要求。使用了SQL语句（有点复杂哈）：

SELECT 客户,货号,sum(数量) as 数量,sum(金额) as 金额 from [Sheet1$A1:F] group by 客户,货号
union (
select a.客户 & " 汇总",a.行数,b.数量,b.金额 from
(select 客户,count(*) as 行数 from (select distinct 客户,货号 from [Sheet1$A1:F]) group by 客户 ) a inner join
(select 客户,sum(数量) as 数量,sum(金额) as 金额 from [Sheet1$A1:F] group by 客户) b on a.客户=b.客户
)

复制代码

透视表计数.rar (4.61 KB, 下载次数: 18)

抹香鲸2009 · 发表于 2010-5-27 18:26

先留个脚印，明天抽空做。

whsfhwm · 发表于 2010-5-27 19:07

SELECT 客户,货号,sum(数量) as 数量,sum(金额) as 金额 from [Sheet1$A1:F] group by 客户,货号 union ( select a.客户 & ' 汇总',a.行数,b.数量,b.金额 from (select 客户,count(*) as 行数 from (select distinct 客户,货号 from [Sheet1$A1:F]) group by 客户 ) a inner join (select 客户,sum(数量) as 数量,sum(金额) as 金额 from [Sheet1$A1:F] group by 客户) b on a.客户=b.客户 ) union all select distinct " 总计","",(select sum(数量) from [Sheet1$A1:F]),(select sum(金额) from [Sheet1$A1:F]) from [Sheet1$A1:F]

复制代码

whsfhwm · 发表于 2010-5-27 19:09

楼主的要求也算是够特殊的啦。
不知道有没有哪位老师提供简洁一些的做法，期待中...

抹香鲸2009 · 发表于 2010-5-28 08:16

这样也可以的：
select 客户,sum(数量) as 数量,sum(金额) as 金额,0 as 款数 from [sheet1$a1:f26] group by 客户 union all
select 客户,0,0,count(货号) as 款数 from (select distinct 客户,货号 from [sheet1$a1:f26]) group by 客户

		自动登录	找回密码
密码			免费注册

[求助] 如何用数据透视表计数谢谢大家