こんにちはデータを随時更新していき最終的に日にち別で(重複はされないようにする)更新されたデータのみレコードに残したいのですがどのようにすればいいのでしょうか?
”ON DUPLICATE KEY UPDATE”を試したのですが、更新がされません。
お力を貸して頂ければ大変助かります。
SQL
1MariaDB [test]> insert into plan(name,main_time) 2 -> values('APLAN',15); 3Query OK, 1 row affected (0.00 sec) 4 5MariaDB [test]> insert into plan(name,main_time) values('BPLAN',30); 6Query OK, 1 row affected (0.00 sec) 7 8MariaDB [test]> insert into plan(name,main_time) values('CPLAN',45); 9Query OK, 1 row affected (0.06 sec) 10 11MariaDB [test]> select * from plan; 12+----+-------+-----------+ 13| id | name | main_time | 14+----+-------+-----------+ 15| 1 | APLAN | 15 | 16| 2 | BPLAN | 30 | 17| 3 | CPLAN | 45 | 18+----+-------+-----------+
SQL
1MariaDB [test]> insert into user(name,old ) values('sato',20); 2Query OK, 1 row affected (0.00 sec) 3 4MariaDB [test]> insert into user(name,old ) values('yamada',20); 5Query OK, 1 row affected (0.00 sec) 6 7MariaDB [test]> insert into user(name,old ) values('yoshida',20); 8Query OK, 1 row affected (0.00 sec) 9 10MariaDB [test]> select * from user 11 -> ; 12+----+---------+------+ 13| id | name | old | 14+----+---------+------+ 15| 4 | sato | 20 | 16| 5 | yamada | 20 | 17| 6 | yoshida | 20 | 18+----+---------+------+ 193 rows in set (0.00 sec)
SQL
1MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,2); 2Query OK, 1 row affected (0.07 sec) 3 4MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,3); 5Query OK, 1 row affected (0.01 sec) 6 7MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,2); 8Query OK, 1 row affected (0.00 sec) 9 10MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,3); 11Query OK, 1 row affected (0.01 sec) 12 13MariaDB [test]> select * from play_task; 14+----+------------+---------+---------+ 15| id | play_day | user_id | plan_id | 16+----+------------+---------+---------+ 17| 1 | 2018-05-17 | 4 | 1 | 18| 2 | 2018-05-17 | 4 | 2 | 19| 3 | 2018-05-17 | 4 | 3 | 20| 4 | 2018-05-17 | 4 | 2 | 21| 5 | 2018-05-17 | 4 | 3 | 22+----+------------+---------+---------+ 235 rows in set (0.00 sec)
SQL
1MariaDB [test]> insert into amount_day( 2 -> amount_day, 3 -> APLAN, 4 -> APLANcount, 5 -> BPLAN, 6 -> BPLANcount, 7 -> CPLAN, 8 -> CPLANcmount 9 -> ) 10 -> select 11 -> play_task.play_day, 12 -> sum(case when play_task.plan_id = 1 then plan.main_time end ), 13 -> count(play_task.plan_id = 1 or null ), 14 -> sum(case when play_task.plan_id = 2 then plan.main_time end ), 15 -> count(play_task.plan_id = 2 or null ), 16 -> sum(case when play_task.plan_id = 3 then plan.main_time end ), 17 -> count(play_task.plan_id = 3 or null ) 18 -> from play_task left outer join plan on 19 -> play_task.plan_id = plan.id group by play_day; 20Query OK, 1 row affected (0.00 sec) 21Records: 1 Duplicates: 0 Warnings: 0 22 23MariaDB [test]> select * from amount_day; 24+----+------------+-------+------------+-------+------------+-------+-------------+ 25| id | amount_day | APLAN | APLANcount | BPLAN | BPLANcount | CPLAN | CPLANcmount | 26+----+------------+-------+------------+-------+------------+-------+-------------+ 27| 1 | 2018-05-17 | 15 | 1 | 60 | 2 | 90 | 2 | 28+----+------------+-------+------------+-------+------------+-------+-------------+ 291 row in set (0.00 sec)
こちらから新しくplay_taskテーブルにデータを追加してamount_dayテーブルを更新すると更新がされません。
なおamount_dayテーブルのamount_dayカラムはUNIQUEキーに指定します。UNIQUEキーを指定しない場合更新されるのですが、
同じ日付のレコードがテーブル内に発生します。これを同じ日付のレコードは1つの状態で最終データで更新されている形でテーブルをまとめたいのですが。。可能なのでしょうか?
SQL
1MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,1); 2Query OK, 1 row affected (0.01 sec) 3 4MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,2); 5Query OK, 1 row affected (0.00 sec) 6 7MariaDB [test]> insert into play_task(play_day,user_id,plan_id ) values(20180517,4,3); 8Query OK, 1 row affected (0.01 sec) 9 10MariaDB [test]> select * from play_task; 11+----+------------+---------+---------+ 12| id | play_day | user_id | plan_id | 13+----+------------+---------+---------+ 14| 1 | 2018-05-17 | 4 | 1 | 15| 2 | 2018-05-17 | 4 | 2 | 16| 3 | 2018-05-17 | 4 | 3 | 17| 4 | 2018-05-17 | 4 | 2 | 18| 5 | 2018-05-17 | 4 | 3 | 19| 6 | 2018-05-17 | 4 | 1 | 20| 7 | 2018-05-17 | 4 | 2 | 21| 8 | 2018-05-17 | 4 | 3 | 22+----+------------+---------+---------+ 238 rows in set (0.00 sec) 24 25MariaDB [test]> insert into amount_day( 26amount_day, 27APLAN, 28APLANcount, 29BPLAN, 30BPLANcount, 31CPLAN, 32CPLANcmount ) 33select play_task.play_day, 34sum(case when play_task.plan_id = 1 then plan.main_time end ), 35count(play_task.plan_id = 1 or null ), 36sum(case when play_task.plan_id = 2 then plan.main_time end ), 37count(play_task.plan_id = 2 or null ), 38sum(case when play_task.plan_id = 3 then plan.main_time end ), 39count(play_task.plan_id = 3 or null ) 40from play_task left outer join plan on 41play_task.plan_id = plan.id 42group by play_day 43ON DUPLICATE KEY UPDATE 44APLAN = VALUES (APLAN) and 45APLANcount = values(APLANcount) and 46BPLAN = values(BPLAN) and 47BPLANcount = values(BPLANcount) and 48CPLAN = values(CPLAN) and 49CPLANcmount = values(CPLANcmount); 50Query OK, 2 rows affected (0.00 sec) 51Records: 1 Duplicates: 1 Warnings: 0 52 53MariaDB [test]> select * from amount_day; 54+----+------------+-------+------------+-------+------------+-------+-------------+ 55| id | amount_day | APLAN | APLANcount | BPLAN | BPLANcount | CPLAN | CPLANcmount | 56+----+------------+-------+------------+-------+------------+-------+-------------+ 57| 1 | 2018-05-17 | 0 | 1 | 60 | 2 | 90 | 2 | 58+----+------------+-------+------------+-------+------------+-------+-------------+ 591 row in set (0.00 sec)
長々と読んでいただき本当にありがとうございます。
お手数ですが、よろしくお願いします。
追記create形式
SQL
1MariaDB [test]> create table user( 2 -> id int unsigned not null auto_increment, 3 -> name varchar(50), 4 -> old int unsigned, 5 -> primary key (id) 6 -> ) engine=InnoDB; 7Query OK, 0 rows affected (0.11 sec) 8 9MariaDB [test]> create table plan( 10 -> id int unsigned not null auto_increment, 11 -> name varchar(50), 12 -> main_time int unsigned, 13 -> primary key (id) 14 -> ) engine=InnoDB; 15Query OK, 0 rows affected (0.01 sec) 16 17MariaDB [test]> create table play_task( 18 -> id int unsigned not null auto_increment, 19 -> play_day date, 20 -> user_id int unsigned , 21 -> plan_id int unsigned , 22 -> primary key (id), 23 -> foreign key(user_id) references user(id) 24 -> on update cascade on delete cascade , 25 -> foreign key(plan_id) references plan(id) 26 -> on update cascade on delete cascade 27 -> ) engine=InnoDB; 28Query OK, 0 rows affected (0.01 sec) 29 30MariaDB [test]> create table amount_day( 31 -> id int unsigned not null auto_increment, 32 -> amount_day date, 33 -> APLAN int unsigned , 34 -> APLANcount int unsigned , 35 -> BPLAN int unsigned , 36 -> BPLANcount int unsigned , 37 -> CPLAN int unsigned , 38 -> CPLANcmount int unsigned , 39 -> primary key (id) 40 -> 41 -> ) engine=InnoDB; 42Query OK, 0 rows affected (0.01 sec)
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/05/18 11:06
2018/05/18 11:12
2018/05/18 11:19
2018/05/18 11:54
2018/05/18 12:30
2018/05/18 23:39