Как использовать конкретную таблицу в соединении при условии?

ProgramBox

Как использовать конкретную таблицу в соединении при условии?

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

#sql #sql-server #tsql

Вопрос:

Я хочу написать хранимую процедуру, включающую входные параметры, временные таблицы и объединения.

У меня есть AllocationDetails временная таблица в этой процедуре:

 ;WITH AllocationDetails AS
(
    SELECT
        lastPaymentAllocationDetails.*
    FROM   
        (SELECT 
             *, 
             ROW_NUMBER() OVER (PARTITION BY [PaymentAllocationGuid] ORDER BY [Year] DESC, [WorkingPeriodTitle] DESC) rowNumber
         FROM 
             ret_vwPaymentAllocationDetails) lastPaymentAllocationDetails
WHERE lastPaymentAllocationDetails.rowNumber = 1
),

Тогда я хотел бы иметь еще одну временную таблицу — вот так:

 ValidPaymentAllocations AS
(
    SELECT
        paymentAllocationDetails.[PaymentAllocationGuid],
        paymentAllocation.[RetiredPersonnelGuid],
        MAX(paymentAllocationDetails.ID) AS MaxPaymentAllocationDetailID,
        MAX(paymentAllocationDetails.[Year] * 100   paymentAllocationDetails.[WorkingPeriodTitle]) AS [WorkingPeriodKey]
    FROM
        ret_PaymentAllocation AS paymentAllocation 
    INNER JOIN
        CASE when(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] 
    INNER JOIN
        ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] 
    INNER JOIN
        (SELECT MAX(ID) ID FROM ret_PaymentAllocationDetails GROUP BY PaymentAllocationGuid,WorkingPeriodTitle,Year) lastRecord ON LastRecord.Id = paymentAllocationDetails.ID
    WHERE
        ISNULL(paymentAllocation.[Deleted],0) = 0 AND
        paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
        (paymentAllocationDetails.[Year]  * 100   paymentAllocationDetails.[WorkingPeriodTitle] <= @Year * 100   @WorkingPeriodID AND paymentAllocationDetails.[Status] = 1 AND paymentTypes.[PaymentTypeFlag] <> 3 OR
         paymentAllocationDetails.[Year]  * 100   paymentAllocationDetails.[WorkingPeriodTitle] = @Year * 100   @WorkingPeriodID AND paymentTypes.[PaymentTypeFlag] = 3) AND
        (paymentAllocation.[PaymentCommandType] = @PaymentCommandType OR paymentAllocation.[PaymentTypesGuid] IN (SELECT * FROM com_udfSplit(@PaymentTypeGuids,','))) AND
        ((paymentTypes.[AffectOnAmountNature] = 1) OR (paymentTypes.[AffectOnAmountNature] = 2 AND paymentAllocation.[Amount] > 0))
    GROUP BY paymentAllocation.[RetiredPersonnelGuid], paymentAllocationDetails.[PaymentAllocationGuid]
),

Но в разделе «Присоединиться» при одном условии @PaymentCommandType = 1 я хочу использовать ret_PaymentAllocationDetails вместо "AllocationDetails" этого (временная таблица выше):

 CASE WHEN(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid]

Я использовал СЛУЧАЙ, КОГДА, но это не сработало. Как решить эту проблему?

В этом и заключается вся процедура:

 CREATE PROCEDURE [dbo].[ret_PayrollCalculations_GetPayrollItems]
(
    @SalariedGuid uniqueidentifier,
    @Year int,
    @WorkingPeriodID int,
    @PaymentCommandType int,
    @PaymentTypeGuids nvarchar(max),
    @PayrollItemGuid uniqueidentifier
)
 AS
IF(ISNULL(@PayrollItemGuid, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' AND @PaymentCommandType = 1)
    SELECT @PayrollItemGuid = [Guid] FROM ret_PayrollItemPatterns WHERE [PaymentCommandType] = 1 AND ISNULL([Deleted], 0) = 0 AND [Active] = 1
;WITH AllocationDetails
AS
(
    SELECT
        lastPaymentAllocationDetails.*
    FROM (
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY [PaymentAllocationGuid] ORDER BY [Year] DESC, [WorkingPeriodTitle] DESC) rowNumber
    FROM ret_vwPaymentAllocationDetails
) lastPaymentAllocationDetails
WHERE lastPaymentAllocationDetails.rowNumber = 1
), ValidPaymentAllocations AS(
    SELECT
        paymentAllocationDetails.[PaymentAllocationGuid],
        paymentAllocation.[RetiredPersonnelGuid],
        MAX(paymentAllocationDetails.ID) AS MaxPaymentAllocationDetailID,
        MAX(paymentAllocationDetails.[Year] * 100   paymentAllocationDetails.[WorkingPeriodTitle]) AS [WorkingPeriodKey]
    FROM
        ret_PaymentAllocation AS paymentAllocation INNER JOIN
        CASE when(@PaymentCommandType = 1) THEN ret_PaymentAllocationDetails  ELSE AllocationDetails END AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] INNER JOIN
        ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] INNER JOIN
        (SELECT MAX(ID) ID FROM ret_PaymentAllocationDetails GROUP BY PaymentAllocationGuid,WorkingPeriodTitle,Year) lastRecord ON LastRecord.Id     = paymentAllocationDetails.ID
    WHERE
        ISNULL(paymentAllocation.[Deleted],0) = 0 AND
        paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
        (paymentAllocationDetails.[Year]  * 100   paymentAllocationDetails.[WorkingPeriodTitle] <= @Year * 100   @WorkingPeriodID AND paymentAllocationDetails.[Status] = 1 AND paymentTypes.[PaymentTypeFlag] <> 3 OR
         paymentAllocationDetails.[Year]  * 100   paymentAllocationDetails.[WorkingPeriodTitle] = @Year * 100   @WorkingPeriodID AND paymentTypes.[PaymentTypeFlag] = 3) AND
        (paymentAllocation.[PaymentCommandType] = @PaymentCommandType OR paymentAllocation.[PaymentTypesGuid] IN (SELECT * FROM com_udfSplit(@PaymentTypeGuids,','))) AND
        ((paymentTypes.[AffectOnAmountNature] = 1) OR (paymentTypes.[AffectOnAmountNature] = 2 AND paymentAllocation.[Amount] > 0))
    GROUP BY paymentAllocation.[RetiredPersonnelGuid], paymentAllocationDetails.[PaymentAllocationGuid]
), LastAmounts AS (
SELECT
    payrollCalculationDetails.[ItemGuid],
    SUM(payrollCalculationDetails.[Amount]) AS [Amount]
FROM
    ret_PayrollCalculationCommands payrollCalculationCommands INNER JOIN
    ret_PayrollCalculations payrollCalculations ON payrollCalculationCommands.[Guid] = payrollCalculations.[CalculationCommandGuid] INNER JOIN
    ret_PayrollCalculationDetails payrollCalculationDetails ON payrollCalculations.Guid = payrollCalculationDetails.[CalculationGuid]
WHERE 
    payrollCalculationCommands.[Hidden] = 0 AND
    payrollCalculationCommands.[Approved] = 1 AND
    payrollCalculations.[SalariedGuid] = @SalariedGuid AND
    payrollCalculations.[WorkingPeriodID] = @WorkingPeriodID AND
    payrollCalculations.[Year] = @Year
GROUP BY 
    payrollCalculationDetails.[ItemGuid]
)
SELECT
    paymentAllocation.[Guid],
    1 AS ItemType,
    paymentAllocation.[PaymentCommandType],
    '' AS ParameterName,
    paymentAllocationDetails.[Status],
    paymentAllocationDetails.[Amount],
    paymentAllocation.[Coffer],
    paymentTypes.[AffectOnSlipNature] AS [AffectOnSlip],
    paymentTypes.[AffectOnAmountNature] AS [AffectOnAmount],
    paymentTypes.[EffectiveOnSlip],
    paymentTypes.[BackPayable],
    0 AS [Reversible],
    lastAmounts.Amount AS LastAmount
FROM
    ret_PaymentAllocation AS paymentAllocation INNER JOIN
    ret_PaymentAllocationDetails AS paymentAllocationDetails ON paymentAllocation.[Guid] = paymentAllocationDetails.[PaymentAllocationGuid] INNER JOIN
    ValidPaymentAllocations AS validPaymentAllocations ON 
    paymentAllocationDetails.[PaymentAllocationGuid] = validPaymentAllocations.[PaymentAllocationGuid] AND 
    paymentAllocationDetails.[Year] * 100   paymentAllocationDetails.WorkingPeriodTitle = validPaymentAllocations.[WorkingPeriodKey] AND 
    paymentAllocationDetails.ID = validPaymentAllocations.MaxPaymentAllocationDetailID INNER JOIN
    ret_PaymentTypes AS paymentTypes ON paymentAllocation.[PaymentTypesGuid] = paymentTypes.[Guid] LEFT JOIN 
    LastAmounts lastAmounts ON lastAmounts.ItemGuid = paymentAllocation.Guid
WHERE
    ISNULL(paymentAllocation.Active,0) = 1 AND
    paymentAllocation.[RetiredPersonnelGuid] = @SalariedGuid AND
    paymentAllocationDetails.[Status] = 1 /* Status.Save */
UNION ALL
SELECT
    payrollItems.[Guid],
    2 AS [ItemType],
    payrollItemPatterns.[PaymentCommandType],
    payrollItems.[ParameterName],
    0 AS [Status],
    0 AS [Amount],
    0 AS [Coffer],
    payrollItems.[Nature] AS [AffectOnSlip],
    0 AS [AffectOnAmount],
    payrollItems.[EffectiveOnSlip],
    payrollItems.[BackPayable],
    payrollItems.[Reversible],
    lastAmounts.Amount AS LastAmount
FROM
    ret_PayrollItemPatterns AS payrollItemPatterns INNER JOIN
    ret_PayrollItemPatternDetails AS PID ON payrollItemPatterns.[Guid] = PID.[PayrollItemPatternGuid] INNER JOIN
    ret_PayrollItems AS payrollItems ON PID.[PayrollItem] = payrollItems.ID LEFT JOIN 
    LastAmounts lastAmounts ON lastAmounts.ItemGuid = payrollItems.Guid
WHERE
    (payrollItemPatterns.[PaymentCommandType] = @PaymentCommandType AND payrollItemPatterns.[Guid] IN (@PayrollItemGuid))

1. Вы не можете «параметризовать» объединение таблицы, вам придется объединить обе таблицы, а затем использовать ОБЪЕДИНЕНИЕ в выборе, чтобы выбрать нужные значения в соответствии с тем, что @PaymentCommandType установлено

2. Вы говорите о временных таблицах, но в вашем SQL их нет. Мы что-то здесь упускаем?

3. Pet peeve также ; является терминатором утверждений, он находится в конце всех ваших утверждений, а не в начале тех, для которых требуется правильное завершение предыдущего утверждения.

4. Большая проблема возникнет, если одно соединение завершится неудачно, так как это приведет к удалению строки для другого соединения. В этом случае используйте левое соединение

5. «Я изложил всю процедуру в вопросе». Однако временных таблиц по- прежнему нет. Возможно, у вас сложилось впечатление, что Общее табличное выражение (CTE) и Временная таблица-это одно и то же? Это не так; они очень разные. CTE, как следует из его названий, — это выражение. Временная таблица-это фактический физический объект, который будет храниться в tempdb течение всего срока его существования. То, как они работают «под колпаком», и то, как они ведут себя, очень разные.

Ответ №1:

Вот объяснение теории с использованием надуманного примера (потому что это проще, чем пытаться понять вашу конкретную ситуацию и написать запрос, который использует ваши точные таблицы — надеюсь, вы сможете перевести этот совет в то, что хотите)

Представьте, что у вас есть 3 таблицы: Человек, рабочий адрес, домашний адрес. Мы проигнорируем замечания о том, что два разных адреса должны находиться в одной таблице с дискриминатором для этого надуманного примера

Вы хотите передать параметр, определяющий, какие данные будут возвращены-рабочие или домашние

Вы не можете этого сделать:

 SELECT * 
FROM
  Person p
  JOIN CASE WHEN @what = 'work' THEN WorkAddress ELSE HomeAddress END x
    ON p.Id = x.PersonId

Хорошая попытка, но вы не можете «вернуть всю таблицу» из случая, когда

Вы должны присоединиться к обоим, а затем выбрать в разделе «Выбор» то, что вы хотите:

 SELECT 
  p.*,
  CASE WHEN @what = 'work' THEN w.Building ELSE h.Building END as Building,
  CASE WHEN @what = 'work' THEN w.Street ELSE h.Street END as Street,
  ...
FROM
  Person p
  JOIN WorkAddress w ON p.Id = w.PersonId
  JOIN HomeAddress h ON p.Id = h.PersonId

Если адрес отсутствует, вам нужно присоединиться к нему СЛЕВА, в противном случае отсутствующий (рабочий) адрес приведет к тому, что весь человек и (доступный) домашний адрес исчезнут из результатов.

Ларну обоснованно указывает, что объединение СЛЕВА позволит вам поместить параметр в предложение ON, что может позволить вашей базе данных оптимизировать его (никогда не выполняйте объединение, если сравнение параметров ложно).

 SELECT 
  p.*,
  COALESCE(w.Building, h.Building) as Building,
  COALESCE(w.Street, h.Street) as Street,
  ...
FROM
  Person p
  LEFT JOIN WorkAddress w ON p.Id = w.PersonId AND @what = 'work'
  LEFT JOIN HomeAddress h ON p.Id = h.PersonId AND @what = 'home'

Charlieface alo обоснованно считает, что вам вообще не следует этого делать; сделайте «ЕСЛИ/ИНАЧЕ», если вы пишете хранимую процедуру, поскольку это самый надежный способ быть уверенным в планировании запросов и оптимизации производительности, которые будут иметь место. Вы можете использовать CTE для частей, общих для обоих запросов, чтобы упростить чтение всего текста

1. Хм, это сработало бы для отношений один к одному, но не один ко многим. Если бы у кого-то, например, было 2 домашних адреса, и вы запросили Рабочий адрес, вы получили бы 2 (идентичные) строки. Если бы у кого-то было 2 домашних и 2 рабочих адреса, то у вас получилось бы 4 строки, о которых вы когда-либо просили.

2. @Larnu это именно моя проблема, несмотря на то, что я выбрал Max(Id) для решения этой проблемы, он возвращает две строки и все испортил.

3. @mehrabhabibi для легкой жизни убедитесь, что «таблицы», к которым вы присоединяетесь, имеют одну строку. Я уверен, что вы знаете, как это сделать, потому что ваш вопрос уже содержит ROW_NUMBER() OVER ... WHERE ronwumber=1 CTE

4. @Larnu Если вы проверите вопрос и комментарии, он определяет объем ответа: я прокомментировал «вы не можете этого сделать, сделайте это», ОП сказал «можете ли вы объяснить больше», я написал ответ, расширяющий «сделайте это». Если OP последовательно сталкивается с декартовым взрывом, с которым (необъяснимо, учитывая, что они демонстрируют способность справляться) они не могут справиться, это отдельный вопрос. Этот вопрос касается исключительно того, почему JOIN CASE WHEN x THEN t1 ELSE t2 END это не работает и как это может быть; мы не должны позволять вопросам становиться набором постоянно меняющихся целей, пока мы не напишем всю систему управления аэропортом OP.

5. @mehrabhabibi (но если вы не можете этого сделать; задайте, пожалуйста, новый вопрос.. это отдельная проблема по сравнению с тем, о чем вы спрашиваете здесь, которая является синтаксической ошибкой, которая вообще не позволяет вашему SQL работать)

Метки: Как использовать конкретную таблицу в соединении при условии?