玩家任务表 p_task
(每个玩家应该有14条任务记录,需要根据"任务配置表"补全"玩家任务表",原有记录不能修改把每人缺失的任务记录补上)
+-------------+---------+-------------+------------+------------+
| player_guid | task_id | task_status | task_total | active_val |
+-------------+---------+-------------+------------+------------+
| 1000001 | 1 | 1 | 20 | 3 |
| 1000001 | 5 | 1 | 20 | 0 |
| 1000002 | 3 | 1 | 50 | 3 |
| 1000002 | 4 | 1 | 10 | 0 |
| 1000003 | 7 | 1 | 10 | 0 |
| 1000004 | 6 | 1 | 2 | 0 |
| 1000005 | 8 | 1 | 3 | 0 |
| 1000005 | 9 | 1 | 3 | 0 |
| 1000006 | 10 | 1 | 3 | 0 |
| 1000007 | 1 | 1 | 20 | 0 |
+-------------+---------+-------------+------------+------------+
任务配置表 task
+---------+-------------+------------+
| task_id | task_status | task_total |
+---------+-------------+------------+
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 1 | 50 |
| 4 | 1 | 10 |
| 5 | 1 | 10 |
| 6 | 1 | 2 |
| 7 | 1 | 3 |
| 8 | 1 | 3 |
| 9 | 1 | 3 |
| 10 | 1 | 10 |
| 11 | 1 | 3 |
| 12 | 1 | 10 |
| 13 | 1 | 5 |
| 14 | 1 | 5 |
+---------+-------------+------------+
利用到笛卡尔积sql,两张表a,b求笛卡尔积c 也就是关联不加关联条件
然后求出c表除去p_task表的那部分d
然后把d表插入到p_task表
select c.* from (select distinct(a.player_guid),b.*,0 as active_val from p_task a,task b ) c left join p_task d on c.player_guid=d.player_guid and c.task_id=d.task_id where d.task_total is null;
