22,207
社区成员
发帖
与我相关
我的任务
分享各位路过大侠请多指教
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
4 99 4 21
5 71 8 21
0 62 1 22
1 33 1 22
2 44 1 22
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
A表是一个销售表，ID 为记录序号PLUID 为交易明细QTY是交易数量SNO位交易单号，
B表是一个营业员表，PLUID 会存在于每笔交易中，求查询：每笔交易的pluid为营业员以下至下一个营业员间的销售属于第一个营业员的
ID,PLUID,QTY,SNO,B.PLUID B.PLUNAME
0 62 1 20 62 AA
1 13 2 20 62 AA
0 63 1 21 63 BB
1 20 1 21 63 BB
2 64 3 21 64 CC
3 22 1 21 64 CC
4 99 4 21 64 CC
5 71 8 21 64 CC
0 62 1 22 62 AA
1 33 1 22 62 AA
2 44 1 22 62
...........
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
4 99 4 21
5 71 8 21
0 62 1 22
1 33 1 22
2 44 1 22
............
B表
PLUID,PLUNAME
62, AA
63, BB
64, CC
..............
A表是一个销售表，ID 为记录序号PLUID 为交易明细QTY是交易数量SNO位交易单号，
B表是一个营业员表，PLUID 会存在于每笔交易中，求查询：每笔交易的pluid为营业员以下至下一个营业员间的销售属于第一个营业员的
ID,PLUID,QTY,SNO,B.PLUID B.PLUNAME
0 62 1 20 62 AA
1 13 2 20 62 AA
0 63 1 21 63 BB
1 20 1 21 63 BB
2 64 3 21 64 CC
3 22 1 21 64 CC
4 99 4 21 64 CC
5 71 8 21 64 CC
0 62 1 22 62 AA
1 33 1 22 62 AA
2 44 1 22 62
...........
...全文
请发表友善的回复…
发表回复
中国风 2010-06-24
- 打赏
- 举报
還是這樣的結果?
use Tempdb
go
--> -->
if not object_id(N'A') is null
drop table A
Go
Create table A([ID] int,[PLUID] int,[QTY] int,[SNO] int)
Insert A
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 union all
select 4,99,4,21 union all
select 5,71,8,21 union all
select 0,62,1,22 union all
select 1,33,1,22 union all
select 2,44,1,22
Go
if not object_id(N'B') is null
drop table B
Go
Create table B([PLUID] int,[PLUNAME] nvarchar(2))
Insert B
select 62,N'AA' union all
select 63,N'BB' union all
select 64,N'CC'
Go
;WITH A2
AS
(
SELECT row=ROW_NUMBER()OVER(ORDER BY (SELECT 1)),* FROM A
)
SELECT
*
FROM
A2 t
CROSS APPLY
(SELECT TOP 1 b.* FROM B INNER JOIN A2 c ON b.PLUID=c.PLUID WHERE c.row<=t.row ORDER BY row desc)t2
/*
row ID PLUID QTY SNO PLUID PLUNAME
-------------------- ----------- ----------- ----------- ----------- ----------- -------
1 0 62 1 20 62 AA
2 1 13 2 20 62 AA
3 0 63 1 21 63 BB
4 1 20 1 21 63 BB
5 2 64 3 21 64 CC
6 3 22 1 21 64 CC
7 4 99 4 21 64 CC
8 5 71 8 21 64 CC
9 0 62 1 22 62 AA
10 1 33 1 22 62 AA
11 2 44 1 22 62 AA
(11 個資料列受到影響)
*/
中国风 2010-06-24
- 打赏
- 举报
use Tempdb
go
--> -->
if not object_id(N'A') is null
drop table A
Go
Create table A([ID] int,[PLUID] int,[QTY] int,[SNO] int)
Insert A
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 union all
select 4,99,4,21 union all
select 5,71,8,21 union all
select 0,62,1,22 union all
select 1,33,1,22 union all
select 2,44,1,22
Go
if not object_id(N'B') is null
drop table B
Go
Create table B([PLUID] int,[PLUNAME] nvarchar(2))
Insert B
select 62,N'AA' union all
select 63,N'BB' union all
select 64,N'CC'
Go
Select a.*,d.[PLUID],d.[PLUNAME]
from A
INNER JOIN (A AS C
INNER JOIN B as D ON C.[PLUID]=D.[PLUID] AND C.ID=0) ON C.SNO=a.SNO
/*
(11 個資料列受到影響)
(3 個資料列受到影響)
ID PLUID QTY SNO PLUID PLUNAME
----------- ----------- ----------- ----------- ----------- -------
0 62 1 20 62 AA
1 13 2 20 62 AA
0 63 1 21 63 BB
1 20 1 21 63 BB
2 64 3 21 63 BB
3 22 1 21 63 BB
4 99 4 21 63 BB
5 71 8 21 63 BB
0 62 1 22 62 AA
1 33 1 22 62 AA
2 44 1 22 62 AA
(11 個資料列受到影響)
*/
永生天地 2010-06-24
- 打赏
- 举报
--建立测试环境
IF OBJECT_ID('ta','U') IS NOT NULL DROP TABLE ta
GO
CREATE TABLE ta
(
id int,
PLUID int,
QTY int,
SNO int
)
GO
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 union all
select 4,99,4,21 union all
select 5,71,8,21 union all
select 0,62,1,22 union all
select 1,33,1,22 union all
select 2,44,1,22
go
--建立测试环境
IF OBJECT_ID('tb','U') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
PLUID int,
PLUNAME varchar(10)
)
GO
insert tb
select 62, 'AA' union all
select 63, 'BB' union all
select 64, 'CC'
--查询 2005可以
;with t1 as
(select id,a.PLUID,QTY,SNO,b.PLUID bPLUID ,PLUNAME,rn=row_number()over(order by getdate())
from ta a left join tb b
on a.PLUID=b.PLUID
)
select id,PLUID,QTY,SNO,
isnull(bPLUID,(select top 1 bPLUID
from t1
where rn<t.rn and bPLUID is not null
order by rn desc)) bPLUID,
isnull(PLUNAME,(select top 1 PLUNAME
from t1
where rn<t.rn and bPLUID is not null
order by rn desc)) PLUNAME
from t1 t
--结果
/*
id PLUID QTY SNO bPLUID PLUNAME
----------- ----------- ----------- ----------- ----------- ----------
0 62 1 20 62 AA
1 13 2 20 62 AA
0 63 1 21 63 BB
1 20 1 21 63 BB
2 64 3 21 64 CC
3 22 1 21 64 CC
4 99 4 21 64 CC
5 71 8 21 64 CC
0 62 1 22 62 AA
1 33 1 22 62 AA
2 44 1 22 62 AA
(11 行受影响)
*/永生天地 2010-06-24
- 打赏
- 举报
--建立测试环境
IF OBJECT_ID('ta','U') IS NOT NULL DROP TABLE ta
GO
CREATE TABLE ta
(
id int,
PLUID int,
QTY int,
SNO int
)
GO
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 union all
select 4,99,4,21 union all
select 5,71,8,21 union all
select 0,62,1,22 union all
select 1,33,1,22 union all
select 2,44,1,22
go
--建立测试环境
IF OBJECT_ID('tb','U') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
PLUID int,
PLUNAME varchar(10)
)
GO
insert tb
select 62, 'AA' union all
select 63, 'BB' union all
select 64, 'CC'
--查询 2005可以
with t1 as
(select id,a.PLUID,QTY,SNO,b.PLUID bPLUID ,PLUNAME,rn=row_number()over(order by getdate())
from ta a left join tb b
on a.PLUID=b.PLUID
)
select id,PLUID,QTY,SNO,
isnull(bPLUID,(select top 1 bPLUID
from t1
where rn<t.rn and bPLUID is not null
order by rn desc)) bPLUID,
isnull(PLUNAME,(select top 1 PLUNAME
from t1
where rn<t.rn and bPLUID is not null
order by rn desc)) PLUNAME
from t1 t
--结果
/*
id PLUID QTY SNO bPLUID PLUNAME
----------- ----------- ----------- ----------- ----------- ----------
0 62 1 20 62 AA
1 13 2 20 62 AA
0 63 1 21 63 BB
1 20 1 21 63 BB
2 64 3 21 64 CC
3 22 1 21 64 CC
4 99 4 21 64 CC
5 71 8 21 64 CC
0 62 1 22 62 AA
1 33 1 22 62 AA
2 44 1 22 62 AA
(11 行受影响)
*/
nightmaple 2010-06-24
- 打赏
- 举报
不好意思，看错题了。。。
试试下面这个吧
试试下面这个吧
select *,PLUID as PLUID2 into #temp from A
declare @pluid int
set @pluid=0
update #temp set @pluid=case when ID=0 then PLUID2 else @pluid end,PLUID2=@pluid
select a.ID,a.PLUID,a.QTY,a.SNO,b.PLUID b.PLUNAME from #temp a
left join B b on a.PLUID2=b.PLUIDnightmaple 2010-06-24
- 打赏
- 举报
select * from A
left join B on A.PLUID=A.PLUIDnightmaple 2010-06-24
- 打赏
- 举报
select * from A
left join B on a.PLUID=b.PLUID
left join B on a.PLUID=b.PLUID
tylzh_gw 2010-06-24
- 打赏
- 举报
谢谢各位大侠，不过还是数据查询结果有错误
我感觉应该使用游标
我感觉应该使用游标
