-- ※作成したテーブルにはインデックスを張っておく

-- buppinテーブルのバックアップ
 buppin_20151221

-- 今回対象外となる物品IDでテーブルを作成し、CSVをインポートする
CREATE TABLE buppin_without_20151221 (
  buppin_id varbinary(36) DEFAULT NULL,
  KEY buppin_id (buppin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- 入荷情報が１つの物品ID :1,310
CREATE TABLE buppin_taisyo_1_20151221
	SELECT buppin_id, COUNT( id ) FROM nyuka
	WHERE buppin_id IN (
		SELECT id
		FROM buppin
		WHERE deleted =0
		)
	AND deleted =0
	GROUP BY buppin_id
	HAVING COUNT( id ) =1;

-- 入荷情報が１つの物品IDに発注情報を追加 :1,310
CREATE TABLE buppin_taisyo_1_hacchu_20151221
	SELECT nyuka.id as nyuka_id, nyuka.date_modified, nyuka.buppin_id, nyuka.hacchuu_id, nyuka.nyuuka_jissuu, nyuka.nyuukabi, nyuka.tanka_shiire, hacchu.hacchu_tanka,
		(CASE WHEN nyuka.hacchuu_id IS NULL THEN nyuka.tanka_shiire 
		ELSE hacchu.hacchu_tanka 
		END) as tanka_result
	FROM nyuka
	LEFT JOIN hacchu on hacchu.id = nyuka.hacchuu_id and hacchu.deleted = 0
	WHERE nyuka.buppin_id IN (
		SELECT buppin_id
		FROM buppin_taisyo_1_20151221
		);

-- 入荷情報が２つ以上の物品ID　：422 
CREATE TABLE buppin_taisyo_23_20151221
	SELECT buppin_id, COUNT( id ) FROM nyuka
	WHERE buppin_id IN (
		SELECT id
		FROM buppin
		WHERE deleted =0
		)
	AND deleted =0
	GROUP BY buppin_id
	HAVING COUNT( id ) >1;

-- 入荷情報が２つ以上の物品ID発注情報を追加 :1,048 
CREATE TABLE buppin_taisyo_23_hacchu_20151221
	SELECT nyuka.id as nyuka_id, nyuka.date_modified, nyuka.buppin_id, nyuka.hacchuu_id, nyuka.nyuuka_jissuu, nyuka.nyuukabi, nyuka.tanka_shiire, hacchu.hacchu_tanka,
		(CASE WHEN nyuka.hacchuu_id IS NULL THEN nyuka.tanka_shiire 
		ELSE hacchu.hacchu_tanka 
		END) as tanka_result
	FROM nyuka
	LEFT JOIN hacchu on hacchu.id = nyuka.hacchuu_id and hacchu.deleted = 0
	WHERE nyuka.buppin_id IN (
		SELECT buppin_id
		FROM buppin_taisyo_23_20151221
		);

-- 入荷情報が２つ以上の物品IDで、単価が１種類のもの :53
CREATE TABLE buppin_taisyo_2_20151221
	SELECT  B_ID as buppin_id, COUNT( B_ID ) FROM
		(SELECT  buppin_id AS B_ID ,  tanka_result , COUNT( nyuka_id ) 
		FROM  buppin_taisyo_23_hacchu_20151221 
		GROUP BY  buppin_id ,  tanka_result) AS X_TABLE 
	GROUP BY  B_ID 
	HAVING COUNT( B_ID ) = 1;

-- 入荷情報が２つ以上の物品IDで、単価が１種類のものに発注情報を追加 :152 
CREATE TABLE buppin_taisyo_2_hacchu_20151221
	SELECT * FROM buppin_taisyo_23_hacchu_20151221
	WHERE buppin_id IN(
		 SELECT  buppin_id FROM buppin_taisyo_2_20151221)

-- 入荷情報が２つ以上の物品IDで、単価が１種類以上のもの :369
CREATE TABLE buppin_taisyo_3_20151221
	SELECT  B_ID as buppin_id, COUNT( B_ID ) FROM
		(SELECT  buppin_id AS B_ID ,  tanka_result , COUNT( nyuka_id ) 
		FROM  buppin_taisyo_23_hacchu_20151221 
		GROUP BY  buppin_id ,  tanka_result) AS X_TABLE 
	GROUP BY  B_ID 
	HAVING COUNT( B_ID ) > 1;

-- 入荷情報が２つ以上の物品IDで、単価が１種類以上のものに発注情報を追加 : 896 
CREATE TABLE buppin_taisyo_3_hacchu_20151221
	SELECT * FROM buppin_taisyo_23_hacchu_20151221
	WHERE buppin_id IN(
		 SELECT  buppin_id FROM buppin_taisyo_3_20151221)

-- タイプ１　対象の発送コードと物品の組み合わせを取得 	:564,377
-- 出荷情報　hasso_contactsのdate_modified「2014-01-03 00:00:00」を除く
CREATE TABLE hassocode_taisyo_1_20151221
SELECT 
	hasso_contacts.id as hasso_contacts_id,  
	hasso_contacts.date_modified as date_modified,  
	hasso_contacts.buppin_id as hasso_contacts_buppin_id,
	hassocode_contacts.id as hassocode_contacts_id, 
	hassocode_contacts.hassocode_id,
	hassocode_contacts.hasso_type
FROM  hasso_contacts 
INNER JOIN  hassocode_contacts ON hasso_contacts.hassocode_contacts_id = hassocode_contacts.id AND
	hassocode_contacts.deleted =0 AND hassocode_contacts.hasso_status =5
WHERE 
	hasso_contacts.hasso_status =5 AND 
	hasso_contacts.deleted =0 AND 
	hasso_contacts.date_modified != "2014-01-03 00:00:00" AND 
	hasso_contacts.buppin_id IN (
		SELECT buppin_id
		FROM buppin_taisyo_1_20151221
		);

-- 通常出荷 :2,080 
CREATE TABLE hassocode_buppin_1_20151221
SELECT * 
FROM  hassocode_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_1_20151221
	) AND
	hassocode_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_1_20151221
	WHERE hasso_type = 1
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :2,080
CREATE TABLE hassocode_buppin_sum_1_20151221
SELECT  hassocode_id,buppin_id , SUM( hasso_suryo ) as suryo_sum 
FROM  hassocode_buppin_1_20151221 
GROUP BY  hassocode_id, buppin_id 

-- 個別出荷 :2,536 
CREATE TABLE shukka_kobetsu_buppin_1_20151221
SELECT * 
FROM  shukka_kobetsu_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_1_20151221
	) AND
	shukka_kobetsu_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_1_20151221
	WHERE hasso_type = 2
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :2,536
CREATE TABLE shukka_kobetsu_buppin_sum_1_20151221
SELECT  shukka_kobetsu_id as hassocode_id, buppin_id , SUM( suryou ) as suryo_sum 
FROM  shukka_kobetsu_buppin_1_20151221 
GROUP BY  shukka_kobetsu_id, buppin_id 

-- 依頼出荷 :666 
CREATE TABLE shukka_iraisho_1_20151221 
SELECT * 
FROM  shukka_iraisho 
WHERE 
	deleted =0 AND 
	id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_1_20151221
	);

-- buppin_id_1〜25でデータセレクトインサートする :4,086
CREATE TABLE shukka_iraisho_buppin_1_20151221 (
  hassocode_id char(36) NOT NULL,
  buppin_id varbinary(36) DEFAULT NULL,
  irai_suryo int(11) DEFAULT NULL
);

INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_1, irai_suryo_1 FROM shukka_iraisho_1_20151221 WHERE buppin_id_1 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_2, irai_suryo_2 FROM shukka_iraisho_1_20151221 WHERE buppin_id_2 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_3, irai_suryo_3 FROM shukka_iraisho_1_20151221 WHERE buppin_id_3 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_4, irai_suryo_4 FROM shukka_iraisho_1_20151221 WHERE buppin_id_4 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_5, irai_suryo_5 FROM shukka_iraisho_1_20151221 WHERE buppin_id_5 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_6, irai_suryo_6 FROM shukka_iraisho_1_20151221 WHERE buppin_id_6 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_7, irai_suryo_7 FROM shukka_iraisho_1_20151221 WHERE buppin_id_7 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_8, irai_suryo_8 FROM shukka_iraisho_1_20151221 WHERE buppin_id_8 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_9, irai_suryo_9 FROM shukka_iraisho_1_20151221 WHERE buppin_id_9 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_10, irai_suryo_10 FROM shukka_iraisho_1_20151221 WHERE buppin_id_10 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_11, irai_suryo_11 FROM shukka_iraisho_1_20151221 WHERE buppin_id_11 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_12, irai_suryo_12 FROM shukka_iraisho_1_20151221 WHERE buppin_id_12 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_13, irai_suryo_13 FROM shukka_iraisho_1_20151221 WHERE buppin_id_13 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_14, irai_suryo_14 FROM shukka_iraisho_1_20151221 WHERE buppin_id_14 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_15, irai_suryo_15 FROM shukka_iraisho_1_20151221 WHERE buppin_id_15 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_16, irai_suryo_16 FROM shukka_iraisho_1_20151221 WHERE buppin_id_16 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_17, irai_suryo_17 FROM shukka_iraisho_1_20151221 WHERE buppin_id_17 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_18, irai_suryo_18 FROM shukka_iraisho_1_20151221 WHERE buppin_id_18 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_19, irai_suryo_19 FROM shukka_iraisho_1_20151221 WHERE buppin_id_19 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_20, irai_suryo_20 FROM shukka_iraisho_1_20151221 WHERE buppin_id_20 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_21, irai_suryo_21 FROM shukka_iraisho_1_20151221 WHERE buppin_id_21 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_22, irai_suryo_22 FROM shukka_iraisho_1_20151221 WHERE buppin_id_22 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_23, irai_suryo_23 FROM shukka_iraisho_1_20151221 WHERE buppin_id_23 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_24, irai_suryo_24 FROM shukka_iraisho_1_20151221 WHERE buppin_id_24 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_1_20151221 SELECT id, buppin_id_25, irai_suryo_25 FROM shukka_iraisho_1_20151221 WHERE buppin_id_25 IS NOT NULL; 

-- 対象の物品IDと発送コードでグループ化して、数量の合計を取得 :2,313
CREATE TABLE shukka_iraisho_buppin_sum_1_20151221
SELECT  hassocode_id, buppin_id , SUM( irai_suryo ) as suryo_sum 
FROM  shukka_iraisho_buppin_1_20151221 
WHERE 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_1_20151221
	)
GROUP BY  hassocode_id, buppin_id;


-- 各物品と数量を発送先でかける

-- 発送数を抽出
-- hassocode_taisyo_1_20151221を使用

-- 通常 :559,529
CREATE TABLE hassocode_buppin_total_1_20151221
SELECT 
	hassocode_taisyo_1_20151221.hasso_contacts_id,
	hassocode_taisyo_1_20151221.date_modified,
	hassocode_taisyo_1_20151221.hassocode_id,
	hassocode_taisyo_1_20151221.hasso_contacts_buppin_id,
	hassocode_buppin_sum_1_20151221.suryo_sum
FROM  hassocode_taisyo_1_20151221 
INNER JOIN hassocode_buppin_sum_1_20151221 ON 
	hassocode_buppin_sum_1_20151221.buppin_id = hassocode_taisyo_1_20151221.hasso_contacts_buppin_id AND
	hassocode_buppin_sum_1_20151221.hassocode_id = hassocode_taisyo_1_20151221.hassocode_id;

-- 個別 :2,536 
CREATE TABLE shukka_kobetsu_buppin_total_1_20151221
SELECT 
	hassocode_taisyo_1_20151221.hasso_contacts_id,
	hassocode_taisyo_1_20151221.date_modified,
	hassocode_taisyo_1_20151221.hassocode_id,
	hassocode_taisyo_1_20151221.hasso_contacts_buppin_id,
	shukka_kobetsu_buppin_sum_1_20151221.suryo_sum
FROM  hassocode_taisyo_1_20151221 
INNER JOIN shukka_kobetsu_buppin_sum_1_20151221 ON 
	shukka_kobetsu_buppin_sum_1_20151221.buppin_id = hassocode_taisyo_1_20151221.hasso_contacts_buppin_id AND
	shukka_kobetsu_buppin_sum_1_20151221.hassocode_id = hassocode_taisyo_1_20151221.hassocode_id ;

-- 依頼 :2,312
CREATE TABLE shukka_iraisho_buppin_total_1_20151221
SELECT 
	hassocode_taisyo_1_20151221.hasso_contacts_id,
	hassocode_taisyo_1_20151221.date_modified,
	hassocode_taisyo_1_20151221.hassocode_id,
	hassocode_taisyo_1_20151221.hasso_contacts_buppin_id,
	shukka_iraisho_buppin_sum_1_20151221.suryo_sum
FROM  hassocode_taisyo_1_20151221 
INNER JOIN shukka_iraisho_buppin_sum_1_20151221 ON 
	shukka_iraisho_buppin_sum_1_20151221.buppin_id = hassocode_taisyo_1_20151221.hasso_contacts_buppin_id AND
	shukka_iraisho_buppin_sum_1_20151221.hassocode_id = hassocode_taisyo_1_20151221.hassocode_id ;


-- タイプ２　対象の発送コードと物品の組み合わせを取得 :142,361
-- 出荷情報　hasso_contactsのdate_modified「2014-01-03 00:00:00」を除く
CREATE TABLE hassocode_taisyo_2_20151221
SELECT 
	hasso_contacts.id as hasso_contacts_id,  
	hasso_contacts.date_modified as date_modified,  
	hasso_contacts.buppin_id as hasso_contacts_buppin_id,
	hassocode_contacts.id as hassocode_contacts_id, 
	hassocode_contacts.hassocode_id,
	hassocode_contacts.hasso_type
FROM  hasso_contacts 
INNER JOIN  hassocode_contacts ON hasso_contacts.hassocode_contacts_id = hassocode_contacts.id AND
	hassocode_contacts.deleted =0 AND hassocode_contacts.hasso_status =5
WHERE 
	hasso_contacts.hasso_status =5 AND 
	hasso_contacts.date_modified != "2014-01-03 00:00:00" AND 
	hasso_contacts.deleted =0 AND 
	hasso_contacts.buppin_id IN (
		SELECT buppin_id
		FROM buppin_taisyo_2_20151221
		);

-- 通常出荷 :610    
CREATE TABLE hassocode_buppin_2_20151221
SELECT * 
FROM  hassocode_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_2_20151221
	) AND
	hassocode_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_2_20151221
	WHERE hasso_type = 1
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :610
CREATE TABLE hassocode_buppin_sum_2_20151221 
SELECT  hassocode_id,buppin_id , SUM( hasso_suryo ) as suryo_sum 
FROM  hassocode_buppin_2_20151221 
GROUP BY  hassocode_id, buppin_id 

-- 個別出荷 :177   
CREATE TABLE shukka_kobetsu_buppin_2_20151221
SELECT * 
FROM  shukka_kobetsu_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_2_20151221
	) AND
	shukka_kobetsu_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_2_20151221
	WHERE hasso_type = 2
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :177
CREATE TABLE shukka_kobetsu_buppin_sum_2_20151221
SELECT  shukka_kobetsu_id as hassocode_id, buppin_id , SUM( suryou ) as suryo_sum 
FROM  shukka_kobetsu_buppin_2_20151221 
GROUP BY  shukka_kobetsu_id, buppin_id 

-- 依頼出荷 :214  
CREATE TABLE shukka_iraisho_2_20151221 
SELECT * 
FROM  shukka_iraisho 
WHERE 
	deleted =0 AND 
	id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_2_20151221
	);

-- buppin_id_1〜25でデータセレクトインサートする :1128
CREATE TABLE shukka_iraisho_buppin_2_20151221 (
  hassocode_id char(36) NOT NULL,
  buppin_id varbinary(36) DEFAULT NULL,
  irai_suryo int(11) DEFAULT NULL
);

INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_1, irai_suryo_1 FROM shukka_iraisho_2_20151221 WHERE buppin_id_1 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_2, irai_suryo_2 FROM shukka_iraisho_2_20151221 WHERE buppin_id_2 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_3, irai_suryo_3 FROM shukka_iraisho_2_20151221 WHERE buppin_id_3 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_4, irai_suryo_4 FROM shukka_iraisho_2_20151221 WHERE buppin_id_4 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_5, irai_suryo_5 FROM shukka_iraisho_2_20151221 WHERE buppin_id_5 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_6, irai_suryo_6 FROM shukka_iraisho_2_20151221 WHERE buppin_id_6 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_7, irai_suryo_7 FROM shukka_iraisho_2_20151221 WHERE buppin_id_7 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_8, irai_suryo_8 FROM shukka_iraisho_2_20151221 WHERE buppin_id_8 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_9, irai_suryo_9 FROM shukka_iraisho_2_20151221 WHERE buppin_id_9 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_10, irai_suryo_10 FROM shukka_iraisho_2_20151221 WHERE buppin_id_10 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_11, irai_suryo_11 FROM shukka_iraisho_2_20151221 WHERE buppin_id_11 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_12, irai_suryo_12 FROM shukka_iraisho_2_20151221 WHERE buppin_id_12 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_13, irai_suryo_13 FROM shukka_iraisho_2_20151221 WHERE buppin_id_13 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_14, irai_suryo_14 FROM shukka_iraisho_2_20151221 WHERE buppin_id_14 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_15, irai_suryo_15 FROM shukka_iraisho_2_20151221 WHERE buppin_id_15 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_16, irai_suryo_16 FROM shukka_iraisho_2_20151221 WHERE buppin_id_16 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_17, irai_suryo_17 FROM shukka_iraisho_2_20151221 WHERE buppin_id_17 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_18, irai_suryo_18 FROM shukka_iraisho_2_20151221 WHERE buppin_id_18 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_19, irai_suryo_19 FROM shukka_iraisho_2_20151221 WHERE buppin_id_19 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_20, irai_suryo_20 FROM shukka_iraisho_2_20151221 WHERE buppin_id_20 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_21, irai_suryo_21 FROM shukka_iraisho_2_20151221 WHERE buppin_id_21 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_22, irai_suryo_22 FROM shukka_iraisho_2_20151221 WHERE buppin_id_22 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_23, irai_suryo_23 FROM shukka_iraisho_2_20151221 WHERE buppin_id_23 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_24, irai_suryo_24 FROM shukka_iraisho_2_20151221 WHERE buppin_id_24 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_2_20151221 SELECT id, buppin_id_25, irai_suryo_25 FROM shukka_iraisho_2_20151221 WHERE buppin_id_25 IS NOT NULL; 

-- 対象の物品IDと発送コードでグループ化して、数量の合計を取得 :249 
CREATE TABLE shukka_iraisho_buppin_sum_2_20151221
SELECT  hassocode_id, buppin_id , SUM( irai_suryo ) as suryo_sum 
FROM  shukka_iraisho_buppin_2_20151221 
WHERE 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_2_20151221
	)
GROUP BY  hassocode_id, buppin_id;

-- 発送数を抽出
-- hassocode_taisyo_2_20151221を使用

-- 通常 :141,935
CREATE TABLE hassocode_buppin_total_2_20151221
SELECT 
	hassocode_taisyo_2_20151221.hasso_contacts_id,
	hassocode_taisyo_2_20151221.date_modified,
	hassocode_taisyo_2_20151221.hassocode_id,
	hassocode_taisyo_2_20151221.hasso_contacts_buppin_id,
	hassocode_buppin_sum_2_20151221.suryo_sum
FROM  hassocode_taisyo_2_20151221 
INNER JOIN hassocode_buppin_sum_2_20151221 ON 
	hassocode_buppin_sum_2_20151221.buppin_id = hassocode_taisyo_2_20151221.hasso_contacts_buppin_id AND
	hassocode_buppin_sum_2_20151221.hassocode_id = hassocode_taisyo_2_20151221.hassocode_id;

-- 個別 :177    
CREATE TABLE shukka_kobetsu_buppin_total_2_20151221
SELECT 
	hassocode_taisyo_2_20151221.hasso_contacts_id,
	hassocode_taisyo_2_20151221.date_modified,
	hassocode_taisyo_2_20151221.hassocode_id,
	hassocode_taisyo_2_20151221.hasso_contacts_buppin_id,
	shukka_kobetsu_buppin_sum_2_20151221.suryo_sum
FROM  hassocode_taisyo_2_20151221 
INNER JOIN shukka_kobetsu_buppin_sum_2_20151221 ON 
	shukka_kobetsu_buppin_sum_2_20151221.buppin_id = hassocode_taisyo_2_20151221.hasso_contacts_buppin_id AND
	shukka_kobetsu_buppin_sum_2_20151221.hassocode_id = hassocode_taisyo_2_20151221.hassocode_id ;

-- 依頼 :249   
CREATE TABLE shukka_iraisho_buppin_total_2_20151221
SELECT 
	hassocode_taisyo_2_20151221.hasso_contacts_id,
	hassocode_taisyo_2_20151221.date_modified,
	hassocode_taisyo_2_20151221.hassocode_id,
	hassocode_taisyo_2_20151221.hasso_contacts_buppin_id,
	shukka_iraisho_buppin_sum_2_20151221.suryo_sum
FROM  hassocode_taisyo_2_20151221 
INNER JOIN shukka_iraisho_buppin_sum_2_20151221 ON 
	shukka_iraisho_buppin_sum_2_20151221.buppin_id = hassocode_taisyo_2_20151221.hasso_contacts_buppin_id AND
	shukka_iraisho_buppin_sum_2_20151221.hassocode_id = hassocode_taisyo_2_20151221.hassocode_id ;


-- タイプ３　対象の発送コードと物品の組み合わせを取得 :415,493
-- 出荷情報　hasso_contactsのdate_modified「2014-01-03 00:00:00」を除く
CREATE TABLE hassocode_taisyo_3_20151221
SELECT 
	hasso_contacts.id as hasso_contacts_id,  
	hasso_contacts.date_modified as date_modified,  
	hasso_contacts.buppin_id as hasso_contacts_buppin_id,
	hassocode_contacts.id as hassocode_contacts_id, 
	hassocode_contacts.hassocode_id,
	hassocode_contacts.hasso_type
FROM  hasso_contacts 
INNER JOIN  hassocode_contacts ON hasso_contacts.hassocode_contacts_id = hassocode_contacts.id AND
	hassocode_contacts.deleted =0 AND hassocode_contacts.hasso_status =5
WHERE 
	hasso_contacts.hasso_status =5 AND 
	hasso_contacts.date_modified != "2014-01-03 00:00:00" AND 
	hasso_contacts.deleted =0 AND 
	hasso_contacts.buppin_id IN (
		SELECT buppin_id
		FROM buppin_taisyo_3_20151221
		);

-- 通常出荷 :1,502  
CREATE TABLE hassocode_buppin_3_20151221
SELECT * 
FROM  hassocode_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_3_20151221
	) AND
	hassocode_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_3_20151221
	WHERE hasso_type = 1
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :1,502
CREATE TABLE hassocode_buppin_sum_3_20151221
SELECT  hassocode_id,buppin_id , SUM( hasso_suryo ) as suryo_sum 
FROM  hassocode_buppin_3_20151221 
GROUP BY  hassocode_id, buppin_id 

-- 個別出荷 :3,332  
CREATE TABLE shukka_kobetsu_buppin_3_20151221
SELECT * 
FROM  shukka_kobetsu_buppin 
WHERE 
	deleted =0 AND 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_3_20151221
	) AND
	shukka_kobetsu_id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_3_20151221
	WHERE hasso_type = 2
	);

-- 物品IDと発送コードでグループ化して、数量の合計を取得 :3,332
CREATE TABLE shukka_kobetsu_buppin_sum_3_20151221
SELECT  shukka_kobetsu_id as hassocode_id, buppin_id , SUM( suryou ) as suryo_sum 
FROM  shukka_kobetsu_buppin_3_20151221 
GROUP BY  shukka_kobetsu_id, buppin_id 

-- 依頼出荷 :815  
CREATE TABLE shukka_iraisho_3_20151221
SELECT * 
FROM  shukka_iraisho 
WHERE 
	deleted =0 AND 
	id IN (
	SELECT DISTINCT(hassocode_id) 
	FROM hassocode_taisyo_3_20151221
	);

-- buppin_id_1〜25でデータセレクトインサートする :4467
CREATE TABLE shukka_iraisho_buppin_3_20151221 (
  hassocode_id char(36) NOT NULL,
  buppin_id varbinary(36) DEFAULT NULL,
  irai_suryo int(11) DEFAULT NULL
);

INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_1, irai_suryo_1 FROM shukka_iraisho_3_20151221 WHERE buppin_id_1 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_2, irai_suryo_2 FROM shukka_iraisho_3_20151221 WHERE buppin_id_2 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_3, irai_suryo_3 FROM shukka_iraisho_3_20151221 WHERE buppin_id_3 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_4, irai_suryo_4 FROM shukka_iraisho_3_20151221 WHERE buppin_id_4 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_5, irai_suryo_5 FROM shukka_iraisho_3_20151221 WHERE buppin_id_5 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_6, irai_suryo_6 FROM shukka_iraisho_3_20151221 WHERE buppin_id_6 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_7, irai_suryo_7 FROM shukka_iraisho_3_20151221 WHERE buppin_id_7 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_8, irai_suryo_8 FROM shukka_iraisho_3_20151221 WHERE buppin_id_8 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_9, irai_suryo_9 FROM shukka_iraisho_3_20151221 WHERE buppin_id_9 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_10, irai_suryo_10 FROM shukka_iraisho_3_20151221 WHERE buppin_id_10 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_11, irai_suryo_11 FROM shukka_iraisho_3_20151221 WHERE buppin_id_11 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_12, irai_suryo_12 FROM shukka_iraisho_3_20151221 WHERE buppin_id_12 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_13, irai_suryo_13 FROM shukka_iraisho_3_20151221 WHERE buppin_id_13 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_14, irai_suryo_14 FROM shukka_iraisho_3_20151221 WHERE buppin_id_14 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_15, irai_suryo_15 FROM shukka_iraisho_3_20151221 WHERE buppin_id_15 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_16, irai_suryo_16 FROM shukka_iraisho_3_20151221 WHERE buppin_id_16 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_17, irai_suryo_17 FROM shukka_iraisho_3_20151221 WHERE buppin_id_17 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_18, irai_suryo_18 FROM shukka_iraisho_3_20151221 WHERE buppin_id_18 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_19, irai_suryo_19 FROM shukka_iraisho_3_20151221 WHERE buppin_id_19 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_20, irai_suryo_20 FROM shukka_iraisho_3_20151221 WHERE buppin_id_20 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_21, irai_suryo_21 FROM shukka_iraisho_3_20151221 WHERE buppin_id_21 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_22, irai_suryo_22 FROM shukka_iraisho_3_20151221 WHERE buppin_id_22 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_23, irai_suryo_23 FROM shukka_iraisho_3_20151221 WHERE buppin_id_23 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_24, irai_suryo_24 FROM shukka_iraisho_3_20151221 WHERE buppin_id_24 IS NOT NULL; 
INSERT INTO shukka_iraisho_buppin_3_20151221 SELECT id, buppin_id_25, irai_suryo_25 FROM shukka_iraisho_3_20151221 WHERE buppin_id_25 IS NOT NULL; 

-- 対象の物品IDと発送コードでグループ化して、数量の合計を取得 :2,606 
CREATE TABLE shukka_iraisho_buppin_sum_3_20151221
SELECT  hassocode_id, buppin_id , SUM( irai_suryo ) as suryo_sum 
FROM  shukka_iraisho_buppin_3_20151221 
WHERE 
	buppin_id IN (
	SELECT  buppin_id 
	FROM buppin_taisyo_3_20151221
	)
GROUP BY  hassocode_id, buppin_id;

-- 発送数を抽出
-- hassocode_taisyo_3_20151221を使用

-- 通常 :409,555
CREATE TABLE hassocode_buppin_total_3_20151221
SELECT 
	hassocode_taisyo_3_20151221.hasso_contacts_id,
	hassocode_taisyo_3_20151221.date_modified,
	hassocode_taisyo_3_20151221.hassocode_id,
	hassocode_taisyo_3_20151221.hasso_contacts_buppin_id,
	hassocode_buppin_sum_3_20151221.suryo_sum
FROM  hassocode_taisyo_3_20151221 
INNER JOIN hassocode_buppin_sum_3_20151221 ON 
	hassocode_buppin_sum_3_20151221.buppin_id = hassocode_taisyo_3_20151221.hasso_contacts_buppin_id AND
	hassocode_buppin_sum_3_20151221.hassocode_id = hassocode_taisyo_3_20151221.hassocode_id;

-- 個別 :3,332  
CREATE TABLE shukka_kobetsu_buppin_total_3_20151221
SELECT 
	hassocode_taisyo_3_20151221.hasso_contacts_id,
	hassocode_taisyo_3_20151221.date_modified,
	hassocode_taisyo_3_20151221.hassocode_id,
	hassocode_taisyo_3_20151221.hasso_contacts_buppin_id,
	shukka_kobetsu_buppin_sum_3_20151221.suryo_sum
FROM  hassocode_taisyo_3_20151221 
INNER JOIN shukka_kobetsu_buppin_sum_3_20151221 ON 
	shukka_kobetsu_buppin_sum_3_20151221.buppin_id = hassocode_taisyo_3_20151221.hasso_contacts_buppin_id AND
	shukka_kobetsu_buppin_sum_3_20151221.hassocode_id = hassocode_taisyo_3_20151221.hassocode_id ;

-- 依頼 :2,606 
CREATE TABLE shukka_iraisho_buppin_total_3_20151221
SELECT 
	hassocode_taisyo_3_20151221.hasso_contacts_id,
	hassocode_taisyo_3_20151221.date_modified,
	hassocode_taisyo_3_20151221.hassocode_id,
	hassocode_taisyo_3_20151221.hasso_contacts_buppin_id,
	shukka_iraisho_buppin_sum_3_20151221.suryo_sum
FROM  hassocode_taisyo_3_20151221 
INNER JOIN shukka_iraisho_buppin_sum_3_20151221 ON 
	shukka_iraisho_buppin_sum_3_20151221.buppin_id = hassocode_taisyo_3_20151221.hasso_contacts_buppin_id AND
	shukka_iraisho_buppin_sum_3_20151221.hassocode_id = hassocode_taisyo_3_20151221.hassocode_id ;


-- タイプ１〜３の入荷、出荷情報を合わせる :1,124,589
-- すでに作成済み、レプリケーション対象外
CREATE TABLE nyuka_shukka_total_20151221 (
  s_id varchar(10) DEFAULT NULL,
  nyuka_type varchar(2) DEFAULT NULL,
  buppin_id varbinary(36) NOT NULL,
  date_modified datetime NOT NULL,
  nyuka_id char(36) DEFAULT NULL,
  hacchuu_id char(36) DEFAULT NULL,
  nyuuka_jissuu double DEFAULT NULL,
  tanka_shiire double DEFAULT NULL,
  hacchu_tanka double DEFAULT NULL,
  tanka_result double DEFAULT NULL,
  hasso_contacts_id varbinary(50) DEFAULT NULL,
  hassocode_id varbinary(36) DEFAULT NULL,
  suryo_sum decimal(32,0) DEFAULT NULL,
  zaikosu decimal(32,0) DEFAULT NULL,
  idouheikin_tanka double DEFAULT NULL
);

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result) 
SELECT "1",buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result FROM buppin_taisyo_1_hacchu_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "1",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM hassocode_buppin_total_1_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "1",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_kobetsu_buppin_total_1_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "1",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_iraisho_buppin_total_1_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result) 
SELECT "2",buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result FROM buppin_taisyo_2_hacchu_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "2",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM hassocode_buppin_total_2_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "2",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_kobetsu_buppin_total_2_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "2",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_iraisho_buppin_total_2_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result) 
SELECT "3",buppin_id,date_modified,nyuka_id,hacchuu_id,nyuuka_jissuu,tanka_shiire,hacchu_tanka,tanka_result FROM buppin_taisyo_3_hacchu_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "3",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM hassocode_buppin_total_3_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "3",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_kobetsu_buppin_total_3_20151221;

INSERT INTO nyuka_shukka_total_20151221(nyuka_type,buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum) 
SELECT "3",hasso_contacts_buppin_id,date_modified,hasso_contacts_id,hassocode_id,suryo_sum FROM shukka_iraisho_buppin_total_3_20151221;

-- 上記テーブルから物品と日付の昇順でセレクト
SELECT * FROM  nyuka_shukka_total_20151221 ORDER BY buppin_id, date_modified, hasso_contacts_id

-- パッチプログラム用セレクト
SELECT s_id,nyuka_type,buppin_id,date_modified,nyuka_id,nyuuka_jissuu,tanka_result,hasso_contacts_id,suryo_sum,zaikosu,idouheikin_tanka 
FROM  nyuka_shukka_total_20151221 ORDER BY buppin_id, date_modified,hasso_contacts_id

-- SMSの在庫管理メニューから移動平均単価リカバリー（LWRecoveryIdoheikinTanka_AN）を実行
	-- Buppin->LWRecoveryIdoheikinTanka_AN内の$table_nameを任意のテーブル名に変更（nyuka_shukka_total_20151221等）
	-- 実行
	-- プログラムでnyuka_shukka_total_20151221のidouheikin_tankaに計算結果の移動平均単価が保存される

-- 物品IDでGROUP BYした結果の行数を取得するクエリー
SELECT SQL_CALC_FOUND_ROWS count( * ) FROM nyuka_shukka_total_20151221 GROUP BY buppin_id;
SELECT FOUND_ROWS( ) ; -- 結果 1732

-- nyuka_shukka_total_20151221から、物品ID毎に日付のMAX(連番のMAX)でレコードを取得 :1732
SELECT s_id,nyuka_type,buppin_id,date_modified,nyuka_id,nyuuka_jissuu,tanka_result,hasso_contacts_id,suryo_sum,zaikosu,idouheikin_tanka 
FROM nyuka_shukka_total_20151221 
WHERE (buppin_id,s_id) IN ( 
	SELECT buppin_id,MAX(s_id) as max_date 
	FROM  nyuka_shukka_total_20151221 GROUP BY buppin_id 
	)
ORDER BY nyuka_type,buppin_id;

-- レプリケーションから外しているテーブルのため、エクスポートして、名前を変更し、次のテーブルにインサートする
-- その際、SQLでエクスポートする（CSVではない）

CREATE TABLE `buppin_idouheikin_tanka_20151221` (
  `s_id` varchar(10) DEFAULT NULL,
  `nyuka_type` varchar(2) DEFAULT NULL,
  `buppin_id` varbinary(36) NOT NULL,
  `date_modified` datetime NOT NULL,
  `nyuka_id` char(36) DEFAULT NULL,
  `nyuuka_jissuu` double DEFAULT NULL,
  `tanka_result` double DEFAULT NULL,
  `hasso_contacts_id` varbinary(50) DEFAULT NULL,
  `suryo_sum` decimal(32,0) DEFAULT NULL,
  `zaikosu` decimal(32,0) DEFAULT NULL,
  `idouheikin_tanka` double DEFAULT NULL,
  KEY `s_id` (`s_id`),
  KEY `buppin_id` (`buppin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 以下はレプリケーションから外してない場合
CREATE TABLE buppin_idouheikin_tanka_20151221
	SELECT s_id,nyuka_type,buppin_id,date_modified,nyuka_id,nyuuka_jissuu,tanka_result,hasso_contacts_id,suryo_sum,zaikosu,idouheikin_tanka 
	FROM nyuka_shukka_total_20151221 
	WHERE (buppin_id,s_id) IN ( 
		SELECT buppin_id,MAX(s_id) as max_date 
		FROM  nyuka_shukka_total_20151221 GROUP BY buppin_id 
		)
	ORDER BY nyuka_type,buppin_id;

-- buppin_without_20151221に対象外の物品を追加　4877->4881
0200001442865
0200001587986
0200001721274
0200002103772

-- 物品と移動平均単価のテーブルから、対象外となる物品を除く :1,423
SELECT nyuka_type,buppin_id,zaikosu,idouheikin_tanka
FROM buppin_idouheikin_tanka_20151221
WHERE buppin_id NOT IN (
	SELECT buppin_id FROM buppin_without_20151221
	)
ORDER BY nyuka_type,buppin_id;

-- 新移動平均単価で物品テーブルを更新
UPDATE
    buppinxx t1,
    (
		SELECT buppin_id,idouheikin_tanka
		FROM buppin_idouheikin_tanka_20151221
		WHERE buppin_id NOT IN (
			SELECT buppin_id FROM buppin_without_20151221
			)
    ) t2
SET 
    t1.idouheikin_tanka = t2.idouheikin_tanka
WHERE
    t1.id = t2.buppin_id