Re: [SQL ] 兩個表格連結 join

[fake01 (code)]

join 還是碰到問題,又來詢問了


relation

        Carname  TotalKm
           a      100
           a      75
           b      10
           b      20

behavior

       Carname month Cozy
          a     5     1
          a     5     3
          b     5     2
          b     5     4


結果想要

       Carname TotalKm month Cozy

         a       175     5    4
         b       30      5    6



我下的是這樣
SELECT
        convert(varchar(255),behavior.Carname)'Carname',
        SUM(TotalKm)'TotalKm',
        convert(varchar(255),behavior.Month)'month',
        SUM(Cozy)'Cozy'
FROM
        [iiicar].[dbo].[behavior]
inner Join
        [iiicar].[dbo].[relation]
on
        convert(varchar(255),behavior.Carname)
        = convert(varchar(255),relation.Carname)
group by
        convert(varchar(255),behavior.Carname),
        convert(varchar(255),behavior.Month)


但是好像會多加一次變成


     Carname TotalKm month Cozy
        a     250     5     8
        b      60     5     12


請問怎麼解決呢?


--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 61.222.59.181
→ ji394su33000:兩個table要分開做sum，再用carname 串起來
→ fake01:請問怎麼做?有點模糊
→ fake01:(SELECT convert(varchar(255),Carname)'Carname',SUM(Runt
→ fake01:time)'Runtime' From relation group by convert(varchar
→ fake01:(255),Carname))(SELECT convert(varchar(255),Carname)'Ca
→ fake01:zy'From behavior groupby convert(varchar(255),Carname))
→ fake01:是可以分開SUM 但怎麼串起來呢?
→ fake01:以解決