標題: MSSQL Query [打印本頁]
作者: climbw25394 時間: 2013-6-28 11:59 標題: MSSQL Query
本帖最後由 climbw25394 於 2013-6-28 12:10 編輯
Table 1
Name1 Year1 Count1
ThisIsName 1995/1996 1
ThisIsName 1996/1997 1
ThisIsName 1997/1998 1
ThisIsName 1998/1999 1
Table2
Name2 Year2 Count2
ThisIsName 1996/1997 2
ThisIsName 1997/1998 2
Table3
Name3 Year3 Count3
ThisIsName 1997/1998 1
ThisIsName 1998/1999 1
ThisIsName 2000/2001 1
我就咁join 3個table (條件係name同year相同) 就出左咁既result..
Name Year Count1 Count2 Count3
ThisIsName 1998/1999 1 0 1
ThisIsName 1997/1998 1 2 1
ThisIsName 1995/1996 1 0 0
ThisIsName 1996/1997 1 2 0
我想要既result係
Name Year Count1 Count2 Count3
ThisIsName 1998/1999 1 0 1
ThisIsName 1997/1998 1 2 1
ThisIsName 1995/1996 1 0 0
ThisIsName 1996/1997 1 2 0
ThisIsName 2000/2001 0 0 1
Table 1冇2000/2001所以我個Query出唔到呢個我想要既result。
有冇師兄可以幫幫手，用咩方法可以做到我想要既效果?
作者: d-snake 時間: 2013-6-28 12:14
本帖最後由 d-snake 於 2013-6-28 12:16 編輯
something like this:
select t.name, t.year, sum(t1.count1), sum(t2.count2), sum(t3.count3)
from
(select name1 [name], year1 [year] from table1
union
select name2, year2 from table2
union
select name3, year3 from table3) t
left join table1 t1 on t.name=t1.name1 and t.year=t1.year1
left join table2 t2 on t.name=t2.name2 and t.year=t2.year2
left join table3 t3 on t.name=t3.name3 and t.year=t3.year3
group by t.name, t.year
order by t.name, t.year