包含多个表的SQL Server 2008子查询

Question

我有4张桌子

表1-图纸提交-DTS

SUBMISSION-ID, 
DRAWING-ID,
INWARD-ID,
REVISION NO,
COMMENT DATE,
COMMENTS FORWARDED TO WED,
STATUS OF DRWG,
RECVD AT FFM,
REMARKS,
Last,
Remarks2,
Drawing Sheet,

表2-绘图-主要-DTS

DRAWING-ID,
DRAWING CSD NO,
DRAWING TITLE,
ENGINEER INCHARGE,

表3- LI项目

PROJECT TITLE,
LI PROJECT NO,

表4-向内

INWARD-ID,
REFERENCE NO,
LI PROJECT NO,
TYPE OF INCOMING DOC,

所有的表都与该表中的一个字段相关。

表4中的参考编号、表2中的图纸CSD编号和表3中的LI项目编号是唯一的。

当绘图CSD号将具有新的版本号时，将生成新的参考号。在该特定图纸CSD号的最后参考号上检查图纸CSD号是否延迟。

我想要表4中的最后一个参考号，表1中FFM的设计和接收状态，表2中的工程师inCharge和图纸标题作为结果。我们将只提供LI项目编号作为输入。

Answer 1

你也许可以使用一个简单的拖放“Access风格”的UI来实现这一点(不过我很少建议你这样做，除非是完全初学者)。我猜[LI PROJECT NO]是一个整数字段..。

CREATE PROC dbo.GetLIProjectInfo (@LIProjNo INT)
AS
-- I got this from stackoverflow.com, see http://stackoverflow.com/questions/12559023/sql-server-2008-subquery-with-multiple-tables
BEGIN
   SELECT TOP (1)
      i.[REFERENCE NO],
      ds.[STATUS OF DRWG],
      ds.[RECVD AT FFM],
      dm.[DRAWING TITLE],
      dm.[ENGINEER INCHARGE]
   FROM dbo.[DRAWING SUBMISSIONS-DTS] AS ds
   JOIN dbo.[DRAWING-MAIN-DTS] AS dm
     ON dm.[DRAWING-ID] = ds.[DRAWING-ID]
   JOIN dbo.[Inward] AS i
     ON i.[INWARD-ID] = ds.[INWARD-ID]
   JOIN dbo.[LI PROJECTS] AS p
     ON p.[LI PROJECT NO] = i.[LI PROJECT NO]
   WHERE i.[LI PROJECT NO] = @LIProjNo
   ORDER BY i.[REFERENCE NO] DESC;
END

编辑：

对于每个[DRAWING CSD NO]表的一行，需要决定是希望LIProjNo过滤器过滤图形列表还是仅过滤项目列表。

CREATE PROC dbo.GetLIProjectInfo (@LIProjNo INT)
AS
-- I got this from stackoverflow.com, see http://stackoverflow.com/questions/12559023/sql-server-2008-subquery-with-multiple-tables
BEGIN
   SELECT *
   FROM 
   (SELECT DISTINCT [DRAWING CSD NO]
     FROM dbo.[DRAWING-MAIN-DTS]) AS csd
   OUTER APPLY --Could be CROSS APPLY if you need
   (SELECT TOP (1)
      i.[REFERENCE NO],
      ds.[STATUS OF DRWG],
      ds.[RECVD AT FFM],
      dm.[DRAWING TITLE],
      dm.[ENGINEER INCHARGE]
   FROM dbo.[DRAWING SUBMISSIONS-DTS] AS ds
   JOIN dbo.[DRAWING-MAIN-DTS] AS dm
     ON dm.[DRAWING-ID] = ds.[DRAWING-ID]
   JOIN dbo.[Inward] AS i
     ON i.[INWARD-ID] = ds.[INWARD-ID]
   JOIN dbo.[LI PROJECTS] AS p
     ON p.[LI PROJECT NO] = i.[LI PROJECT NO]
   WHERE i.[LI PROJECT NO] = @LIProjNo
   AND dm.[DRAWING CSD NO] = csd.[DRAWING CSD NO]
   ORDER BY i.[REFERENCE NO] DESC) as inf
   ORDER BY csd.[DRAWING CSD NO];
END

编辑：

因为您只需要与提供的项目相关的图形：

CREATE PROC dbo.GetLIProjectInfo (@LIProjNo INT)
AS
-- I got this from stackoverflow.com, see http://stackoverflow.com/questions/12559023/sql-server-2008-subquery-with-multiple-tables
BEGIN
   WITH Numbered AS (
   SELECT 
      ROW_NUMBER() OVER (PARTITION BY dm.[DRAWING CSD NO] ORDER BY i.[REFERENCE NO] DESC) AS RowNum,
      i.[REFERENCE NO],
      ds.[STATUS OF DRWG],
      ds.[RECVD AT FFM],
      dm.[DRAWING TITLE],
      dm.[ENGINEER INCHARGE]
   FROM dbo.[DRAWING SUBMISSIONS-DTS] AS ds
   JOIN dbo.[DRAWING-MAIN-DTS] AS dm
     ON dm.[DRAWING-ID] = ds.[DRAWING-ID]
   JOIN dbo.[Inward] AS i
     ON i.[INWARD-ID] = ds.[INWARD-ID]
   JOIN dbo.[LI PROJECTS] AS p
     ON p.[LI PROJECT NO] = i.[LI PROJECT NO]
   WHERE i.[LI PROJECT NO] = @LIProjNo
   )
   SELECT *
   FROM Numbered
   WHERE RowNum = 1;
END