有表1(主):
id items iquantity
1 h001 150
2 h003 200
表2(子表):
Pid auto_id in_iquantity
1 1 50
1 2 100
2 3 145
2 4 55
关联之后查询的数据如下
id items iquantity Pid auto_id in_iquantity
1 h001 150 1 1 50
1 h001 150 1 2 100
2 h003 200 2 3 145
2 h003 200 2 4 55
现在我想查询后的结果如下
id items iquantity Pid auto_id in_iquantity
1 h001 150 1 1 50
1 h001 0 1 2 100
2 h003 200 2 3 145
2 h003 0 2 4 55
就是把主表里面的数据只保留一个.其它用零填
id items iquantity
1 h001 150
2 h003 200
表2(子表):
Pid auto_id in_iquantity
1 1 50
1 2 100
2 3 145
2 4 55
关联之后查询的数据如下
id items iquantity Pid auto_id in_iquantity
1 h001 150 1 1 50
1 h001 150 1 2 100
2 h003 200 2 3 145
2 h003 200 2 4 55
现在我想查询后的结果如下
id items iquantity Pid auto_id in_iquantity
1 h001 150 1 1 50
1 h001 0 1 2 100
2 h003 200 2 3 145
2 h003 0 2 4 55
就是把主表里面的数据只保留一个.其它用零填
a.id,a.items,case when exists (select 1 from 表2 where pid=a.id and auto_id<b.auto_id) then 0 else a.iquantity end as a.iquantity,
b.*
from 表1 a,表2 b
where a.id=b.pid
order by a.id,b.auto_id
select a.id ,a.items,(case when b.Auto_ID=c.maxAuto_ID then a.iquantity else 0 end) as iquantity,
b.Pid,b.auto_id,b.in_iquantity
from
table1 a,
table2 b,
(select pid,max(auto_id) as maxAuto_ID from table2 group by pid) c
where a.id=b.pid and b.pid=c.pid
order by a.id
select
a.pid,a.items,
case when exists (select 1 from table2 where pid=a.pid and auto_id<b.auto_id) then 0 else a.iquantity end as iquantity,
b.*
from table1 a,table2 b
where a.pid=b.pid