:两表连接有关问题
求助:两表连接问题
各位大神,请帮忙一条SQL语句
表A
id1 Partid Qty1
1 1234 -5
4 1234 -6
6 123 -7
表 B
id2 Partid Qty2
2 1234 6
3 1234 3
5 123 8
7 123 10
两表连接条件:表A连接表B,partid相同,大于表A的Id,并且最接近表A的id
id Partid Qty1 id2 Qty2
1 1234 -5 2 6
4 1234 -6
6 123 -7 7 10
------解决方案--------------------
------解决方案--------------------
select a.id,a.Partid,a.Qty1,b.id2,cast(substring(b.qry2,11,10) as money) as Qty2
from A left join (
select b.Partid, Min(b.id) as id, min( right('0000000000'+cast(b.Id as varchar(10)))+cast(b.Qty2 as varchar(10)) ) as qry2 from B,A
where b.Partid=a.Partidand b.ID>a.ID
group by b.PartID) b on a.partid=b.partid
各位大神,请帮忙一条SQL语句
表A
id1 Partid Qty1
1 1234 -5
4 1234 -6
6 123 -7
表 B
id2 Partid Qty2
2 1234 6
3 1234 3
5 123 8
7 123 10
两表连接条件:表A连接表B,partid相同,大于表A的Id,并且最接近表A的id
id Partid Qty1 id2 Qty2
1 1234 -5 2 6
4 1234 -6
6 123 -7 7 10
------解决方案--------------------
with a(id1,Partid,Qty1)as(
select 1,1234,-5 union
select 4,1234,-6 union
select 6,123,-7),
B(id2,Partid,Qty2)as(
select 2,1234,6 union
select 3,1234,3 union
select 5,123,8 union
select 7,123,10)
select *,id2=(select min(id2) from B where a.Partid=Partid and id2>a.id1)
,qty2=(select qty2 from B where a.Partid=Partid and id2 in(select MIN(id2) from B where id2>a.id1))
from a
------解决方案--------------------
select a.id,a.Partid,a.Qty1,b.id2,cast(substring(b.qry2,11,10) as money) as Qty2
from A left join (
select b.Partid, Min(b.id) as id, min( right('0000000000'+cast(b.Id as varchar(10)))+cast(b.Qty2 as varchar(10)) ) as qry2 from B,A
where b.Partid=a.Partidand b.ID>a.ID
group by b.PartID) b on a.partid=b.partid