首页 文章

如何在SQL Server 2014中创建视图以查看缺少某些数据的交叉引用表中的数据？

提问于
浏览 次
0

我正在尝试使用表a，a_xref_b和b从标准化数据库生成报告 .

CREATE TABLE a (a_rid INT primary key, a1 varchar(20), );

CREATE TABLE b (b_rid INT primary key, b1 varchar(20), b2 varchar(20));

CREATE TABLE a_xref_b (a_rid INT, b_rid INT,xref_type int
CONSTRAINT fk_a FOREIGN KEY (a_rid) REFERENCES a (a_rid),
CONSTRAINT fk_b FOREIGN KEY (b_rid) REFERENCES b (b_rid));

INSERT INTO a VALUES (1,'John'), (2,'Sue')
INSERT INTO b VALUES (1,'Atlanta','GA'), (2,'Macon','GA'), (3,'Opp','AL')
INSERT INTO a_xref_b VALUES (1,1,1), (1,2,2), (2,3,1)

表A和表B是交叉引用的，并且交叉引用具有一种类型，它定义了B中的数据代表什么 .

场景 - 约翰被提供一个主要城市和一个替代 . 苏只提供了一个主要城市 .

我正在尝试编写一个视图来加载具有名称，主要城市和备用页面的页面 .

Name   b_rid_t1  City_t1   St_1  b_rid_2   City_t2  St_t2
John   1         Atlanta   GA    2         Macon    GA
Sue    3         Opp       AL    NULL      NULL     NULL

我试过这个

select
   a.a1,
   b_1.b_rid,
   b_1.b1,
   b_1.b2,
   b_2.b_rid,
   b_2.b1,
   b_2.b2      
from
   a     
left join
   a_xref_b as xf1 
      on a.a_rid = xf1.a_rid      
inner join
   b as b_1 
      on xf1.b_rid = b_1.b_rid 
      and xf1.xref_type = 1     
left join
   a_xref_b xf2 
      on a.a_rid = xf2.a_rid      
inner join
   b as b_2 
      on xf2.b_rid = b_2.b_rid 
      and xf2.xref_type = 2

然而它降低了苏的记录 . 如果我将它从外部参照更改为左边连接到b，那么我会得到重复的记录 .

真实场景是与地址表交叉引用的联系表，外部参照表具有类型字段 . 我们正在尝试构建一个加载页面的视图 .

sql sql-server tsql

3 回答

  • 0

    试试这个，你应该用a_xref_b表连接添加xref_type条件 .

    select
   a.a1,
   b_1.b_rid,
   b_1.b1,
   b_1.b2,
   b_2.b_rid,
   b_2.b1,
   b_2.b2      
from
   a     
left join a_xref_b as xf1 on a.a_rid = xf1.a_rid      
   and xf1.xref_type = 1     
left join b as b_1 on xf1.b_rid = b_1.b_rid       
left join a_xref_b xf2 on a.a_rid = xf2.a_rid      
   and xf2.xref_type = 2
left join b as b_2 on xf2.b_rid = b_2.b_rid
    回复于
  • 0

    您需要使用数据透视表或使用提供相同类型结果的SQL语句 . 例如：

    ;WITH    Main
          AS ( SELECT   a1 AS Name
                       ,b1 AS City
                       ,b2 AS Code
                       ,xref_type
               FROM     a_xref_b
                        LEFT JOIN a ON a_xref_b.a_rid = a.a_rid
                        LEFT JOIN b ON a_xref_b.b_rid = b.b_rid
             )
    SELECT  Name
           ,MAX(CASE WHEN xref_type = 1 THEN City
                END) FirstCity
           ,MAX(CASE WHEN xref_type = 1 THEN Code
                END) FirstCode
           ,MAX(CASE WHEN xref_type = 2 THEN City
                END) AlternateCity
           ,MAX(CASE WHEN xref_type = 2 THEN Code
                END) AlternateCode
    FROM    Main
    GROUP BY Name
    回复于
  • 1

    另一种方法可能是使用Union：

    select max(name) name, max(id1) id1, max(city1) city1, max(state1) state1, max(id2) id2, max(city2) city2, max(state2) state2
from(
    select a.a1 as name, xf1.b_rid id1, xf1.b1 city1, xf1.b2 state1, null id2, null city2, null state2
    from a left join 
    (select a_rid, b1, b2, b.b_rid from a_xref_b inner join b on a_xref_b.b_rid = b.b_rid 
    where xref_type = 1) xf1 on a.a_rid = xf1.a_rid 
    union
    select a.a1 as name, null id1, null city1, null state1,xf1.b_rid id2, xf1.b1 city2, xf1.b2 state2
    from a left join 
    (select a_rid, b1, b2, b.b_rid from a_xref_b inner join b on a_xref_b.b_rid = b.b_rid 
    where xref_type = 2) xf1 on a.a_rid = xf1.a_rid 
)t group by name
    回复于

相关问题