首页 文章

SQL在多个字段上连接多个表

提问于
浏览 次
-2

MS SQL Server .

我有10个表（table1，table2，table3等），我想离开加入它们并从每个表中获取一些字段 . 有些表需要在多个字段上保持连接，例如table1和table2需要在3个字段上保持连接 .

这是我尝试使用的脚本中的示例，但是我收到错误（我屏蔽了真实的表和字段名称）：

SELECT  table1.[Field1], 
        table1.[Field2],
        table1.[Field3],
        table1.[Field5],        
        table2.[Field1], 
        table2.[Field2], 
        table2.[Field3],  
        table3.[Field4],
            table4.[Field1], 
        table4.[Field4],
        table4.[Field5],        
        table5.[Field4],
FROM table2
LEFT JOIN table1 As a
    on a.[Field1] = table2.[Field1]
LEFT JOIN table1 As b 
    on b.[Field3] = table2.[Field3]
LEFT JOIN table1 As c 
    on c.[Field2] = table2.[Field2]
LEFT JOIN table4 As d 
    on table1.[Field5] = d.[Field4]
LEFT JOIN table4 As e 
    on table1.[Field1] = e.[Field1]
LEFT JOIN table5 
    on table4.[Field4] = table5.[Field4]
LEFT JOIN table3 
    on table4.[Field4] = table3.[Field4];

任何“简单”的方法吗？

sql sql-server join

2 回答

  • 1

    试试这个查询.it可以帮到你

    SELECT  table1.[Field1], 
        table1.[Field2],
        table1.[Field3],
        table1.[Field5],        
        table2.[Field1], 
        table2.[Field2], 
        table2.[Field3],  
        table3.[Field4], 
        table4.[Field4],
        table4.[Field5],        
        table5.[Field4],
FROM table2
LEFT JOIN table1 As a on a.[Field1] = table2.[Field1]
and a.[Field3] = table2.[Field3]
and a.[Field2] = table2.[Field2]
LEFT JOIN table4 As d on table1.[Field5] = d.[Field4]
and table1.[Field1] = d.[Field1]
LEFT JOIN table5 on d.[Field4] = table5.[Field4]
LEFT JOIN table3 on d.[Field4] = table3.[Field4];
    回复于
  • 1

    use NORTHWND;
    go

    select Orders.OrderID,Employees.EmployeeID,Employees.FirstName + ' ' +
    Employees.LastName as "Full Name",Customers.CompanyName as "Customer Company Name",
    Customers.ContactName as "Customers Contact Name", RegionDescription,Products.ProductName,[Order Details].UnitPrice, Suppliers.CompanyName as "Supplier Company Name"
    from Orders
    left join Customers
    on Orders.CustomerID=Customers.CustomerID
    left join Employees
    on Orders.EmployeeID=Employees.EmployeeID
    left join EmployeeTerritories
    on Employees.EmployeeID=EmployeeTerritories.EmployeeID
    join Territories
    on Territories.TerritoryID=EmployeeTerritories.TerritoryID
    join Region
    on region.RegionID=Territories.RegionID
    join [Order Details]
    on [Order Details].OrderID=Orders.OrderID
    join Products
    on Products.ProductID=[Order Details].ProductID
    join Categories
    on Categories.CategoryID=Products.CategoryID
    join Suppliers
    on Suppliers.SupplierID=products.SupplierID
    where categories.CategoryID=1;

    回复于

相关问题