在視圖中使用布爾欄位來辨識連結表

我正在使用 SQL Server 2019。我在客戶端使用 SQL Server Management Studio。

假設我有以下數據模型：

-- A record of the overall test of a  widget
CREATE TABLE [TestRecords]
(
  [TestRecord_ID] BIGINT NOT NULL,
  -- ...

  CONSTRAINT PK_TestRecords PRIMARY KEY ([TestRecord_ID])
);

-- A record of an individual subtest within the overall test
-- Any given subtest may be repeated N times
CREATE TABLE [SubtestRuns]
(
  [TestRecord_ID] BIGINT NOT NULL,
  [TestType] VARCHAR(50) NOT NULL,

  -- [RunNumber] is unique within one overall test, but not across all time
  [RunNumber] INT NOT NULL,

  -- Fields common to all subtests
  -- ...

  CONSTRAINT PK_SubtestRuns PRIMARY KEY ([TestRecord_ID], [TestType], [RunNumber]),

  CONSTRAINT FK_SubtestRuns_TestRecord_ID
             FOREIGN KEY ([TestRecord_ID])
             REFERENCES [TestRecords]([TestRecord_ID])
);

-- This is the first of many subtest types
CREATE TABLE [Subtest_1]
(
  [TestRecord_ID] BIGINT NOT NULL,
  [RunNumber] INT NOT NULL,
  [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_1_TestType DEFAULT 'Subtest_1',

  -- Fields specific to this subtest type
  -- ...

  CONSTRAINT PK_Subtest_1 PRIMARY KEY ([TestRecord_ID], [RunNumber]),
  CONSTRAINT CHK_Subtest_1_TestType_EQ_Subtest_1 CHECK ([TestType] = 'Subtest_1'),

  CONSTRAINT FK_Subtest_1_TestRecord_ID_RunNumber_TestType
             FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber])
             REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber])
);

-- This is the second of many subtest types
CREATE TABLE [Subtest_2]
(
  [TestRecord_ID] BIGINT NOT NULL,
  [RunNumber] INT NOT NULL,
  [TestType] VARCHAR(50) NOT NULL CONSTRAINT DF_Subtest_2_TestType DEFAULT 'Subtest_2',

  -- Fields specific to this subtest type
  -- ...

  CONSTRAINT PK_Subtest_2 PRIMARY KEY ([TestRecord_ID], [RunNumber]),
  CONSTRAINT CHK_Subtest_2_TestType_EQ_Subtest_2 CHECK ([TestType] = 'Subtest_2'),

  CONSTRAINT FK_Subtest_2_TestRecord_ID_RunNumber_TestType
             FOREIGN KEY ([TestRecord_ID], [TestType], [RunNumber])
             REFERENCES [SubtestRuns]([TestRecord_ID], [TestType], [RunNumber])
);

-- And so on for other subtest types...

我現在想創建一個與 相同[TestRecords]但添加布爾列以指示每個子測試類型的數據是否存在的視圖。

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
  [TestRecords].[TestRecord_ID],
  -- Remaining [TestRecords] fields
  -- ...

  [Has_Subtest_1_Data] BIT NOT NULL,
  [Has_Subtest_2_Data] BIT NOT NULL,
  -- ...

  FROM [TestRecords]
  -- ...

我無法弄清楚如何為計算[Has_Subtest_N_data]欄位分配值。我如何填充這些欄位？

編輯以通過左外連接上的計算欄位添加嘗試

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
  [T0].[TestRecord_ID],
  -- Remaining [TestRecords] fields
  -- ...

  -- These lines yield this error:
  -- Incorrect syntax near '.'.
  [Has_Subtest_1_Data] AS [T1].[TestRecord_ID] IS NOT NULL,
  [Has_Subtest_2_Data] AS [T2].[TestRecord_ID] IS NOT NULL,
  -- ...

  FROM [TestRecords] AS [T0]
  LEFT OUTER JOIN [Subtest_1] AS [T1]
  ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID]
  LEFT OUTER JOIN [Subtest_2] AS [T2]
  ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID]

  -- ...

如內聯註釋中所示，這會產生語法錯誤。

T-SQL 在語法上是否可以做我正在嘗試的事情？

就像是：

CREATE VIEW v AS (
   SELECT t.TestRecord_ID
        , ...
        , s1.TestRecord_ID IS NOT NULL AS Has_Subtest_1_Data
        , s2.TestRecord_ID IS NOT NULL AS Has_Subtest_2_Data
        , ...
   FROM TestRecords t
   LEFT JOIN Subtest_1 s1
       ON ...
   LEFT JOIN Subtest_2 s2
       ON ... 
   ... 

編輯：SQL 伺服器似乎不允許布爾表達式。添加了 CASE 表達式

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
  [T0].[TestRecord_ID],

  CASE WHEN [T1].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_1_Data,
  CASE WHEN [T2].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_2_Data

  FROM [TestRecords] AS [T0]
  LEFT OUTER JOIN [Subtest_1] AS [T1]
      ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID]
  LEFT OUTER JOIN [Subtest_2] AS [T2]
      ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID];

小提琴

引用自：https://dba.stackexchange.com/questions/312201