SQL查询 - 添加第三表困难。继续遇到无限错误

Question

SELECT 
    List_Type_ID, ListTypeName,
    SUM(ListData.LIST_ID) AS ListCount,
    SUM(IsListOpen) AS TotalListOpen,
    AVG(ListNumberOfDaysToComplete) AS AvgListNumberToComplete,
    0 AS TotalOpenListCount
INTO 
    #TempListData
FROM 
    (SELECT 
         LIST_Type.cmb_Desc AS ListTypeName,
         LIST_Type.LIST_Type_ID,
         CASE WHEN List.LIST_ID IS NULL THEN 0 ELSE 1 END AS List_ID, 
         CASE WHEN (List.list_DateListClosed IS NULL AND list_Status <> 'Closed') THEN 1 ELSE 0 END AS IsListOpen,
         ISNULL(DATEDIFF(dd, List.list_DateListOpened,  List.list_DateListClosed) + 1, 0) AS ListNumberOfDaysToComplete
     FROM 
         LIST_Type 
     LEFT JOIN 
         (SELECT 
              List_Id, List_Type_Id, list_DateListOpened, 
              list_DateListClosed, list_Status
          FROM 
              List 
          WHERE 
              List.list_Status <> 'Deleted' 
              AND List.SITE_ID IN (1, 15, 34, 35, 2, 12, 4, 9, 10, 13, 18, 19, 36, 38, 11, 14, 16, 17, 33, 40)) AS List
                ON LIST_Type.LIST_Type_ID = List.LIST_Type_ID
     WHERE 
         LIST_Type.cmb_Status = 'Active') AS ListData 
GROUP BY 
    List_Type_Id, ListTypeName

我正在尝试添加第三个表，项目。应该包含在此中

CASE WHEN List.LIST_ID IS NULL THEN 0 ELSE 1 END AS List_ID
        , CASE WHEN (List.list_DateListClosed IS NULL AND list_Status <> 'Closed') THEN 1 ELSE 0 END AS IsListOpen

也应该具有project。创建不是null，

但是每当我添加项目表时，我都无法工作，

这是我尝试的SQL查询：

 SELECT List_Type_ID, ListTypeName
            , SUM(ListData.LIST_ID) AS ListCount
            , SUM(IsListOpen) AS TotalListOpen
            , AVG(ListNumberOfDaysToComplete) AS AvgListNumberToComplete
            , 0 AS TotalOpenListCount
            INTO #TempListData14
            FROM (SELECT LIST_Type.cmb_Desc AS ListTypeName
            , LIST_Type.LIST_Type_ID 
            , CASE WHEN List.LIST_ID IS NULL THEN 0 ELSE 1 END AS List_ID
            , CASE WHEN (List.list_DateListClosed IS NULL AND list_Status <> 'Closed' AND Project.CREATED IS NOT NULL) THEN 1 ELSE 0 END AS IsListOpen
            , ISNULL(DATEDIFF(dd, List.list_DateListOpened, List.list_DateListClosed) + 1, 0) AS ListNumberOfDaysToComplete
            FROM LIST_Type LEFT JOIN (SELECT List.List_Id, List_Type_Id, list_DateListOpened, list_DateListClosed, list_Status
                FROM List JOIN PROJECT ON List.LIST_ID = PROJECT.LIST_ID
                WHERE List.list_Status <> 'Deleted' AND List.SITE_ID IN(1, 15, 34, 35, 2, 12, 4, 9, 10, 13, 18, 19, 36, 38, 11, 14, 16, 17, 33, 40)) AS List
            ON LIST_Type.LIST_Type_ID = List.LIST_Type_ID
            WHERE LIST_Type.cmb_Status = 'Active' ) AS ListData 
            GROUP BY List_Type_Id, ListTypeName

创建表：

list_type： 设置ANSI_NULLS 去

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LIST_Type](
    [LIST_Type_ID] [int] IDENTITY(1,1) NOT NULL,
    [LIST_Type_GUID] [uniqueidentifier] ROWGUIDCOL  NULL,
    [RecordDateTime] [datetime] NULL,
    [RecordSource_ID] [int] NULL,
    [RecordSource_FKey] [varchar](255) NULL,
    [SITE_ID] [int] NULL,
 CONSTRAINT [PK_LIST_Type] PRIMARY KEY CLUSTERED 
(
    [LIST_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LIST_Type] ADD  CONSTRAINT [DF_LIST_Type_LIST_Type_GUID]  DEFAULT (newid()) FOR [LIST_Type_GUID]
GO

列表： 设置ANSI_NULLS 去

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LIST](
    [LIST_ID] [int] IDENTITY(1,1) NOT NULL,
    [LIST_GUID] [uniqueidentifier] ROWGUIDCOL  NULL,
    [RecordDateTime] [datetime] NULL,
    [RecordSource_ID] [int] NULL,
    [RecordSource_FKey] [varchar](255) NULL,
    [JOB_ID] [int] NULL,
    [PROJECT_ID] [int] NULL,
    [SITE_ID] [int] NULL,
    [list_RecType] [varchar](25) NULL,
    [list_Status] [varchar](25) NULL,
    [list_ListNumber] [varchar](50) NULL,
    [list_DateListClosed] [datetime] NULL,
    [list_DateListOpened] [datetime] NULL,

 CONSTRAINT [PK_LIST] PRIMARY KEY CLUSTERED 
(
    [LIST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[LIST] ADD  CONSTRAINT [DF_LIST_LIST_GUID]  DEFAULT (newid()) FOR [LIST_GUID]
GO

项目：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PROJECT](
    [PROJECT_ID] [int] IDENTITY(1,1) NOT NULL,
    [PROJECT_GUID] [uniqueidentifier] ROWGUIDCOL  NULL,
    [Created] [datetime] NULL,
    [RecordSource_ID] [int] NULL,
    [RecordSource_FKey] [varchar](255) NULL,
    [PROJECT_Area_ID] [int] NULL,
    [PROJECT_Determination_ID] [int] NULL,
    [JOB_ID] [int] NULL,
    [LIST_ID] [int] NULL,
    [SITE_ID] [int] NULL,
    [VENDOR_ID] [int] NULL,
    [PROJECT_ID] [int] NULL,
    [PROJECT_RecType] [varchar](14) NULL,
    [PROJECT_Status] [int] NULL,
    [PROJECT_ListNumber] [varchar](50) NULL,
    [PROJECT_PROJECTNumber] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_WSODetail] PRIMARY KEY CLUSTERED 
(
    [PROJECT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[PROJECT] ADD  CONSTRAINT [DF_PROJECT_PROJECT_GUID]  DEFAULT (newid()) FOR [PROJECT_GUID]
GO