创建查询作用域的命名集 (MDX)
如果某个命名集仅用于单个多维表达式 (MDX) 查询，则可以使用 WITH 关键字来定义该命名集。使用 WITH 关键字创建的命名集在执行完查询之后就不再存在。
如本主题中所述，WITH 关键字的语法非常灵活，甚至允许使用函数来定义命名集。
.gif "ms145487.note(zh-cn,SQL.90).gif") 注意： |
|---|
| 有关命名集的详细信息，请参阅在 MDX 中生成命名集 (MDX)。 |
WITH 关键字语法
可以使用以下语法将 WITH 关键字添加到 MDX SELECT 语句：
[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ]
SELECT [ * | ( <SELECT query axis clause> [ , <SELECT query axis clause> ... ] ) ]
FROM <SELECT subcube clause>
[ <SELECT slicer axis clause> ]
[ <SELECT cell property list clause> ]
<SELECT WITH clause> ::=
( SET Set_Identifier AS 'Set_Expression')
在 WITH 关键字的语法中，Set_Identifier 参数包含命名集的别名。Set_Expression 参数包含命名集别名所指代的集表达式。
WITH 关键字示例
下列 MDX 查询将检查 FoodMart 2000（Microsoft SQL Server 2000 Analysis Services 示例数据库）中各种 Chardonnay 酒和 Chablis 酒的单位销售额。尽管就结果集而言此查询相当简单，但是在必须维护这种查询的情况下，它就显得冗长且难处理。
SELECT
{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chardonnay], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chardonnay], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chardonnay], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chardonnay], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chablis Wine], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chablis Wine], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chablis Wine], [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chablis Wine]} ON COLUMNS,
{Measures.[Unit Sales]} ON ROWS
FROM Sales
若要使上述 MDX 更易于维护，可以使用 WITH 关键字为此查询创建一个命名集。以下代码显示了如何使用 WITH 关键字来创建命名集 [ChardonnayChablis]，以及该命名集如何使得 SELECT 语句更简洁、更易于维护。
WITH SET [ChardonnayChablis] AS
{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chardonnay],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chablis Wine],
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chablis Wine]}
SELECT
[ChardonnayChablis] ON COLUMNS,
{Measures.[Unit Sales]} ON ROWS
FROM Sales
将函数与 WITH 关键字一起使用
尽管可以显式定义命名集的每个成员，但是这种方法可能会产生过长的语句。若要使命名集的创建和维护更简单，可以使用 MDX 函数来定义成员。
例如，下面的 MDX 查询示例使用 Filter、CurrentMember 和 Name MDX 函数以及 InStr VBA 函数创建 [ChardonnayChablis] 命名集。这种版本的 [ChardonnayChablis] 命名集与本主题前面所演示的显式定义的命名集相同。
WITH SET [ChardonnayChablis] AS
'Filter([Product].Members, (InStr(1, [Product].CurrentMember.Name, "chardonnay") <> 0) OR (InStr(1, [Product].CurrentMember.Name, "chablis") <> 0))'
SELECT
[ChardonnayChablis] ON COLUMNS,
{Measures.[Unit Sales]} ON ROWS
FROM Sales