SQL Server MDX结果集Sets使用技巧详解
SQL Server 2008 MDX学习笔记之结果集Sets使用技巧是本文我们主要要介绍的内容，接下来就让我们从以下的几个例子中区了解结果集Sets的使用技巧吧。
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 */
关于SQL Server 2008 MDX学习笔记之结果集Sets使用技巧的知识就介绍到这里了，希望本次的介绍能够对您有所收获！