SQLServerでUNIONしたテーブルに同じ項目がある場合に一部の項目の値を変更する方法

Question

###前提・実現したいこと
テーブルA
NAME　SUBJECT SCORE
太郎　　英語　　　30　　　
次郎　　英語　　　40
三郎　　英語　　　50

テーブルB
NAME　SUBJECT SCORE
太郎　　数学　　　40
次郎　　数学　　　50
四郎　　数学　　　60

SQLServerで上記のような2つのテーブルをunion allし、同じNAME項目があれば
列に合計を表示するような取得方法はありますでしょうか？

実現したい検索結果
NAME　SUBJECT SCORE
太郎　　英語　　　70
太郎　　数学　　　70
次郎　　英語　　　90
次郎　　数学　　　90
三郎　　英語　　　50
四郎　　数学　　　60

Answer 1

SQLServerの分析関数の使用例
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html
を見ながら、SQLFiddleのSQLServer2014環境で作ってみました :-)

with A(NAME,SUBJECT,SCORE) as(
select N'太郎',N'英語',30 union
select N'次郎',N'英語',40 union
select N'三郎',N'英語',50),
B(NAME,SUBJECT,SCORE) as(
select N'太郎',N'数学',40 union
select N'次郎',N'数学',50 union
select N'四郎',N'数学',60),
tmp as(
select NAME,SUBJECT,SCORE from A
union all
select NAME,SUBJECT,SCORE from B)
select NAME,SUBJECT,sum(SCORE) over(partition by NAME) as SCORE
  from tmp
order by NAME,SUBJECT;

| NAME   | SUBJECT | SCORE |
|--------|---------|-------|
|   三郎 |    英語 |    50 |
|   四郎 |    数学 |    60 |
|   太郎 |    数学 |    70 |
|   太郎 |    英語 |    70 |
|   次郎 |    数学 |    90 |
|   次郎 |    英語 |    90 |

Comments

[sazi]

partition byにSUBJECTが足らないですね

[ranma]

解決しました。感謝します。

Answer 2

なぜ、union してた結果をgroup by してsum()を取るのが駄目なのか分かりませんが、
そこは不問にするとしての回答です。

union した結果で重複して２つまでという前提で。( 2012以降)

select NAME,SUBJECT
    , SCORE
     +LEAD(SCORE,1,0) over(partition by NAME,SUBJECT order by SCORE)
     +LAG(SCORE,1,0) over(partition by NAME,SUBJECT order by SCORE)
      as SCORE
from (
    select * from テーブルA
  union all
    select * from テーブルB
) t1

３以上重複というなら(2008以降)　

with テーブルAB(NAME,SUBJECT,SCORE) as(
    select * from テーブルA
  union all
    select * from テーブルB
)
select NAME,SUBJECT
    , (select sum(SCORE) from テーブルAB 
       where NAME=t1.NAME and SUBJECT=t1.SUBJECT) as SCORE 
from テーブルAB as t1

※一応、AketiJyuuzouさんの別解ということで、使用できる環境なら、AketiJyuuzouさんの方が性能面で良いと思います。

Comments

[ranma]

回答ありがとうございます。感謝します。