Блог

Я пытаюсь использовать @variable1 @variable2 в запросе, но на самом деле результат равен 0.

Версия сервера MariaDB: 10.2.21

 set @start_at = '2019-01-01';
set @end_at = '2019-01-16';
set @receivable = 0;
set @invoiced = 0;
SELECT DISTINCT Customer.custnr 'Customer Number',
                Address.name    'Name',
                @receivable := sum(case
                      WHEN [condition1 <= @start_at]
                        AND Transactions.`key` not in [subquery]
                        THEN Transactions.amount
                      ELSE 0 END) 'Account Receivable',
                @invoiced := sum(case
                      WHEN [condition1 between @start_at and @end_at]
                        AND [condition2]
                        AND [condition3]
                        AND Transactions.`key` not in [subquery]
                        THEN Transactions.amount
                      ELSE 0 END) 'Invoiced',
                @receivable   @invoiced 'Total'
FROM LocalCust
       INNER JOIN Customer
                  on Customer.`key` = LocalCust.customerkey
       INNER JOIN Address
                  on Address.`key` = Customer.addresskey
       INNER JOIN Location
                  on Location.`key` = LocalCust.localkey
       INNER JOIN Transactions
                  on Transactions.localcustkey = LocalCust.`Key`
GROUP BY Transactions.localcustkey;
  

Результат:

Используйте подзапрос и вообще не используйте переменные:

 SELECT x.*, (Account_Receivable   Invoice) as Total
FROM (SELECT c.custnr as Customer_Number, a.name, 
             sum(case when condition1 <= @start_at and
                           t.`key` not in [subquery]
                      then t.amount
                      else 0 
                 end) as Account_Receivable,
             sum(case when condition1 between @start_at and @end_at and
                           [condition2] and
                           [condition3] and
                           t.`key` not in [subquery]
                      then t.amount
                      else 0 
                 end) as Invoiced
      FROM LocalCust lc JOIN
           Customer c
           on c.`key` = lc.customerkey JOIN
           Address a
           on a.`key` = c.addresskey JOIN
           Location l
           on l.`key` = lc.localkey join
           Transactions t
           on t.localcustkey = lc.`Key`
      GROUP BY c.custnr, a.name
     ) x;
  

Примечания:

• Псевдонимы таблиц облегчают запись и чтение запроса.
• SELECT DISTINCT почти никогда не требуется с GROUP BY .
• GROUP BY Ключи должны соответствовать неагрегированным столбцам в SELECT .
• Выберите псевдонимы столбцов, которые не нужно экранировать. То есть без пробелов.

Вы не можете просто поместить @receivable, @invoiced и @receivable @invoiced в одну и ту же инструкцию select. (Они не будут сохранять значение по порядку. Они будут выполнены одновременно.)

Сначала вам нужно сохранить значения в @receivable, @invoiced затем использовать подзапрос для вычисления общей суммы:

 set @start_at = '2019-01-01';
set @end_at = '2019-01-16';
set @receivable = 0;
set @invoiced = 0;

SELECT *, A.[Account Receivable]   A.[Invoiced]  AS TOTAL FROM (
SELECT DISTINCT Customer.custnr 'Customer Number',
                Address.name    'Name',
                @receivable := sum(case
                      WHEN [condition1 <= @start_at]
                        AND Transactions.`key` not in [subquery]
                        THEN Transactions.amount
                      ELSE 0 END) 'Account Receivable',
                @invoiced := sum(case
                      WHEN [condition1 between @start_at and @end_at]
                        AND [condition2]
                        AND [condition3]
                        AND Transactions.`key` not in [subquery]
                        THEN Transactions.amount
                      ELSE 0 END) 'Invoiced'
FROM LocalCust
       INNER JOIN Customer
                  on Customer.`key` = LocalCust.customerkey
       INNER JOIN Address
                  on Address.`key` = Customer.addresskey
       INNER JOIN Location
                  on Location.`key` = LocalCust.localkey
       INNER JOIN Transactions
                  on Transactions.localcustkey = LocalCust.`Key`
GROUP BY Transactions.localcustkey) A;