mysql - SQL语句从多个表中提取记录

Question

我试图从我的电子邮件服务器的 SQL 数据库中提取联系人。需要拉取用户、联系人和所有联系人详细信息。

但是，所有内容都在一个单独的表中。这就是我正在运行的:

SELECT [Contacts].[ID]
  ,[Users].[LoginName]
  ,[Contacts].[JobTitle]
  ,[Contacts].[Company]
  ,[Contacts].[WebPageAddress]
  ,[Contacts].[FirstName]
  ,[Contacts].[LastName]
  ,[EmailAddresses].[Address]
  ,[EmailAddresses].[Name]
  ,[Addresses].[Name] 
  ,[Addresses].[Address1]
  ,[Addresses].[Town]  
  ,[Addresses].[County]
  ,[Addresses].[Country] 
  ,[Addresses].[Postcode]
  ,[PhoneNumbers].[Name] 
  ,[PhoneNumbers].[Number]


FROM [WorkgroupShare].[dbo].[Contacts]
  INNER JOIN [WorkgroupShare].[dbo].[Users] 
  ON [WorkgroupShare].[dbo].[Contacts].[Owner]=[WorkgroupShare].[dbo].[Users].[ID]
  FULL OUTER JOIN [WorkgroupShare].[dbo].[EmailAddresses]
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= [WorkgroupShare].[dbo].[EmailAddresses].[OwnerID]
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses]
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= [WorkgroupShare].[dbo].[Addresses].[OwnerID]
  FULL OUTER JOIN [WorkgroupShare].[dbo].[PhoneNumbers]
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= [WorkgroupShare].[dbo].[PhoneNumbers].[OwnerID]
  order by [Contacts].[ID]

由于 EmailAddress.Name 中的 Name 字段包含 Email1 或 Email2，我得到每条记录的双重返回。 Address.Name 字段包含公司、家庭或其他我得到一个三元组。并且 PhoneNumbers.Name 包含 4 个字段，我得到四倍。然后你将它乘以一个 6000 个联系人的列表变成数万个。无法弄清楚如何编写循环或 while 语句。我想我有逻辑只是想不出语法。任何帮助将不胜感激。

Answer 1

如果您在每个名称字段中都有一组固定的值，那么您可以将它们硬编码到列中，这样您就可以为每个联系人获得 1 行，其中的列代表引用数据的每种“风格”。如下所示(我没有为 4 个电话号码添加它，因为在这种情况下您没有提供 Name 的可能值，但您应该能够理解):

SELECT [Contacts].[ID]
  ,[Users].[LoginName]
  ,[Contacts].[JobTitle]
  ,[Contacts].[Company]
  ,[Contacts].[WebPageAddress]
  ,[Contacts].[FirstName]
  ,[Contacts].[LastName]
  ,email1.[Address] as email1Address
  ,email2.[Address] as email2Address
  ,Address1.[Address1] as HomeAddress1
  ,Address1.[Town] as HomeAddressTown
  ,Address1.[County] as HomeAddressCounty
  ,Address1.[Country] as HomeAddressCountry
  ,Address1.[Postcode] as HomeAddressPostcode
  ,Address2.[Address1] as BusinessAddress1
  ,Address2.[Town] as BusinessAddressTown
  ,Address2.[County] as BusinessAddressCounty
  ,Address2.[Country] as BusinessAddressCountry
  ,Address2.[Postcode] as BusinessAddressPostcode
  ,Address3.[Address1] as OtherAddress1
  ,Address3.[Town] as OtherAddressTown
  ,Address3.[County] as OtherAddressCounty
  ,Address3.[Country] as OtherAddressCountry
  ,Address3.[Postcode] as OtherAddressPostcode
  ,[PhoneNumbers].[Name] 
  ,[PhoneNumbers].[Number]
FROM [WorkgroupShare].[dbo].[Contacts]
  INNER JOIN [WorkgroupShare].[dbo].[Users] 
  ON [WorkgroupShare].[dbo].[Contacts].[Owner]=[WorkgroupShare].[dbo].[Users].[ID]
  FULL OUTER JOIN [WorkgroupShare].[dbo].[EmailAddresses] email1
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= email1.[OwnerID] AND email1.[Name] = 'Email1'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[EmailAddresses] email2
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= email1.[OwnerID] AND email1.[Name] = 'Email2'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address1
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address1.[OwnerID] AND Address1.Name = 'Home'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address2
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address2.[OwnerID] AND Address2.Name = 'Business'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[Addresses] Address3
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= Address3.[OwnerID] AND Address3.Name = 'Other'
  FULL OUTER JOIN [WorkgroupShare].[dbo].[PhoneNumbers]
  ON [WorkgroupShare].[dbo].[Contacts].[ID]= [WorkgroupShare].[dbo].[PhoneNumbers].[OwnerID]
  order by [Contacts].[ID]