對不同行的2列求和

我通過使用以下查詢從表中提取資料。

SELECT  ID ,Desc_Cars ,DocID ,TabID
    ,(Select Dist1 where TabID = 85)
    ,(Select Dist2 where TabID = 86) 
    ,(Select Days1 where TabID = 85)    
    ,(Select Days2 where TabID = 85)
    ,(Select Days3 where TabID = 86)    
FROM Details
      where   DocID = 16

我在 SQL 中有以下部分表：

 ID Desc_Cars   DocID   TabID   Dist1   Dist2   Days1   Days2   Days3
607 Car1        16      85      481     NULL    11      0       NULL
608 Car2        16      85      2072    NULL    21      2       NULL
609 Car3        16      85      333     NULL    15      6       NULL
610 Car4        16      85      1564    NULL    14      0       NULL
611 Car1        16      86      NULL    118     NULL    NULL    4
612 Car2        16      86      NULL    12      NULL    NULL    0
613 Car3        16      86      NULL    133     NULL    NULL    10
614 Car4        16      86      NULL    777     NULL    NULL    17

如何對列 Dist1 Dist2 和 Days1 Days2 Days3 求和以獲得該結果

Desc_Cars   Sum_Dist    Sum_Days
Car1        599         15
Car2        2084        23
Car3        555         31
Car4        2341        31

我總是對 1 個 DocID 進行操作。每個 DocID 始終有 2 個表：TabID 85 和 86

......................

嗨，我在 Select 中使用 Select 的原因是 TabID 85 & 86 在其他列中也有值

SELECT  ID ,Desc_Cars ,DocID ,TabID
        ,Dist1
        ,Dist2
        ,Days1
        ,Days2
        ,Days3
    FROM Details
          where   DocID = 16
      
ID  Desc_Cars   DocID   TabID   Dist1   Dist2   Days1   Days2   Days3
607 Car1        16      85      481     NULL    11      0       NULL
608 Car2        16      85      2072    NULL    21      2       NULL
609 Car3        16      85      333     NULL    15      6       NULL
610 Car4        16      85      1564    NULL    14      0       NULL
611 Car1        16      86      2129    118     10      2101    4
612 Car2        16      86      612     12      2       601     0
613 Car3        16      86      52      133     2       55      10
614 Car4        16      86      59      777     3       800     17

uj5u.com熱心網友回復：

自加入可能最容易做到：

select d1.Desc_Cars, 
       SUM(COALESCE(d1.Dist1, 0)   COALESCE(d2.Dist2, 0)) Sum_Dist,
       SUM(COALESCE(d1.Days1, 0)   COALESCE(d1.Days2, 0)   COALESCE(d2.Days3, 0)) Sum_Days
from Details d1
join details d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.tabid = d2.tabid-1
  and d1.docid = d2.docid
where   d1.DocID = 16  
group by d1.Desc_Cars 

小提琴

@Jarlh:s 版本，在我看來，讓空值的聚合處理更優雅

select d1.Desc_Cars, 
       SUM(d1.Dist1)   SUM(d2.Dist2) Sum_Dist,
       SUM(d1.Days1)   SUM(d1.Days2)   SUM(d2.Days3) Sum_Days
from Details d1
join details d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.tabid = d2.tabid-1
  and d1.docid = d2.docid
where   d1.DocID = 16  
group by d1.Desc_Cars 

如果 tabid 不能保證是連續的，你可以使用 row_number 來匹配下一個：

with t (Desc_Cars, DocID, Dist1, Dist2, Days1, Days2, Days3, rn) AS (
  select Desc_Cars, DocID, Dist1, Dist2, Days1, Days2, Days3
       , row_number() over (partition by Desc_Cars, DocID order by tabid) as rn
  from Details
)
select d1.Desc_Cars, 
       SUM(d1.Dist1)   SUM(d2.Dist2) Sum_Dist,
       SUM(d1.Days1)   SUM(d1.Days2)   SUM(d2.Days3) Sum_Days 
from t d1
join t d2
  on d1.Desc_cars = d2.Desc_cars
  and d1.rn = d2.rn-1
  and d1.docid = d2.docid
group by d1.Desc_Cars;

uj5u.com熱心網友回復：

做一個GROUP BY：

select Desc_Cars,
       SUM(Dist1)   SUM(Dist2) Sum_Dist,
       SUM(Days1)   SUM(Days2)   SUM(Days3) Sum_Days
from Details
where DocID = 16   --  <-- perhaps this condition is needed? 
group by Desc_Cars

標籤：sqlsql服务器

上一篇：使用UserId在不同的表中查找用戶名

下一篇：獲取鍵在所有列中僅出現一次的所有行