>玩家(tblPlayer)拥有0-N英雄(tblBattleTarget tblHero).
>玩家可以在0-N族(tblMembershipPlayer2PlayerClan).
>玩家可以与同一个战队中的其他玩家分享英雄(tblHero.Sharing = 2).
>英雄可以由所有者或其他玩家控制,其中所有者是成员,英雄可以分享.
我创建了查询:
SELECT H.HeroID /*PK of hero*/ , BT.IDBattleTargetOwner /*ID of owner of hero (player)*/ , MP2PC_Other.IDPlayer AS IDOtherPlayerByClan /*ID of another player, which can control hero*/ FROM [dbo].[tblPlayer] AS P_Owner /*owner of heroes*/ INNER JOIN [dbo].[tblBattleTarget] AS BT /*"base class" for hero*/ ON BT.IDBattleTargetOwner = P_Owner.PlayerID INNER JOIN [dbo].[tblHero] AS H /*hero in game*/ ON H.HeroID = BT.BattleTargetID INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Owner /*hero's owner can be in 0-N clans*/ ON MP2PC_Owner.IDPlayer = BT.IDBattleTargetOwner INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Other /*other players can be in 0-N clans*/ ON MP2PC_Other.IDPlayerClan = MP2PC_Owner.IDPlayerClan WHERE H.Sharing = [dbo].[CONST_Sharing_PlayerClan]() /*only heroes shared with clan can be in result*/
但是,当我尝试在视图上创建索引时,我收到错误:
无法在视图“mydatabase.dbo.vwHero_SharingWithClan”上创建索引.该视图包含“mydatabase.dbo.tblMembershipPlayer2PlayerClan”上的自联接. (Microsoft SQL Server,错误:1947)
我搜索了网络和sql server在线预订,我没有找到解决自加入问题的方法.有什么方法可以做到这一点?或者我应该如何重写查询以获得正确的结果?
谢谢!
有关如何重新构建查询以解决此问题的一些提示,请参阅此链接Creating an Indexed View with a Self-Join (Kinda) .