《Microsoft SQL Server 2008 MDX Step by Step》学习笔记三：了解结果集（Sets）

SQL Server 2008中SQL应用系列及BI笔记系列--目录索引

导读:本文介绍结果集（Sets）的基础内容，已经了解的读者可以直接略过。

本文将包括以下内容：

■1、使用基本技巧组装Sets

■2、使用Sets从一个查询中提取多个Cell

■3、了解Select语句的基本组件

本文所用数据库和所有源码，请到微软官网下载

1、Sets的基本技巧

在Analysis Service中，Set代表元组（Tuples）的集合。在一个Set内部，独立的元组被用逗号隔开，如下：

{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
}

下面我们组装一个基本的Sets

打开MDX查询编辑器，如下：

例4-1

SELECT
{
([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
} ON COLUMNS,
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
} ON ROWS
FROM [Step-by-Step]
;

我们增加一个元组([Product].[Subcategory].[Mountain Bikes])，如下：

例4-2

SELECT
{
([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
} ON COLUMNS,
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components]),
([Product].[Subcategory].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;
/*
Executing the query ...
Members belong to different hierarchies in the function.
函数中指定的两个集具有不同的维数。
Execution complete
*/

我们使用相同维数的用户层次结构[Product Categories]，修改如下:

例4-3

SELECT
{
([Date].[Calendar].[CY 2002], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
} ON COLUMNS,
{
([Product].[Product Categories ].[Accessories]),
([Product].[Product Categories ].[Bikes]),
([Product].[Product Categories ].[Clothing]),
([Product].[Product Categories ].[Components]),
([Product].[Product Categories ].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;
/* CY 2002 CY 2003 CY 2004
United States United States United States
Accessories $61,263.90 $151,136.35 $76,027.18
Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
Clothing $317,939.41 $495,443.62 $197,590.92
Components $2,526,542.06 $3,284,551.84 $1,137,105.72
Mountain Bikes $6,970,418.73 $5,832,626.02 $2,539,198.92
*/

下面这个查询有类似错误：

例4-4

SELECT
{
([Geography].[Country].[United States], [Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003], [Geography].[Country].[United States]),
([Date].[Calendar].[CY 2004], [Geography].[Country].[United States])
} ON COLUMNS,
{
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product Categories].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;
/*
Executing the query ...
Query (2, 4) Two sets specified in the function have different dimensionality.
函数中指定的两个集具有不同的维数。
Execution complete
*/

正确应为：

例4-5

SELECT
{
([Geography].[Country].[United States], [Date].[Calendar].[CY 2002]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2004])
} ON COLUMNS,
{
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product Categories].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;

我们可以这样改变顺序，并增加一个行：

例4-6

SELECT
{
([Geography].[Country].[United States], [Date].[Calendar].[CY 2004]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2002])
} ON COLUMNS,
{
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product Categories].[Mountain Bikes])
} ON ROWS
FROM [Step-by-Step]
;

查询结果如下：

/* United States United States United States
CY 2004 CY 2003 CY 2002
Accessories $76,027.18 $151,136.35 $61,263.90
Accessories $76,027.18 $151,136.35 $61,263.90
Bikes $7,951,335.55 $16,139,984.68 $14,716,804.14
Clothing $197,590.92 $495,443.62 $317,939.41
Components $1,137,105.72 $3,284,551.84 $2,526,542.06
Mountain Bikes $2,539,198.92 $5,832,626.02 $6,970,418.73
*/

多出的重复行怎么办？我们可以使用distinct函数（http://msdn.microsoft.com/zh-cn/library/ms146033.aspx ）

例4-6

SELECT
{
([Geography].[Country].[United States], [Date].[Calendar].[CY 2004]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2003]),
([Geography].[Country].[United States], [Date].[Calendar].[CY 2002])
} ON COLUMNS,
DISTINCT (
{
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Accessories]),
([Product].[Product Categories].[Bikes]),
([Product].[Product Categories].[Clothing]),
([Product].[Product Categories].[Components]),
([Product].[Product Categories].[Mountain Bikes])
}
) ON ROWS
FROM [Step-by-Step]
;
/* United States United States United States
CY 2004 CY 2003 CY 2002
Accessories $76,027.18 $151,136.35 $61,263.90
Bikes $7,951,335.55 $16,139,984.68 $14,716,804.14
Clothing $197,590.92 $495,443.62 $317,939.41
Components $1,137,105.72 $3,284,551.84 $2,526,542.06
Mountain Bikes $2,539,198.92 $5,832,626.02 $6,970,418.73

*/

2、理解Select语句

select语句（http://msdn.microsoft.com/zh-cn/library/ms146002.aspx ）的结构如下图：

3、使用函数生成Sets

下面了解几个非常useful的函数。

（一）Member函数（http://msdn.microsoft.com/zh-cn/library/ms144851(v=SQL.105).aspx ），返回某个维度、级别或层次结构中的成员集。

我们看两个查询：

例4-7

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
{
([Product].[Category].[Accessories]),
([Product].[Category].[Bikes]),
([Product].[Category].[Clothing]),
([Product].[Category].[Components])
} ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;

例4-8

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
{[Product].[Category].[Category].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;

这两个MDX查询的结果是一样的，都是：

/* CY 2002 CY 2003 CY 2004
Accessories $61,263.90 $151,136.35 $76,027.18
Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
Clothing $317,939.41 $495,443.62 $197,590.92
Components $2,526,542.06 $3,284,551.84 $1,137,105.72
*/

请大家结合例子体会Member函数的用途。

我们略微修改一下例4-8 ，如下：

例4-9

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
{[Product].[Category].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;

/* CY 2002 CY 2003 CY 2004
All Products $17,622,549.51 $20,071,116.48 $9,362,059.37
Accessories $61,263.90 $151,136.35 $76,027.18
Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
Clothing $317,939.41 $495,443.62 $197,590.92
Components $2,526,542.06 $3,284,551.84 $1,137,105.72
*/

我们再修改，

例4-10

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
{[Product].[Product Categories ].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;

/* NOTE: First 10 Row Members Presented
CY 2002 CY 2003 CY 2004
All Products $17,622,549.51 $20,071,116.48 $9,362,059.37
Accessories $61,263.90 $151,136.35 $76,027.18
Bike Racks (null) $57,382.62 $36,937.90
Hitch Rack - 4-Bike (null) $57,382.62 $36,937.90
Bike Stands (null) (null) (null)
All-Purpose Bike Stand (null) (null) (null)
Bottles and Cages (null) $2,186.60 $1,554.90
Mountain Bottle Cage (null) (null) (null)
Road Bottle Cage (null) (null) (null)
Water Bottle - 30 oz. (null) $2,186.60 $1,554.90
*/

（二）MeasureGroupMeasures函数（http://msdn.microsoft.com/zh-cn/library/ms145485(v=SQL.105).aspx ），它返回属于指定度量值组的一组度量值。

例4-11

SELECT
{MeasureGroupMeasures("Reseller Sales")} ON COLUMNS,
{[Product].[Category].Members} ON ROWS
FROM [Step-by-Step]
;
/* Reseller Sales Amount Reseller Order Quantity Reseller Extended Amount Reseller Tax Amount Reseller Freight Cost Discount Amount Reseller Total Product Cost Reseller Standard Product Cost
All Products $80,450,596.98 214,378 $80,978,104.87 $6,436,047.61 $2,011,265.92 $527,507.93 $79,980,114.38 $26,693,830.57
Accessories $571,297.93 25,839 $577,985.95 $45,703.83 $14,282.52 $6,688.03 $375,505.33 $71,122.89
Bikes $66,302,381.56 75,015 $66,797,022.19 $5,304,190.40 $1,657,560.05 $494,640.65 $67,293,081.45 $22,039,062.39
Clothing $1,777,840.84 64,497 $1,798,805.33 $142,227.25 $44,446.19 $20,964.51 $1,545,417.42 $294,511.26
Components $11,799,076.66 49,027 $11,804,291.40 $943,926.12 $294,977.15 $5,214.74 $10,766,110.18 $4,289,134.03
*/

（三）Crossjoin函数（http://msdn.microsoft.com/zh-cn/library/ms144816.aspx ）,返回一个或多个集的叉积。

例4-12

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
{[Product].[Category].[Category].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;
/* CY 2002 CY 2003 CY 2004
Accessories $61,263.90 $151,136.35 $76,027.18
Bikes $14,716,804.14 $16,139,984.68 $7,951,335.55
Clothing $317,939.41 $495,443.62 $197,590.92
Components $2,526,542.06 $3,284,551.84 $1,137,105.72
*/ 

例4-13

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} ON COLUMNS,
Crossjoin (
{[Product].[Category].[Category].Members},
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Order Quantity])
}
) ON ROWS
FROM [Step-by-Step]
WHERE ([Geography].[Country].[United States])
;
/*
CY 2002 CY 2003 CY 2004
Accessories Reseller Sales Amount $61,263.90 $151,136.35 $76,027.18
Accessories Reseller Order Quantity 3,426 6,848 3,125
Bikes Reseller Sales Amount $14,716,804.14 $16,139,984.68 $7,951,335.55
Bikes Reseller Order Quantity 16,079 19,728 9,121
Clothing Reseller Sales Amount $317,939.41 $495,443.62 $197,590.92
Clothing Reseller Order Quantity 11,284 18,028 7,250
Components Reseller Sales Amount $2,526,542.06 $3,284,551.84 $1,137,105.72
Components Reseller Order Quantity 9,602 14,503 5,323
*/

注意这里有四个产品分类和两个度量，因而共有4*2=8个元组(tuples)。

当然，下面是一种用法，

例4-15

SELECT
CROSSJOIN (
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
},
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
)
ON COLUMNS,
{[Product].[Category].[Category].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;

其结果为：

/* CY 2002 CY 2002 CY 2003 CY 2003 CY 2004 CY 2004
Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount
Accessories $61,263.90 (null) $151,136.35 $293,709.71 $76,027.18 $407,050.25
Bikes $14,716,804.14 $6,530,343.53 $16,139,984.68 $9,359,102.62 $7,951,335.55 $9,162,324.85
Clothing $317,939.41 (null) $495,443.62 $138,247.97 $197,590.92 $201,524.64
Components $2,526,542.06 (null) $3,284,551.84 (null) $1,137,105.72 (null)
*/

4、限定Sets

（一）Auto-exists

例4-16

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} *
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
{[Product].[Category].[Category].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;
/* CY 2002 CY 2002 CY 2003 CY 2003 CY 2004 CY 2004
Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount
Accessories $61,263.90 (null) $151,136.35 $293,709.71 $76,027.18 $407,050.25
Bikes $14,716,804.14 $6,530,343.53 $16,139,984.68 $9,359,102.62 $7,951,335.55 $9,162,324.85
Clothing $317,939.41 (null) $495,443.62 $138,247.97 $197,590.92 $201,524.64
Components $2,526,542.06 (null) $3,284,551.84 (null) $1,137,105.72 (null)
*/ 

注意提示：Cell set consists of 6 rows and 7 columns.实际返回的只有5行

例4-17

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
}*
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
{[Product].[Category].[Category].Members}
* {[Product].[Subcategory].[Subcategory].Members}
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;
/*
CY 2002 CY 2002 CY 2003 CY 2003 CY 2004 CY 2004
Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount
Accessories Bike Racks (null) (null) $57,382.62 $16,440.00 $36,937.90 $22,920.00
Accessories Bike Stands (null) (null) (null) $18,921.00 (null) $20,670.00
Accessories Bottles and Cages (null) (null) $2,186.60 $23,280.27 $1,554.90 $33,517.92
Accessories Cleaners (null) (null) $3,379.09 $3,044.85 $2,232.34 $4,173.75
Accessories Fenders (null) (null) (null) $19,408.34 (null) $27,211.24
Accessories Helmets $49,586.49 (null) $61,131.18 $92,583.54 $24,542.87 $132,752.06
Accessories Hydration Packs (null) (null) $19,561.85 $16,771.95 $10,639.64 $23,535.72
Accessories Lights (null) (null) (null) (null) (null) (null)
Accessories Locks $6,339.46 (null) $3,890.52 (null) (null) (null)
Accessories Panniers (null) (null) (null) (null) (null) (null)
Accessories Pumps $5,337.95 (null) $3,262.37 (null) (null) (null)
Accessories Tires and Tubes (null) (null) $342.13 $103,259.76 $119.54 $142,269.56
Bikes Mountain Bikes $6,970,418.73 $1,562,456.76 $5,832,626.02 $3,989,638.48 $2,539,198.92 $3,814,691.06
Bikes Road Bikes $7,746,385.41 $4,967,886.77 $7,838,944.52 $3,952,029.21 $3,042,999.81 $2,920,267.67
Bikes Touring Bikes (null) (null) $2,468,414.14 $1,417,434.93 $2,369,136.82 $2,427,366.12
Clothing Bib-Shorts $64,077.06 (null) $42,248.34 (null) (null) (null)
Clothing Caps $6,338.69 (null) $7,200.98 $7,956.15 $2,779.52 $11,731.95
Clothing Gloves $59,284.03 (null) $61,502.08 $14,228.69 $7,570.49 $20,792.01
Clothing Jerseys $74,165.33 (null) $150,755.38 $70,370.46 $71,246.39 $102,580.22
Clothing Shorts $31,632.12 (null) $108,658.99 $30,445.65 $67,982.00 $40,874.16
Clothing Socks $2,642.10 (null) $7,935.99 $2,229.52 $5,513.87 $2,876.80
Clothing Tights $79,800.08 (null) $52,561.35 (null) (null) (null)
Clothing Vests (null) (null) $64,580.51 $13,017.50 $42,498.65 $22,669.50
Components Bottom Brackets (null) (null) $16,433.12 (null) $11,589.91 (null)
Components Brakes (null) (null) $22,966.98 (null) $11,054.70 (null)
Components Chains (null) (null) $3,065.40 (null) $1,906.61 (null)
Components Cranksets (null) (null) $62,408.73 (null) $42,709.90 (null)
Components Derailleurs (null) (null) $21,731.33 (null) $12,608.56 (null)
Components Forks $32,047.57 (null) $19,191.24 (null) (null) (null)
Components Handlebars $38,615.98 (null) $55,997.42 (null) $17,002.14 (null)
Components Headsets $23,991.39 (null) $16,164.84 (null) (null) (null)
Components Mountain Frames $1,019,984.65 (null) $1,311,493.01 (null) $501,825.49 (null)
Components Pedals (null) (null) $57,437.40 (null) $34,089.08 (null)
Components Road Frames $1,100,779.19 (null) $1,020,543.04 (null) $209,069.30 (null)
Components Saddles (null) (null) $20,562.60 (null) $9,715.37 (null)
Components Touring Frames (null) (null) $492,424.75 (null) $285,534.67 (null)
Components Wheels $311,123.28 (null) $164,131.99 (null) (null) (null)
*/

注意这个结果是从37*4=148个行中，只返回了39行。

我们再修改下查询：

例4-18

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} *
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
{[Product].[Category].[Category].Members}
* {[Product].[Color].[Color].Members}
ON ROWS
FROM [Step-by-Step]
WHERE ([Geography].[Country].[United States],
[Product].[Subcategory].[Mountain Bikes])
;
/* CY 2002 CY 2002 CY 2003 CY 2003 CY 2004 CY 2004
Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount
Bikes Black $3,927,415.56 $842,059.40 $3,265,300.39 $1,962,003.53 $1,265,049.27 $1,775,241.11
Bikes Silver $3,043,003.18 $720,397.36 $2,567,325.63 $2,027,634.94 $1,274,149.65 $2,039,449.95
*/

（二）Exists函数

例4-19

SELECT
{
([Date].[Calendar].[CY 2002]),
([Date].[Calendar].[CY 2003]),
([Date].[Calendar].[CY 2004])
} *
{
([Measures].[Reseller Sales Amount]),
([Measures].[Internet Sales Amount])
}
ON COLUMNS,
EXISTS (
{[Product].[Category].[Category].Members}
* {[Product].[Color].[Color].Members},
{([Product].[Subcategory].[Mountain Bikes])}
)
ON ROWS
FROM [Step-by-Step]
WHERE [Geography].[Country].[United States]
;
/* CY 2002 CY 2002 CY 2003 CY 2003 CY 2004 CY 2004
Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount Reseller Sales Amount Internet Sales Amount
Bikes Black $6,314,977.53 $1,728,251.55 $6,358,849.63 $3,775,240.23 $2,446,852.45 $2,809,810.02
Bikes Silver $3,043,003.18 $720,397.36 $2,567,325.63 $2,027,634.94 $1,274,149.65 $2,039,449.95
*/

请注意上两个查询的不同。

（三）NON EMPTY关键字和Exists函数的其他形式

例4-20

SELECT
{[Measures].[Reseller Sales Amount]} ON COLUMNS,
{[Date].[Month].[May 2002]} * {[Employee].[Employee].Members} ON ROWS
FROM [Step-by-Step]
;
/* NOTE: First 75 Row Members Shown
Reseller Sales Amount
May 2002 All Employees $2,269,116.71
May 2002 A. Scott Wright (null)
May 2002 Alan J. Brewer (null)
May 2002 Alejandro E. McGuel (null)
May 2002 Alex M. Nayberg (null)
May 2002 Alice O. Ciccu (null)
May 2002 Amy E. Alberts (null)
May 2002 Andreas T. Berglund (null)
May 2002 Andrew M. Cencini (null)
May 2002 Andrew R. Hill (null)
May 2002 Andy M. Ruth (null)
May 2002 Angela W. Barbariol (null)
May 2002 Anibal T. Sousa (null)
May 2002 Annette L. Hill (null)
May 2002 Annik O. Stahl (null)
May 2002 Arvind B. Rao (null)
May 2002 Ashvini R. Sharma (null)
May 2002 Barbara C. Moreland (null)
May 2002 Barbara S. Decker (null)
May 2002 Baris F. Cetinok (null)
May 2002 Barry K. Johnson (null)
May 2002 Belinda M. Newman (null)
May 2002 Ben T. Miller (null)
May 2002 Benjamin R. Martin (null)
May 2002 Betsy A. Stadick (null)
May 2002 Bjorn M. Rettig (null)
May 2002 Bob N. Hohman (null)
May 2002 Bonnie N. Kearney (null)
May 2002 Brandon G. Heidepriem (null)
May 2002 Brenda M. Diaz (null)
May 2002 Brian P. LaMee (null)
May 2002 Brian Richard. Goldstein (null)
May 2002 Brian S. Welcker (null)
May 2002 Brian T. Lloyd (null)
May 2002 Britta L. Simon (null)
May 2002 Bryan A. Walton (null)
May 2002 Bryan Baker (null)
May 2002 Candy L. Spoon (null)
May 2002 Carol M. Philips (null)
May 2002 Carole M. Poland (null)
May 2002 Chad W. Niswonger (null)
May 2002 Charles B. Fitzgerald (null)
May 2002 Chris K. Norred (null)
May 2002 Chris O. Okelberry (null)
May 2002 Chris T. Preston (null)
May 2002 Christian E. Kleinerman (null)
May 2002 Christopher E. Hill (null)
May 2002 Cristian K. Petculescu (null)
May 2002 Cynthia S. Randall (null)
May 2002 Dan B. Wilson (null)
May 2002 Dan K. Bacon (null)
May 2002 Danielle C. Tiedt (null)
May 2002 David A. Yalovsky (null)
May 2002 David J. Liu (null)
May 2002 David J. Ortiz (null)
May 2002 David M. Barber (null)
May 2002 David M. Bradley (null)
May 2002 David M. Bradley (null)
May 2002 David N. Johnson (null)
May 2002 David Oliver. Lawrence (null)
May 2002 David P. Hamilton (null)
May 2002 David R. Campbell $115,901.80
May 2002 Deborah E. Poe (null)
May 2002 Denise H. Smith (null)
May 2002 Diane H. Tibbott (null)
May 2002 Diane L. Margheim (null)
May 2002 Diane R. Glimp (null)
May 2002 Don L. Hall (null)
May 2002 Doris M. Hartwig (null)
May 2002 Douglas B. Hite (null)
May 2002 Dragan K. Tomic (null)
May 2002 Dylan A. Miller (null)
May 2002 Ebru N. Ersan (null)
May 2002 Ed R. Dudenhoefer (null)
May 2002 Elizabeth I. Keyser (null)
....
*/ 

例4-21

SELECT
{[Measures].[Reseller Sales Amount]} ON COLUMNS,
NON EMPTY {[Date].[Month].[May 2002]} * {[Employee].[Employee].Members} ON ROWS
FROM [Step-by-Step]
;
/* Reseller Sales Amount
May 2002 All Employees $2,269,116.71
May 2002 David R. Campbell $115,901.80
May 2002 Garrett R. Vargas $172,622.35
May 2002 Jillian Carson $464,167.31
May 2002 Jos?Edvaldo. Saraiva $212,020.26
May 2002 Linda C. Mitchell $401,687.56
May 2002 Michael G. Blythe $172,429.58
May 2002 Pamela O. Ansman-Wolfe $252,116.05
May 2002 Shu K. Ito $103,508.43
May 2002 Tsvi Michael. Reiter $374,663.36
*/

关于Sets的学习到此为止，下文我们将要了解的概念是表达式。相信这节和下节对于熟悉SQL的同学而言是轻松愉快的，只不过查询结果变成了多维的，而不是我们通常看到的二维(行列）格式，不是吗？

参考资源：

1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx ）

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! [e-mail]