各位路过大侠请多指教

2010-06-24 10:06:45 121 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

PHY6252开发实战：从零构建BLE串口透传应用本文详细介绍了PHY6252开发板在BLE串口透传应用中的实战经验，包括开发环境搭建、BLE串口透传例程解析、AT指令集实现与扩展、数据透传机制优化以及常见问题排查指南。通过智能家居遥控器项目案例，展示了PHY6252在低功耗物联网设备开发中的实际应用。阅读详情

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
...........

...全文

ThingsBoard 3.1.1版本在window本地运行之TB-Gateway服务搭建(三) TB-gateway服务是TB不可或缺的部分，很多的协议都需要TB-gateway来做分类处理，今天我们就来做TB-Gateway服务搭建。阅读详情

tylzh_gw

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

中国风 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.PLUID

nightmaple 2010-06-24

打赏
举报

select * from A  

left join B on A.PLUID=A.PLUID

nightmaple 2010-06-24

打赏
举报

select * from A
left join B on a.PLUID=b.PLUID

tylzh_gw 2010-06-24

打赏
举报

谢谢各位大侠，不过还是数据查询结果有错误
我感觉应该使用游标

各位路过大侠请多指教

相关推荐