Question

我有三个表格: Acount , Monitoring 和 MonitorAcountLook 。

账户表包含 places 。管理表包含业务等级体系中每个级别records records 。管理表还包含一个值( levelID) ,该值表示记录在等级体系中跌落到哪个级别。管理账户图表是连接这些级别的查找表。

我无法写一个查询来获取所有账户和两个相关的管理记录

例如:一个账户可能有5个或5个以上的相关管理记录,但我只关心两个特定的管理层,它们具有品牌或区域等分级码。此外,我只想要一个账户在结果网格中出现一次。

结果组应该看起来是这样的:

AccountID Brand Region --------- -------- ------ account1 Wendys East US account2 McDonalds West US

这似乎是一个简单的问题,但我一直无法确切地知道如何取得这一结果。我尝试了自我欢乐、子节节和其他我能想到的事物,但我似乎无法将结果一排地纳入其中。

任何帮助都将不胜感激。

*EDIT: ManagementAccountLookup has two fields (AccountID, ManagementID). Those are the PKs of the two other tables.
Management has a column LevelID which is how you can tell if the record is a Brand, Region, District, etc...

品牌与区域将在管理表内分为两行。我需要结果网格才能将结果网格放在同一行中。

Answer 1

您需要添加两次管理账户查看/ 管理组合, 才能在一行中获取信息。我已直接将 < code> levelID 标准连接到一起, 以便于在出现需要时向左接线过渡。

select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
 inner join ManagementAccountLookup mal_brand
    on Account.AccountID = mal_brand.AccountID
 inner join Management m_brand
    on mal_brand.ManagementID = m_brand.ManagementID
   and m_brand.LevelID = @Insert_Management_Brand_Level_Here
 inner join ManagementAccountLookup mal_region
    on Account.AccountID = mal_region.AccountID
 inner join Management m_region
    on mal_region.ManagementID = m_region.ManagementID
   and m_region.LevelID = @Insert_Management_Region_Level_Here

<强度>EDIT: ,如果您需要显示所有账户,您可以在括号中使用左括号/内连接组合键:

select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
  left join 
  (
       ManagementAccountLookup mal_brand
       inner join Management m_brand
         on mal_brand.ManagementID = m_brand.ManagementID
        and m_brand.LevelID = @Insert_Management_Brand_Level_Here
 )
   on Account.AccountID = mal_brand.AccountID
 left join 
 (
       ManagementAccountLookup mal_region
       inner join Management m_region
          on mal_region.ManagementID = m_region.ManagementID
         and m_region.LevelID = @Insert_Management_Region_Level_Here
 )
    on Account.AccountID = mal_region.AccountID

为了让它更易读,你可以使用CTE:

; with mal_level as (
  select AccountID,
         m.LevelID,
         m.Name
    from ManagementAccountLookup mal
   inner join Management m
      on mal.ManagementID = m.ManagementID
)
select Account.AccountID,
       m_brand.Name Brand,
       m_region.Name Region
  from Account
  left join mal_level m_brand
    on Account.AccountID = m_brand.AccountID
   and m_brand.LevelID = @Insert_Management_Brand_Level_Here
  left join mal_level m_region
    on Account.AccountID = m_region.AccountID
   and m_region.LevelID = @Insert_Management_Region_Level_Here

或"http://msdn.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx" rel="nofollow">outer application :

select Account.AccountID,
       b.Brand,
       r.Region
  from Account
 outer apply
 (
       select m_brand.Name Brand
         from ManagementAccountLookup mal_brand
        inner join Management m_brand
           on mal_brand.ManagementID = m_brand.ManagementID
          and m_brand.LevelID = @Insert_Management_Brand_Level_Here
        where mal_brand.AccountID = Account.AccountID
 ) b
 outer apply
 (
       select m_region.Name Region
         from ManagementAccountLookup mal_region
        inner join Management m_region
           on mal_region.ManagementID = m_region.ManagementID
          and m_region.LevelID = @Insert_Management_Region_Level_Here
        where mal_region.AccountID = Account.AccountID
 ) r

友情链接