6.9、union、intersect、exception

复杂集合的处理union、intersect、exception

 

 

1          语法

Union(set1, set2[,All])，返回两个集合合并后的集合

Intersect(set1, set2[,All])，返回两个集合交叉部分的集合

Exception(set1, set2[,All])，返回set1中与set2不交叉部分的集合

 

2          Union示例

select
         {
                   [Measures].[In Count],
                   [Measures].[Out Count]
         }on 0
         ,union(
                   topCount(
                            [Date].[YearNum].&[2015]*[Date].[Month Num Of Year].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2015])
                   ),
                   topCount(
                            [Date].[YearNum].&[2016]*[Date].[Month Num Of Year].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2016])
                   )
         )
         on 1
from
         [Park]

         查询2015年和2016年inCount排前三名的月份

 

3          Intersect示例

select
         {
                   [Measures].[In Count],
                   [Measures].[Out Count]
         }on 0
         ,intersect(
                   topCount(
                            [Date].[Month Num OfYear].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2015])
                   ),
                   topCount(
                            [Date].[Month Num OfYear].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2016])
                   )
         )
         on 1
from
         [Park]

         查询2015年和2016年的inCount都排名前三的月份

 

4          Except示例

select
         {
                   [Measures].[In Count],
                   [Measures].[Out Count]
         }on 0
         ,except(
                   topCount(
                            [Date].[Month Num OfYear].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2016])
                   ),
                  topCount(
                            [Date].[Month Num OfYear].[Month Num Of Year].members,
                            3,
                            ([Measures].[InCount], [Date].[Year Num].&[2015])
                   )
         )
         on 1
from
         [Park]

         查询2016年inCount排前三，且不在2015年inCount排前三的月份