Блог

У меня есть огромный код sql, который, должно быть, был обновлен еще на 2 столбца. Поскольку я новичок в sql, эти 4 подзапроса слишком важны для меня.

Может ли кто-нибудь помочь мне добавить еще два столбца?

Код SQL находится здесь:

 select 
  vnd.[Vendor Posting Group], 
  x.[No_], 
  x.SumOfAmount, 
  x.[SumOfAmount (EUR)], 
  x.[Siai Dienai], 
  vnd.[E-Mail],
  vnd.[Address 2],
  vnd.[Phone No_]    

from
  (
    select ldg.[No_],
      -Sum(dtl.Amount) AS SumOfAmount, 
      -Sum(dtl.[Amount (LCY)]) AS [SumOfAmount (EUR)] , 
      sd.[Siai Dienai]    
    from
      [DatabaseOne] ldg
      left join [DatabaseDetailed] dtl on ldg.[No_]=dtl.[Vendor No_]
      LEFT JOIN
      (
        SELECT
          ldg.[No_], 
          -Sum(dtl.[Amount]) AS [Siai Dienai] 
        from
          [DatabaseOne] ldg     
          left join [DatabaseDetailed] dtl on ldg.[No_]=dtl.[Vendor No_]  
        WHERE
          dtl.[Posting Date]<=cast(getdate() as date)  
        group by ldg.[No_]
     ) sd 
     on sd.[No_]=ldg.[No_]    

    where
      ldg.No_ not like 'KT%' and
      dtl.[Posting Date]<= '2020 – 07 - 31'  

    group by  
      ldg.[No_], 
      sd.[Siai Dienai] 
  ) x 
  LEFT JOIN 
  (
    SELECT 
      vnd.[No_], 
      vnd.[Vendor Posting Group], 
      vnd.[E-Mail], 
      vnd.[Address 2],
      vnd.[Phone No_], 
    FROM
      [DatabaseOne] vnd) vnd on vnd.[No_]=x.[No_]    
    where x.sumofamount is not null 
    order by x.[No_] 
  )   
  

Мне нужно добавить еще два столбца, скажем, x.One и x.Two.

Код для этих столбцов является:

 SELECT 
    [Vendor No_], 
    COUNT ([Amount (LCY)]) AS 'One', 
    SUM ([Amount (LCY)]) AS 'Two'
FROM 
    [DatabaseDetailed]
WHERE
    [Applied Vend_ Ledger Entry No_] = '0'
    AND [Posting Date] BETWEEN '2020-01-01' AND '2020-07-31' 
    AND [Document Type] >= '2'
GROUP BY
    [Vendor No_]    
        
  

Данные берутся из DatabaseDetailed (который используется как dtl в первом коде)

Кто-нибудь может помочь мне объединить их с кодами?

Спасибо

У вас может быть дополнительное ЛЕВОЕ ВНЕШНЕЕ соединение и соединение с использованием номера поставщика и выбора столбцов. Дополнительная производная таблица называется как x1 .

 SELECT vnd.[Vendor Posting Group]
    ,x.[No_]
    ,x.SumOfAmount
    ,x.[SumOfAmount (EUR)]
    ,x.[Siai Dienai]
    ,vnd.[E-Mail]
    ,vnd.[Address 2]
    ,vnd.[Phone No_]
    , x1.One -- additional column
    , x1.Two -- additional column
FROM (
    SELECT ldg.[No_]
        ,- Sum(dtl.Amount) AS SumOfAmount
        ,- Sum(dtl.[Amount (LCY)]) AS [SumOfAmount (EUR)]
        ,sd.[Siai Dienai]
    FROM [DatabaseOne] ldg
    LEFT JOIN [DatabaseDetailed] dtl ON ldg.[No_] = dtl.[Vendor No_]
    LEFT JOIN (
        SELECT ldg.[No_]
            ,- Sum(dtl.[Amount]) AS [Siai Dienai]
        FROM [DatabaseOne] ldg
        LEFT JOIN [DatabaseDetailed] dtl ON ldg.[No_] = dtl.[Vendor No_]
        WHERE dtl.[Posting Date] <= cast(getdate() AS DATE)
        GROUP BY ldg.[No_]
        ) sd ON sd.[No_] = ldg.[No_]
    WHERE ldg.No_ NOT LIKE 'KT%'
        AND dtl.[Posting Date] <= '2020 – 07 - 31'
    GROUP BY ldg.[No_]
        ,sd.[Siai Dienai]
    ) x
LEFT JOIN (
    SELECT vnd.[No_]
        ,vnd.[Vendor Posting Group]
        ,vnd.[E-Mail]
        ,vnd.[Address 2]
        ,vnd.[Phone No_]
        ,
    FROM [DatabaseOne] vnd
    ) vnd ON vnd.[No_] = x.[No_]
LEFT OUTER JOIN -- additional JOIN condition
(
SELECT [Vendor No_], COUNT([Amount (LCY)]) as 'One', SUM ([Amount (LCY)]) as 'Two'FROM 
[DatabaseDetailed]Where [Applied Vend_ Ledger Entry No_] = '0'
AND [Posting Date] BETWEEN '2020-01-01' AND '2020-07-31' 
AND [Document Type] >= '2'
Group by [Vendor No_] 
) AS x1 ON x1.[Vendor No_] = x.[No_]
WHERE x.sumofamount IS NOT NULL
ORDER BY x.[No_]
  

Чтобы все было еще более понятно, вы можете использовать общие табличные выражения и объединяться с ними.

 ; WITH x AS
(
SELECT ldg.[No_]
        ,- Sum(dtl.Amount) AS SumOfAmount
        ,- Sum(dtl.[Amount (LCY)]) AS [SumOfAmount (EUR)]
        ,sd.[Siai Dienai]
    FROM [DatabaseOne] ldg
    LEFT JOIN [DatabaseDetailed] dtl ON ldg.[No_] = dtl.[Vendor No_]
    LEFT JOIN (
        SELECT ldg.[No_]
            ,- Sum(dtl.[Amount]) AS [Siai Dienai]
        FROM [DatabaseOne] ldg
        LEFT JOIN [DatabaseDetailed] dtl ON ldg.[No_] = dtl.[Vendor No_]
        WHERE dtl.[Posting Date] <= cast(getdate() AS DATE)
        GROUP BY ldg.[No_]
        ) sd ON sd.[No_] = ldg.[No_]
    WHERE ldg.No_ NOT LIKE 'KT%'
        AND dtl.[Posting Date] <= '2020 – 07 - 31'
    GROUP BY ldg.[No_]
        ,sd.[Siai Dienai]
), vnd AS
(
SELECT vnd.[No_]
        ,vnd.[Vendor Posting Group]
        ,vnd.[E-Mail]
        ,vnd.[Address 2]
        ,vnd.[Phone No_]
        ,
    FROM [DatabaseOne] vnd
), x1 AS
(
SELECT [Vendor No_], COUNT([Amount (LCY)]) as 'One', SUM ([Amount (LCY)]) as 'Two'FROM 
[DatabaseDetailed]Where [Applied Vend_ Ledger Entry No_] = '0'
AND [Posting Date] BETWEEN '2020-01-01' AND '2020-07-31' 
AND [Document Type] >= '2'
Group by [Vendor No_] 
)
SELECT vnd.[Vendor Posting Group]
    ,x.[No_]
    ,x.SumOfAmount
    ,x.[SumOfAmount (EUR)]
    ,x.[Siai Dienai]
    ,vnd.[E-Mail]
    ,vnd.[Address 2]
    ,vnd.[Phone No_]
    , x1.One -- additional column
    , x1.Two -- additional column
FROM  x
LEFT OUTER JOIN vnd ON vnd.[No_] = x.[No_]
LEFT OUTER JOIN x1 ON x1.[Vendor No_] = x.[No_]
WHERE x.sumofamount IS NOT NULL
ORDER BY x.[No_]