只需关注这一句就行了吧?
SHAPE {SELECT FirstName,Lastname,City FROM `Employees`} AS Command1 COMPUTE
Command1 BY 'City'
SHAPE {SELECT FirstName,Lastname,City FROM `Employees`} AS Command1 COMPUTE
Command1 BY 'City'
调试欢乐多
use pubs
goSELECT title = CONVERT(char(20), title), type, price, advance
FROM titles
WHERE ytd_sales IS NOT NULL
AND type LIKE '%cook%'
ORDER BY type DESC
COMPUTE AVG(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)title type price advance
-------------------- ------------ --------------------- ---------------------
Onions, Leeks, and G trad_cook 20.9500 7000.0000
Fifty Years in Bucki trad_cook 11.9500 4000.0000
Sushi, Anyone? trad_cook 14.9900 8000.0000 avg
=====================
15.9633 sum
=====================
19000.0000
title type price advance
-------------------- ------------ --------------------- ---------------------
Silicon Valley Gastr mod_cook 19.9900 .0000
The Gourmet Microwav mod_cook 2.9900 15000.0000 avg
=====================
11.4900 sum
=====================
15000.0000
sum
=====================
70.8700 sum
=====================
34000.0000
(所影响的行数为 8 行)
格式如下:
部门名称 数据 数据 数据 数据 数据 数据
.
.
.
.
小计
.
部门名称 数据 数据 数据 数据 数据 数据
.
.
小计
.
合计
.
部门名称 数据 数据 数据 数据 数据 数据
.
.
.
.
小计
.
部门名称 数据 数据 数据 数据 数据 数据
.
.
小计
.
合计
.用一个语句算了:
select case when type=2 then '小计'
when type=3 then '合计'
else 部门名称
end as 部门名称,
数据1,数据2
from (
select 1 as type,部门名称,数据1,数据2,...
from tab
union all
select 2 as type,部门名称,sum(数据1) as 数据1,sum(数据2) as 数据2,...
from tab
group by 部门名称
union all
select 3 as type,max(部门名称) as 部门名称,sum(数据1) as 数据1,sum(数据2) as 数据2,...
from tab
) as tmp
order by 部门名称,type
http://www.triaton.com.cn/triatonk2/Private/Zip/ShapeTree.zip两个例子!