table1
player_id mission_id
1 3
2 4
1 5
1 6 table2
item_id item_num
1 11
2 11
3 11 table3
item_id mission_id item_num
1 3 5
2 4 5
3 5 5
4 6 5 有这么段sql语句
$sql="select a.item_num-b.item_num as num1
from table3 a inner join table2 b on b.item_id=a.item_id
where a.mission_id in(select mission_id from table1 where player_id=$user_id)"; 这样查出来的是错误结果 比预想结果要大好多
我怎么才能得到上述我想要的结果
player_id mission_id
1 3
2 4
1 5
1 6 table2
item_id item_num
1 11
2 11
3 11 table3
item_id mission_id item_num
1 3 5
2 4 5
3 5 5
4 6 5 有这么段sql语句
$sql="select a.item_num-b.item_num as num1
from table3 a inner join table2 b on b.item_id=a.item_id
where a.mission_id in(select mission_id from table1 where player_id=$user_id)"; 这样查出来的是错误结果 比预想结果要大好多
我怎么才能得到上述我想要的结果
player_id mission_id
1 3
2 4
1 5
1 6 table2
item_id item_num
1 11
2 11
3 11 table3
item_id mission_id item_num
1 3 5
2 4 5
3 5 5
4 6 5
player_id mission_id
1 3
2 4
1 5
1 6 table2
item_id item_num
1 11
2 11
3 11 table3
item_id mission_id item_num
1 3 5
2 4 5
3 5 5
4 6 5
首先 table1中select mission_id from table1 where player_id=1 查处
mission_id
3
5
6 然后 在table3中查处 条件是mission_id和上表相同
item_id item_num
1 5
3 5
4 5 最后和table2中相减 条件是item_id和上表相同 num
6
from table2 left join (
select item_id
from table1,table3
where table1.mission_id=table3.mission_id
and table1.player_id=1) t on table2.item_id=t.item_id
where t.item_id is null
from table2 left join (
select item_id
from table1 inner join table3 on table1.mission_id=table3.mission_id
where table1.player_id=1) t on table2.item_id=t.item_id
where t.item_id is nullselect table2.item_id
from table2
where item_id not in (
select item_id
from table1 inner join table3 on table1.mission_id=table3.mission_id
where table1.player_id=1
)