更新SQL子查询

Question

我有一个巨大的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 )

有人能帮我把这些合并成代码吗？

谢谢

Answer 1

您可以使用额外的左外部联接和使用供应商编号的联接并选择列。附加的派生表被命名为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_]