请教关于透视表的问题？

songdawei

请问一个关于透视表的问题，附件中的透视表是如何做出来的，我怎么做出来的透视表没有科目、名次这些字段，这些字段是怎么做出来的，恳请高手指点指点。

wjzwang

你打开透视表，在数据源，连接属性，定义，命令文本里，有SELECT语句，如下：
select (select count(成绩1) as 数量 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="语文") group by 科目,姓名) A1 where A1.成绩1 >=A2.成绩1) as 名次,A2.科目,A2.姓名,A2.成绩1 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="语文") group by 科目,姓名 ) A2 union all
select (select count(成绩1) as 数量 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="数学") group by 科目,姓名) A1 where A1.成绩1 >=A2.成绩1) as 名次,A2.科目,A2.姓名,A2.成绩1 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="数学") group by 科目,姓名 ) A2 union all

select (select count(成绩1) as 数量 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="英语") group by 科目,姓名) A1 where A1.成绩1 >=A2.成绩1) as 名次,A2.科目,A2.姓名,A2.成绩1 from (select 科目,姓名,sum(成绩) as 成绩1 from (select * from (select "语文" as 科目,姓名,语文 as 成绩 from [成绩$] union all select "数学" as 科目,姓名,数学 as 成绩 from [成绩$] union all select "英语" as 科目,姓名,英语 as 成绩 from [成绩$] ) where 科目="英语") group by 科目,姓名 ) A2

zhzhy202

建议你到以下地址学习一下。

http://www.excelhome.net/post/422.htm

亡者天下

今天又学习了一招！！

songdawei

谢谢老师指点，多谢了。

wjg023

你在选择数据透视表时把行首,列首都选上,
在透视表布局时,选择上你需要的列就行了呀.
透视表,就是对某列的以某种规则的统计