Question

我有这四张桌子:

SELECT [B_Key]
  ,[B_FiscalYear]
  ,[B_OrgCode]
  ,[B_SubObject]
  ,[B_Explanation]
  ,[B_CIPrefNo]
  ,[B_OrgBudgetAmt]
  ,[B_BudgetAmt]
  ,[B_Initials]
FROM [NAOLI].[dbo].[BudgetTbl]

SELECT [F_Fykey]
  ,[F_FiscalYear]
  ,[F_Year]
FROM [NAOLI].[dbo].[codeFiscalYearTbl]

SELECT [O_OrgKey]
  ,[O_OrgCode]
  ,[O_OrgDesc]
  ,[O_Divisions]
FROM [NAOLI].[dbo].[codeOrgCodeTbl]

SELECT [S_SubKey]
  ,[S_SubObject]
  ,[S_SubDescrip]
FROM [NAOLI].[dbo].[codeSubObjectTbl]

我要综合这些表格中不同部分的信息,以便编制以下的表格:

   [B_FiscalYear]
  ,[O_OrgCode]
  ,[O_OrgDesc]
  ,[S_SubObject]
  ,[S_SubDescrip]
  ,[B_BudgetAmt]
  ,[B_Initials]
  ,[B_CIPrefNo]
  ,[B_OrgBudgetAmt]

我尝试了下面的查询, 但是它返回了 20 750 个记录中的 0 个。如何完成?

SELECT [B_FiscalYear]
  ,[B_OrgCode]
  ,[O_OrgDesc]
  ,[B_SubObject]
  ,[S_SubDescrip]
  ,[B_BudgetAmt]
  ,[B_Initials]
  ,[B_CIPrefNo]
  ,[B_OrgBudgetAmt]

INTO dbo.BudgetsTbl
  FROM [BudgetTbl] BT, [codeFiscalYearTbl] FY, [codeOrgCodeTbl] OC, [codeSubObjectTbl] SO
WHERE BT.B_FiscalYear = FY.F_Year and BT.B_OrgCode = OC.O_OrgCode and BT.B_SubObject = SO.S_SubObject

Answer 1

正确的合并语法是 :

SELECT [B_FiscalYear], [B_OrgCode], [O_OrgDesc], [B_SubObject], [S_SubDescrip],
       [B_BudgetAmt], [B_Initials], [B_CIPrefNo], [B_OrgBudgetAmt]
INTO dbo.BudgetsTbl
FROM BudgetTbl BT join
     codeFiscalYearTbl FY
     on BT.B_FiscalYear = FY.F_Year  join
     codeOrgCodeTbl OC
     on BT.B_OrgCode = OC.O_OrgCode join
     codeSubObjectTbl SO
     on BT.B_SubObject = SO.S_SubObject

假设您查找的表格中有一个或多个为空。如果您想要全部行, 请在上述查询中将“ join” 替换为“ left exter join ” 。

Answer 2

联合起来就应该这样:

SELECT [B_FiscalYear]
  ,[B_OrgCode]
  ,[O_OrgDesc]
  ,[B_SubObject]
  ,[S_SubDescrip]
  ,[B_BudgetAmt]
  ,[B_Initials]
  ,[B_CIPrefNo]
  ,[B_OrgBudgetAmt]
FROM BudgetTbl BT
JOIN codeFiscalYearTbl FY ON BT.B_FiscalYear = FY.F_Year
JOIN codeOrgCodeTbl OC ON BT.B_OrgCode = OC.O_OrgCode
JOIN codeSubObjectTbl SO ON BT.B_SubObject = SO.S_SubObject

You can look at http://sqlfiddle.com/#!3/8ff6b/7 for more info. Also you can add left joins if needed.

友情链接