SQL, использующий два запроса с объединением

ProgramBox

SQL, использующий два запроса с объединением

Post author:admin
Запись опубликована:29 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

Я довольно новичок в SQL. Я пытался использовать два запроса и объединить их вместе, чтобы получить желаемый результат:

 select 
       A.[Article:Link]
      ,A.[ArtNo]
      ,A.[GenericArticle:Link]
      ,A.[Vehicle:Link]
      ,A.[LinkingTargetNo]
      ,Q1.[Name]
FROM [HellaPim].[dbo].[TecDoc.ArticleLinkage.Linkages] A WITH (NOLOCK) 
LEFT OUTER JOIN 
(SELECT 
       base.[TecDocNo], 
       base.[VehicleType],
       base.[TypeNo],
       T3.[Manufacturer:Link], 
       T24.[Name], 
       T1.[Designation], 
       T1.[LongDesignation], 
       T2.[DescriptionNo], 
       base.[Model:Link], 
       T4.[Designation], 
       base.[SortNo], 
       base.[ConstructionYearFrom], 
       base.[ConstructionYearTo], 
       base.[KW], 
       base.[PS], 
       base.[CcmTaxation], 
       base.[CcmTechnical], 
       base.[CapacityLitres], 
       base.[NumCylinders], 
       base.[NumDoors], 
       base.[FuelTankCapacity], 
       base.[MainVoltage], 
       base.[ABS], 
       base.[ASR], 
       base.[EngineType:Link], 
       T6.[Description <4>], 
       base.[FuelMixtureFormation:Link], 
       T8.[Description <4>], 
       base.[DriveType:Link], 
       T10.[Description <4>],  
       base.[BrakeType:Link], 
       T12.[Description <4>], 
       base.[BrakeSystem:Link], 
       T14.[Description <4>], 
       base.[NumValves], 
       base.[FuelType:Link], 
       T16.[Description <4>], 
       base.[CatalystConverterType:Link], 
       T18.[Description <4>], 
       base.[TransmissionType:Link], 
       T20.[Description <4>], 
       base.[BodyType:Link], 
       T22.[Description <4>]
FROM [HellaPim].[dbo].[TecDoc.LinkingTargets.Types] base WITH (NOLOCK)

  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models] T3 WITH (NOLOCK) 
               ON T3.[ModelNo] = [base].[Model:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers] T23 WITH (NOLOCK) 
               ON T23.[ManufacturerNo] = [T3].[Manufacturer:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers <TecDoc.GeneralData.Countries>] T24 WITH (NOLOCK) 
               ON T24.[:Id] = [T23].[:Id] 
               AND T24.[:TecDoc.GeneralData.Countries_Id] = 'A98E18B0-1401-4104-9EF9-827497E6407F'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Types <TecDoc.GeneralData.Countries>] T1 WITH (NOLOCK) 
               ON T1.[:Id] = [base].[:Id] 
               AND T1.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.CouLngDescriptions] T2 WITH (NOLOCK) 
               ON T2.[DescriptionNo] = [base].[Description:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models <TecDoc.GeneralData.Countries>] T4 WITH (NOLOCK) 
               ON T4.[:Id] = [T3].[:Id] 
               AND T4.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T5 WITH (NOLOCK) 
               ON T5.[KeyValueNo] = [base].[EngineType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T6 WITH (NOLOCK) 
               ON T6.[DescriptionNo] = [T5].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T7 WITH (NOLOCK) 
               ON T7.[KeyValueNo] = [base].[FuelMixtureFormation:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T8 WITH (NOLOCK) 
               ON T8.[DescriptionNo] = [T7].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T9 WITH (NOLOCK) 
               ON T9.[KeyValueNo] = [base].[DriveType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T10 WITH (NOLOCK) 
               ON T10.[DescriptionNo] = [T9].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T11 WITH (NOLOCK) 
               ON T11.[KeyValueNo] = [base].[BrakeType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T12 WITH (NOLOCK) 
               ON T12.[DescriptionNo] = [T11].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T13 WITH (NOLOCK) 
               ON T13.[KeyValueNo] = [base].[BrakeSystem:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T14 WITH (NOLOCK) 
               ON T14.[DescriptionNo] = [T13].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T15 WITH (NOLOCK) 
               ON T15.[KeyValueNo] = [base].[FuelType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T16 WITH (NOLOCK) 
               ON T16.[DescriptionNo] = [T15].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T17 WITH (NOLOCK) 
               ON T17.[KeyValueNo] = [base].[CatalystConverterType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T18 WITH (NOLOCK) 
               ON T18.[DescriptionNo] = [T17].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T19 WITH (NOLOCK) 
               ON T19.[KeyValueNo] = [base].[TransmissionType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T20 WITH (NOLOCK) 
               ON T20.[DescriptionNo] = [T19].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T21 WITH (NOLOCK) 
               ON T21.[KeyValueNo] = [base].[BodyType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T22 WITH (NOLOCK) 
               ON T22.[DescriptionNo] = [T21].[Name:Link]
) as Q1 on A.[LinkingTargetNo] = Q1.[TypeNo]

Моя ошибка

Сообщение 8156, уровень 16, состояние 1, строка 107 Столбец ‘Обозначение’ был указан несколько раз для ‘Q1’.

Два запроса по отдельности работают отлично. Проблема заключается в том, когда я объединяю их вместе. Как вы можете видеть, я передаю [Обозначение] два раза, но один раз из T1 и один раз из T4.

Чего мне не хватает?

Ответ №1:

используйте псевдоним для обозначения

 select 
       A.[Article:Link]
      ,A.[ArtNo]
      ,A.[GenericArticle:Link]
      ,A.[Vehicle:Link]
      ,A.[LinkingTargetNo]
      ,Q1.[Name]
FROM [HellaPim].[dbo].[TecDoc.ArticleLinkage.Linkages] A WITH (NOLOCK) 
LEFT OUTER JOIN 
(SELECT 
       base.[TecDocNo], 
       base.[VehicleType],
       base.[TypeNo],
       T3.[Manufacturer:Link], 
       T24.[Name], 
       T1.[Designation] as DesignationT1 , 
       T1.[LongDesignation], 
       T2.[DescriptionNo], 
       base.[Model:Link], 
       T4.[Designation], 
       base.[SortNo], 
       base.[ConstructionYearFrom], 
       base.[ConstructionYearTo], 
       base.[KW], 
       base.[PS], 
       base.[CcmTaxation], 
       base.[CcmTechnical], 
       base.[CapacityLitres], 
       base.[NumCylinders], 
       base.[NumDoors], 
       base.[FuelTankCapacity], 
       base.[MainVoltage], 
       base.[ABS], 
       base.[ASR], 
       base.[EngineType:Link], 
       T6.[Description <4>], 
       base.[FuelMixtureFormation:Link], 
       T8.[Description <4>], 
       base.[DriveType:Link], 
       T10.[Description <4>],  
       base.[BrakeType:Link], 
       T12.[Description <4>], 
       base.[BrakeSystem:Link], 
       T14.[Description <4>], 
       base.[NumValves], 
       base.[FuelType:Link], 
       T16.[Description <4>], 
       base.[CatalystConverterType:Link], 
       T18.[Description <4>], 
       base.[TransmissionType:Link], 
       T20.[Description <4>], 
       base.[BodyType:Link], 
       T22.[Description <4>]
FROM [HellaPim].[dbo].[TecDoc.LinkingTargets.Types] base WITH (NOLOCK)

  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models] T3 WITH (NOLOCK) 
               ON T3.[ModelNo] = [base].[Model:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers] T23 WITH (NOLOCK) 
               ON T23.[ManufacturerNo] = [T3].[Manufacturer:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers <TecDoc.GeneralData.Countries>] T24 WITH (NOLOCK) 
               ON T24.[:Id] = [T23].[:Id] 
               AND T24.[:TecDoc.GeneralData.Countries_Id] = 'A98E18B0-1401-4104-9EF9-827497E6407F'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Types <TecDoc.GeneralData.Countries>] T1 WITH (NOLOCK) 
               ON T1.[:Id] = [base].[:Id] 
               AND T1.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.CouLngDescriptions] T2 WITH (NOLOCK) 
               ON T2.[DescriptionNo] = [base].[Description:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models <TecDoc.GeneralData.Countries>] T4 WITH (NOLOCK) 
               ON T4.[:Id] = [T3].[:Id] 
               AND T4.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T5 WITH (NOLOCK) 
               ON T5.[KeyValueNo] = [base].[EngineType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T6 WITH (NOLOCK) 
               ON T6.[DescriptionNo] = [T5].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T7 WITH (NOLOCK) 
               ON T7.[KeyValueNo] = [base].[FuelMixtureFormation:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T8 WITH (NOLOCK) 
               ON T8.[DescriptionNo] = [T7].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T9 WITH (NOLOCK) 
               ON T9.[KeyValueNo] = [base].[DriveType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T10 WITH (NOLOCK) 
               ON T10.[DescriptionNo] = [T9].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T11 WITH (NOLOCK) 
               ON T11.[KeyValueNo] = [base].[BrakeType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T12 WITH (NOLOCK) 
               ON T12.[DescriptionNo] = [T11].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T13 WITH (NOLOCK) 
               ON T13.[KeyValueNo] = [base].[BrakeSystem:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T14 WITH (NOLOCK) 
               ON T14.[DescriptionNo] = [T13].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T15 WITH (NOLOCK) 
               ON T15.[KeyValueNo] = [base].[FuelType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T16 WITH (NOLOCK) 
               ON T16.[DescriptionNo] = [T15].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T17 WITH (NOLOCK) 
               ON T17.[KeyValueNo] = [base].[CatalystConverterType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T18 WITH (NOLOCK) 
               ON T18.[DescriptionNo] = [T17].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T19 WITH (NOLOCK) 
               ON T19.[KeyValueNo] = [base].[TransmissionType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T20 WITH (NOLOCK) 
               ON T20.[DescriptionNo] = [T19].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T21 WITH (NOLOCK) 
               ON T21.[KeyValueNo] = [base].[BodyType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T22 WITH (NOLOCK) 
               ON T22.[DescriptionNo] = [T21].[Name:Link]
) as Q1 on A.[LinkingTargetNo] = Q1.[TypeNo]

Ответ №2:

Вы можете попробовать ниже — у вас есть два столбца обозначений, один из которых — T1.[Обозначение], а другой — T4. [Обозначение] — один должен быть переименован

 select 
       A.[Article:Link]
      ,A.[ArtNo]
      ,A.[GenericArticle:Link]
      ,A.[Vehicle:Link]
      ,A.[LinkingTargetNo]
      ,Q1.[Name]
FROM [HellaPim].[dbo].[TecDoc.ArticleLinkage.Linkages] A WITH (NOLOCK) 
LEFT OUTER JOIN 
(SELECT 
       base.[TecDocNo], 
       base.[VehicleType],
       base.[TypeNo],
       T3.[Manufacturer:Link], 
       T24.[Name], 
       T1.[Designation] as designation1, 
       T1.[LongDesignation], 
       T2.[DescriptionNo], 
       base.[Model:Link], 
       T4.[Designation], 
       base.[SortNo], 
       base.[ConstructionYearFrom], 
       base.[ConstructionYearTo], 
       base.[KW], 
       base.[PS], 
       base.[CcmTaxation], 
       base.[CcmTechnical], 
       base.[CapacityLitres], 
       base.[NumCylinders], 
       base.[NumDoors], 
       base.[FuelTankCapacity], 
       base.[MainVoltage], 
       base.[ABS], 
       base.[ASR], 
       base.[EngineType:Link], 
       T6.[Description <4>], 
       base.[FuelMixtureFormation:Link], 
       T8.[Description <4>], 
       base.[DriveType:Link], 
       T10.[Description <4>],  
       base.[BrakeType:Link], 
       T12.[Description <4>], 
       base.[BrakeSystem:Link], 
       T14.[Description <4>], 
       base.[NumValves], 
       base.[FuelType:Link], 
       T16.[Description <4>], 
       base.[CatalystConverterType:Link], 
       T18.[Description <4>], 
       base.[TransmissionType:Link], 
       T20.[Description <4>], 
       base.[BodyType:Link], 
       T22.[Description <4>]
FROM [HellaPim].[dbo].[TecDoc.LinkingTargets.Types] base WITH (NOLOCK)

  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models] T3 WITH (NOLOCK) 
               ON T3.[ModelNo] = [base].[Model:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers] T23 WITH (NOLOCK) 
               ON T23.[ManufacturerNo] = [T3].[Manufacturer:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers <TecDoc.GeneralData.Countries>] T24 WITH (NOLOCK) 
               ON T24.[:Id] = [T23].[:Id] 
               AND T24.[:TecDoc.GeneralData.Countries_Id] = 'A98E18B0-1401-4104-9EF9-827497E6407F'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Types <TecDoc.GeneralData.Countries>] T1 WITH (NOLOCK) 
               ON T1.[:Id] = [base].[:Id] 
               AND T1.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.CouLngDescriptions] T2 WITH (NOLOCK) 
               ON T2.[DescriptionNo] = [base].[Description:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models <TecDoc.GeneralData.Countries>] T4 WITH (NOLOCK) 
               ON T4.[:Id] = [T3].[:Id] 
               AND T4.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T5 WITH (NOLOCK) 
               ON T5.[KeyValueNo] = [base].[EngineType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T6 WITH (NOLOCK) 
               ON T6.[DescriptionNo] = [T5].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T7 WITH (NOLOCK) 
               ON T7.[KeyValueNo] = [base].[FuelMixtureFormation:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T8 WITH (NOLOCK) 
               ON T8.[DescriptionNo] = [T7].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T9 WITH (NOLOCK) 
               ON T9.[KeyValueNo] = [base].[DriveType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T10 WITH (NOLOCK) 
               ON T10.[DescriptionNo] = [T9].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T11 WITH (NOLOCK) 
               ON T11.[KeyValueNo] = [base].[BrakeType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T12 WITH (NOLOCK) 
               ON T12.[DescriptionNo] = [T11].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T13 WITH (NOLOCK) 
               ON T13.[KeyValueNo] = [base].[BrakeSystem:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T14 WITH (NOLOCK) 
               ON T14.[DescriptionNo] = [T13].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T15 WITH (NOLOCK) 
               ON T15.[KeyValueNo] = [base].[FuelType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T16 WITH (NOLOCK) 
               ON T16.[DescriptionNo] = [T15].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T17 WITH (NOLOCK) 
               ON T17.[KeyValueNo] = [base].[CatalystConverterType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T18 WITH (NOLOCK) 
               ON T18.[DescriptionNo] = [T17].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T19 WITH (NOLOCK) 
               ON T19.[KeyValueNo] = [base].[TransmissionType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T20 WITH (NOLOCK) 
               ON T20.[DescriptionNo] = [T19].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T21 WITH (NOLOCK) 
               ON T21.[KeyValueNo] = [base].[BodyType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T22 WITH (NOLOCK) 
               ON T22.[DescriptionNo] = [T21].[Name:Link]
) as Q1 on A.[LinkingTargetNo] = Q1.[TypeNo]

Ответ №3:

Ваш запрос SELECT содержит T1.Define и T4.Define, и это может быть проблемой. Возможно, попробуйте добавить псевдоним к одному из них, например. T4.Обозначьте КАК Обозначение4.

Ответ №4:

Пожалуйста, задайте псевдонимы для каждого повторяющегося столбца в списке

вот так

 T4.[Designation] As T4Designation,
T6.[Description <4>] As T6Description,

и так далее.