代码之家 › 专栏 › 技术社区 › sofsntp

SQL Server与子表左联接并获取表名

sql-server sql

sofsntp · 技术社区 · 6 年前

假设我们有一个表工具,其中子表equity和bond具有一个外键instructID。每个仪器在其中一个子表中都有一个唯一的记录。

是否可以使用左联接创建视图,以查看包含记录所在表名的列的所有仪器?

SELECT        Instrument.InstrumentId, Instrument.Name, ***CHILD_TABLE_NAME***
FROM          Instrument
LEFT OUTER JOIN
              Equity ON Equity.InstrumentId = Instrument.InstrumentId 
LEFT OUTER JOIN
              Bond ON SBond.InstrumentId = Instrument.InstrumentId

另一种方法是进行内部连接的联合:

SELECT instrumentId, Name, instrumentType 
FROM
(SELECT Instrument.instrumentId, Name, 'Equity' as instrumentType FROM dbo.Equity inner join Instrument on Instrument.InstrumentId = Equity.InstrumentId
UNION
SELECT Instrument.instrumentId, Name, 'Bond' as instrumentType from dbo.Bond inner join Instrument on Instrument.InstrumentId = Bond.InstrumentId) u

2 回复 | 直到 6 年前

GuidoG 6 年前

一种选择是在这样的联接中包含表名

SELECT i.InstrumentId, 
       i.Name, 
       e.TableEquity,
       b.TableBond
FROM Instrument i 
  LEFT OUTER JOIN (select 'Equity' as TableEquity from Equity) e
    ON i.InstrumentId = e.InstrumentId 
  LEFT OUTER JOIN (select 'Bond' as TableBond from Bond) b
    ON i.InstrumentId = b.InstrumentId

由@sofsntp编辑:在一列中合并权益/债券

SELECT i.InstrumentId, 
       i.Name, 
       (ISNULL(e.TableEquity,'') + ISNULL(b.TableBond ,''))
FROM Instrument i 
  LEFT OUTER JOIN (select *, 'Equity' as TableEquity from Equity) e
   ON e.InstrumentId = i.InstrumentId 
 LEFT OUTER JOIN (select *, 'Bond' as TableBond  from StraightBond) b
   ON b.InstrumentId = i.InstrumentId

Gordon Linoff 6 年前

使用A case 表达式:

SELECT i.InstrumentId, i.Name, 
       (CASE WHEN e.InstrumentId IS NOT NULL THEN 'Equity'
             WHEN b.InstrumentId IS NOT NULL THEN 'Bond'
        END) as which_table
FROM Instrument i LEFT OUTER JOIN
     Equity e
     ON e.InstrumentId = i.InstrumentId LEFT OUTER JOIN
     Bond b
     ON b.InstrumentId = i.InstrumentId ;

注:这给出了第一匹配。如果两者都需要:

SELECT i.InstrumentId, i.Name, 
       ((CASE WHEN e.InstrumentId IS NOT NULL THEN 'Equity' ELSE '' END) + 
        (CASE WHEN b.InstrumentId IS NOT NULL THEN 'Bond' ELSE '' END)
       ) as which_table
FROM Instrument i LEFT OUTER JOIN
     Equity e
     ON e.InstrumentId = i.InstrumentId LEFT OUTER JOIN
     Bond b
     ON b.InstrumentId = i.InstrumentId ;

编辑我正在添加丢失的结尾