各位路过大侠请多指教:
A表
ID PLUID QTY SNO
0 62 1 20
1 13 2 20
0 63 1 21
1 20 1 21
2 64 3 21
3 22 1 21
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
求语句写法,查询结果为
ID,PLUID,QTY,SNO,B.PLUID
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 21 3 21 64
3 22 1 21 64
...........
A表
ID PLUID QTY SNO
0 62 1 20
1 13 2 20
0 63 1 21
1 20 1 21
2 64 3 21
3 22 1 21
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
求语句写法,查询结果为
ID,PLUID,QTY,SNO,B.PLUID
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 21 3 21 64
3 22 1 21 64
...........
--> 测试数据:[TA]
if object_id('[TA]') is not null drop table [TA]
create table [TA]([ID] int,[PLUID] int,[QTY] int,[SNO] int)
insert [TA]
select 0,62,1,20 union all
select 1,13,2,20 union all
select 0,63,1,21 union all
select 1,20,1,21 union all
select 2,64,3,21 union all
select 3,22,1,21
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([PLUID] int,[PLUNAME] varchar(2))
insert [TB]
select 62,'AA' union all
select 63,'BB' union all
select 64,'CC'
select A.*,B.PLUID,
isnull(B.PLUID,(select top 1 PLUID from TA where ID<A.ID and SNO=A.SNO order by ID desc))
from [TA] A left join [TB] B on A.PLUID=B.PLUID
/*
ID PLUID QTY SNO PLUID
----------- ----------- ----------- ----------- ----------- -----------
0 62 1 20 62 62
1 13 2 20 NULL 62
0 63 1 21 63 63
1 20 1 21 NULL 63
2 64 3 21 64 64
3 22 1 21 NULL 64(6 行受影响)*/drop table [TA]
drop table [TB]
FROM a t1 right join a t2
ON t1.id+1=t2.id and t1.sno=t2.sno---------------------------------------
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 64 3 21 20
3 22 1 21 64
FROM a t1 left join a t2
ON t1.id=t2.id+1 and t1.sno=t2.sno
order by t1.sno, t1.id;---------------------------------------
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 64 3 21 64
3 22 1 21 64
insert [TA]
select 0,62,1,20 union all
select 1,13,2,20 union all
select 0,63,1,21 union all
select 1,20,1,21 union all
select 2,64,3,21 union all
select 3,22,1,21
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([PLUID] int,[PLUNAME] varchar(2))
insert [TB]
select 62,'AA' union all
select 63,'BB' union all
select 64,'CC'select * from ta left join tb on ta.pluid = tb.pluid
A表
ID PLUID QTY SNO
0 62 1 20
1 13 2 20
0 63 1 21
1 20 1 21
2 64 3 21
3 22 1 21
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
求语句写法,A表是一个销售表,B表是一个营业员表,每笔交易的第一项pluid为营业员以下至下一个营业员间的销售属于第一个营业员的,求查询结果为每个营业员所销售的pluid
ID,PLUID,QTY,SNO,B.PLUID
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 21 3 21 64
3 22 1 21 64
...........
4楼的兄弟写的语句接近准确,但是遇到同一个sno内有多个营业员情况,每个营业员下游多个营业员销售就难以区分了
A表
ID PLUID QTY SNO
0 62 1 20
1 13 2 20
0 63 1 21
1 20 1 21
2 64 3 21
3 22 1 21
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
求语句写法,A表是一个销售表,B表是一个营业员表,每笔交易的第一项pluid为营业员以下至下一个营业员间的销售属于第一个营业员的,求查询结果为每个营业员所销售的pluid
ID,PLUID,QTY,SNO,B.PLUID
0 62 1 20 62
1 13 2 20 62
0 63 1 21 63
1 20 1 21 63
2 64 3 21 64
3 22 1 21 64
...........
不好意思查询结果这里出现一个低级错误,谢谢大家