SQLServer2008数据库学习笔记之MDX查询示例
SQL Server 2008数据库学习笔记之MDX查询示例是本文我们主要要介绍的，我们首先介绍了SQL查询与MDX查询示例比较，然后介绍了MDX的实例，接下来就让我们一起来了解一下这部分内容吧。
成都创新互联公司10多年企业网站制作服务;为您提供网站建设,网站制作,网页设计及高端网站定制服务,企业网站制作及推广,对玻璃贴膜等多个方面拥有多年的网站维护经验的网站建设公司。
SQL查询与MDX查询示例比较
在MdxStepByStep数据库中（后缀名为.SQL）查询
- USE MdxStepByStep
- SELECT
- m.EnglishProductName, o.TotalSalesAmount
- FROM dbo.DimProduct m
- INNER JOIN ( -- TOP 10 PRODUCTS OF 2003
- SELECT TOP 10
- a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
- FROM (
- SELECT
- x.productkey, x.salesamount
- FROM dbo.FactInternetSales x
- INNER JOIN dbo.DimDate y
- ON x.OrderDateKey=y.DateKey
- WHERE y.CalendarYear=2003
- UNION ALL
- SELECT
- x.productkey, x.salesamount
- FROM dbo.FactResellerSales x
- INNER JOIN dbo.DimDate y
- ON x.OrderDateKey=y.DateKey
- WHERE y.CalendarYear=2003
- ) a
- GROUP BY a.ProductKey
- ORDER BY TotalSalesAmount DESC
- ) n
- ON m.ProductKey=n.ProductKey
- LEFT OUTER JOIN ( --PRODUCT SALES IN 2004
- SELECT
- a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
- FROM (
- SELECT
- x.productkey, x.salesamount
- FROM dbo.FactInternetSales x
- INNER JOIN dbo.DimDate y
- ON x.OrderDateKey=y.DateKey
- WHERE y.CalendarYear=2004
- UNION ALL
- SELECT
- x.productkey, x.salesamount
- FROM dbo.FactResellerSales x
- INNER JOIN dbo.DimDate y
- ON x.OrderDateKey=y.DateKey
- WHERE y.CalendarYear=2004
- ) a
- GROUP BY a.ProductKey
- ) o
- ON m.ProductKey=o.productkey
- ORDER BY n.TotalSalesAmount DESC
- /*
- EnglishProductName TotalSalesAmount
- Mountain-200 Black, 38 1327957.4077
- Mountain-200 Black, 42 1139429.4872
- Mountain-200 Silver, 38 1136622.4928
- Mountain-200 Black, 46 1011074.3685
- Mountain-200 Black, 38 NULL
- Mountain-200 Silver, 42 1011486.1762
- Touring-1000 Blue, 60 835290.1559
- Road-350-W Yellow, 48 897217.9635
- Mountain-200 Silver, 46 1029170.7639
- Road-350-W Yellow, 40 840970.6467
- */
而在维度数据库中对应的MDX查询（后缀名为.MDX）如下：
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- ([Measures].[Internet Sales Amount]) + ([Measures].[Reseller Sales Amount])
- SET [Top 10 Products of 2003] AS
- TOPCOUNT(
- {[Product].[Product].[Product].Members},
- 10,
- ([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003])
- )
- SELECT
- {([Measures].[Total Sales Amount])} ON COLUMNS,
- {[Top 10 Products of 2003]} ON ROWS
- FROM [Step-by-Step]
- WHERE ([Date].[Calendar Year].[CY 2004]);
- /*
- Total Sales Amount
- Mountain-200 Black, 38 $1,327,957.41
- Mountain-200 Black, 42 $1,139,429.49
- Mountain-200 Silver, 38 $1,136,622.49
- Mountain-200 Black, 46 $1,011,074.37
- Mountain-200 Black, 38 (null)
- Mountain-200 Silver, 42 $1,011,486.18
- Touring-1000 Blue, 60 $835,290.16
- Road-350-W Yellow, 48 $897,217.96
- Mountain-200 Silver, 46 $1,029,170.76
- Road-350-W Yellow, 40 $840,970.65
- */
从上面的这个例子我们领略了SQL与MDX语法的大致区别。
用MDX查询编辑器实现一个最简单的MDX的编写
在SSMS界面登录Analysis Services，选择MDX Step By Step数据库：
对于MDX新手来说，最令人鼓舞的莫过于大部分MDX查询都可以借助拖拽来实现。
大家可以参考以下的MDX脚本完成本文开始提供的MDX查询。
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- [Measures].[Internet Sales Amount]
- SELECT
- FROM [Step-by-Step]
- WHERE [Date].[Calendar Year].&[2004];
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- [Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
- SELECT
- FROM [Step-by-Step]
- WHERE [Date].[Calendar Year].&[2004]
- ;
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- [Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
- SET [Top 10 Products of 2003] AS
- TOPCOUNT( [Product].[Product],10)
- SELECT
- FROM [Step-by-Step]
- WHERE [Date].[Calendar Year].&[2004];
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- [Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
- SET [Top 10 Products of 2003] AS
- TOPCOUNT( [Product].[Product].[Product].Members, 10,
- ([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003]))
- SELECT
- FROM [Step-by-Step]
- WHERE [Date].[Calendar Year].&[2004];
- WITH
- MEMBER [Measures].[Total Sales Amount] AS
- [Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
- SET [Top 10 Products of 2003] AS
- TOPCOUNT( [Product].[Product].[Product].Members, 10,
- ([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003]))
- SELECT
- {([Measures].[Total Sales Amount])} ON COLUMNS,
- {[Top 10 Products of 2003]} ON ROWS
- FROM [Step-by-Step]
- WHERE [Date].[Calendar Year].&[2004];
关于SQL Server 2008数据库学习笔记之MDX查询的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获！
分享名称：SQLServer2008数据库学习笔记之MDX查询示例
本文URL：http://www.shufengxianlan.com/qtweb/news12/91462.html
网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联