SQL 重複しないJoinの仕方を教えてください

質問

データが重複しないSQL文の書き方を教えてください。
下記のような2つのテーブルがあり、「管理番号」でOnして「使用数」をJoinさせ、かつ重複しないようにSQLを作成したいのですが、MySQLで可能でしょうか？
※0002に関しては、テーブル２の方がデータ数が多いため、Join後は2行になって「使用数」は重複せず、「数」の部分には０が入ればベストです

【テーブル１】
日付　　　　品番　　管理番号　数
2012/6/12 A987 　0001 500
2012/6/14 A987 　0001 300
2012/6/16 A987 　0001 400
2012/6/18 A987 　0001 800
2012/6/12 A987 　0002 750
2012/6/12 A987 　0003 540
2012/6/14 A987 　0003 740
2012/6/16 A987 　0003 840
2012/6/18 A987 　0003 240
2012/6/20 A987 　0003 640

【テーブル２】
日付　　　　品番　　管理番号　使用数
2012/7/10 A987 　0001 160
2012/7/11 A987 　0001 260
2012/7/10 A987 　0002 220
2012/7/12 A987 　0002 320
2012/7/20 A987 　0003 530
2012/7/22 A987 　0003 430
2012/7/24 A987 　0003 830

【テーブルJoin】
日付　　　　品番　　管理番号　数　使用数
2012/6/12 A987 　0001 500 160
2012/6/14 A987 　0001 300 260
2012/6/16 A987 　0001 400 0
2012/6/18 A987 　0001 800 0
2012/6/12 A987 　0002 750 220
2012/6/12 A987 　0002 0 320
2012/6/12 A987 　0003 540 530
2012/6/14 A987 　0003 740 430
2012/6/16 A987 　0003 840 830
2012/6/18 A987 　0003 240 0
2012/6/20 A987 　0003 640 0

回答No.1

yambejp

SQLの基本がたぶんわかってないんだろうなぁ・・・という結合です

＞※0002に関しては、テーブル２の方がデータ数が多いため

の箇所はロジックが破綻しているので、表示できるとしても「NULL」です
無理すれば一発でできないことはないですが、テンポラリを作りながらやると解りやすいかも。

//準備
create table テーブル1(日付 date,品番 varchar(10),管理番号 varchar(10),数 int,unique key (日付,管理番号,品番));
insert into テーブル1 values('2012-06-12','A987','0001',500),('2012-06-14','A987','0001',300),('2012-06-16','A987','0001',400),('2012-06-18','A987','0001',800),('2012-06-12','A987','0002',750),('2012-06-12','A987','0003',540),('2012-06-14','A987','0003',740),('2012-06-16','A987','0003',840),('2012-06-18','A987','0003',240),('2012-06-20','A987','0003',640);

create table テーブル2(日付 date,品番 varchar(10),管理番号 varchar(10),使用数 int,unique key (日付,管理番号,品番));
insert into テーブル2 values('2012-7-10','A987','0001',160),('2012-7-11','A987','0001',260),('2012-7-10','A987','0002',220),('2012-7-12','A987','0002',320),('2012-7-20','A987','0003',530),('2012-7-22','A987','0003',430),('2012-7-24','A987','0003',830);

//抽出SQL
create temporary table temp_t1
select (select count(*) +1 from テーブル1 AS t1b where t1a.日付 > t1b.日付 and t1a.管理番号=t1b.管理番号 and t1a.品番=t1b.品番) AS RANK,日付,品番,管理番号,数
FROM テーブル1 AS t1a;
create temporary table temp_t2
select (select count(*) +1 from テーブル2 AS t2b where t2a.日付 > t2b.日付 and t2a.管理番号=t2b.管理番号 and t2a.品番=t2b.品番) AS RANK,日付,品番,管理番号,使用数
FROM テーブル2 AS t2a;
create temporary table temp_t3
select RANK,管理番号,品番 from temp_t1 union select RANK,管理番号,品番 from temp_t2;

//表示部分
select temp_t1.日付
,temp_t3.品番
,temp_t3.管理番号
,coalesce(数,0) as 数
,coalesce(使用数,0) as 使用数
from temp_t3
left join temp_t1 on temp_t3.RANK=temp_t1.RANK and temp_t3.管理番号=temp_t1.管理番号 and temp_t3.品番=temp_t1.品番
left join temp_t2 on temp_t3.RANK=temp_t2.RANK and temp_t3.管理番号=temp_t2.管理番号 and temp_t3.品番=temp_t2.品番
order by temp_t3.管理番号,temp_t3.RANK