MDX查询.如何解决"IN"问题?在“哪里"部分 [英] MDX queries. How can to solve "IN" in "where" part
问题描述
我有sql查询,我需要在olap cube中执行此查询.
I have sql query and i need this query execute in olap cube .
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m
inner join [dbo].[MenuItemOlds] as mi
on mi.id = m.MenuItemId
inner join [dbo].[RestaurantlistItems] as rl
on rl.RestaurantId = mi.RestaurantId
where m.AttributeId = 31 and rl.RestaurantListId = 69 and mi.PeriodId = 99 and m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6
并且我有有效的mdx查询,并且我需要为该查询添加运算符'IN'或其他解决方案
and i have working mdx query and I need to add operator 'IN' or something another solution for this query
SELECT CROSSJOIN(
{[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},
{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS,
{[Menu Item Olds].[id]} ON ROWS
FROM [MTD DEV]
where (
{[Restaurant Lists].[Id].[69]},
{[Attributes].[Id].[6]} ,
{[Attribute Categories].[Id].[5]} -- or can use the same parameter {[Attributes].[Id].[31]}
)
为了更好地理解: https://drive.google.com/file/d/0B3rw0YPItJIIa3FfNEtrVC04SVU/view ?usp =分享
for better understanding: https://drive.google.com/file/d/0B3rw0YPItJIIa3FfNEtrVC04SVU/view?usp=sharing
对问题的其他注释 n
在ms sql中,我必须通过某些参数m.AttributeId = 31来切片MenuItemOlds 然后从结果中我必须再次对参数AttributeId = 6进行切片. 在Sql中,它看起来像这样:
In ms sql I have to slice MenuItemOlds by some parameter m.AttributeId = 31 annd then from result I have to slice again for parameter AttributeId = 6. In Sql it looks like this:
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m
inner join [dbo].[MenuItemOlds] as mi on mi.id = m.MenuItemId
where m.AttributeId = 31 and m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6
我在OLAP Cube中遇到问题. 我如何解决这个问题:
I have problem in OLAP Cube. How I see to solve this problem :
1.我得到AttributeId = 31的所有数据
1.I get all data where AttributeId = 31
SELECT CROSSJOIN(
{[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},
{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS,
{[Menu Item Olds].[id]} ON ROWS
FROM [MTD DEV]
where ({[Attributes].[Id].[31]})
此操作的结果-所有餐饮菜单项
the result of this - all catering menu items
- 此后,在此菜单项集合中,我需要找到所有菜单项,其中{[Attributes].[Id].[6]}(儿童菜单)
当我尝试执行此类查询时:
When i am trying to execute such query :
SELECT CROSSJOIN(
{[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},
{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS,
{[Menu Item Olds].[id]} ON ROWS
FROM [MTD DEV]
where (
{[Attributes].[Id].[6]} ,
{[Attributes].[Id].[31]}
)
我得到result,其中我的菜单项带有AttributeId.[6] +具有attributeId的menuItem.[31] 例如:
I get result , in which i have Menu items with AttributeId.[6] + menuItem with attributeId.[31] for example:
具有AttributeId的菜单项数.[6] = 11000项
count of menu items with AttributeId.[6] = 11000 items
具有AttributeId的菜单项数.[31] = 724000项
count of menu items with AttributeId.[31] = 724000items
结果是724000 + 11000 = 735000,但我不需要它
and result is 724000+11000 = 735000 but i don`t need it
我需要找到所有带有AttributeId.[31]的项目,在此集合中,我需要找到带有AttributeId.[6]的项目. 正确的查询结果必须少于11000个项目
i need to find all items with AttributeId.[31], and in this collection i need to find items with AttributeId.[6] The right result of query must be less than 11000 items
推荐答案
带有intersect
的NonEmpty
是否可以替代?
SELECT
{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
*
{
[Periods].[Id].[99]
,[Periods].[Id].[93]
,[Periods].[Id].[75]
} ON COLUMNS
,Intersect
(
NonEmpty
(
[Menu Item Olds].[id].[id].MEMBERS
,(
[Attributes].[Id].[31]
,{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
)
)
,NonEmpty
(
[Menu Item Olds].[id].[id].MEMBERS
,(
[Attributes].[Id].[6]
,{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
)
)
) ON ROWS
FROM [MTD DEV];
这篇关于MDX查询.如何解决"IN"问题?在“哪里"部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!