高分请教一个sql语句的写法,在线等待

2008-09-18 02:25:27 255 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

FreeModbus从入门到实战：手把手教你用STM32实现工业级Modbus RTU通信本文详细介绍了如何使用STM32和FreeModbus实现工业级Modbus RTU通信，涵盖硬件搭建、协议栈移植及优化技巧。重点解析了FreeModbus的移植过程，包括串口驱动适配、定时器配置和工业级稳定性优化，为开发者提供了一套完整的工业通信解决方案。阅读详情



//表结构

CREATE TABLE [dbo].[test_tb](

	[id] [int] IDENTITY(1,1) NOT NULL,

	[height] [int] NULL,

	[width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

	[pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

//测试数据

insert into test_tb

select 0,'5.11','6.11'

union all 

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all 

select 1,'5.22','6.22'

union all 

select 1,'5.22','6.22'

union all 

select 1,'5.22','6.22'

union all

select 1,'5.22','6.22'

union all

select 1,'5.22','6.22'

union all

select 2,'5.33','6.33'

//要的结果
height width pvt
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33

说明一下这个结果的由来,首先按升序取一条(给他加上一个新的ID为1),然后判断除id列表的其它列,如果相同的话,相同的显示在一起
显示结果中最新的列是根据条件自动添加的,还有就是真实的数据中表中的id可能是不连续的

...全文

EWM与SAP ERP的数据集成 1、EWM与 SAPERP的无缝集成-基础数据&业务交易数据 2、SAP ERP设置：连接SAP ERP和EWM必须设置应用程序连接支持-ALE（Application Link Enabling） 3、ERP - EWM仓库数据集成： SAP ERP需要设置配置信息，连接ERP和EWM之间的库存管理应用，基于工厂+库存地点对应一个仓库号来进行配置；在ERP中需要创建一个中间虚拟仓库，代表ERP仓库；“中间虚拟”仓库创建之后，工厂+库存地点的信息... 阅读详情

lixueming3000

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

+ 关注 + 加入社区

切换为时间正序

lixueming3000 2008-09-18

打赏
举报

经过实现数据测试happyflystone,Haiwer符合要求,而且happyflystone的适合不同的字段类型，lgxyz用了临时表,也是一种解决问题的思路,在此感谢各位,结贴给分.

等不到来世 2008-09-18

打赏
举报



select nid=(select count(distinct height) from test_tb where height<=a.height),a.height,a.width,a.pvt from test_tb a



/*

--------------------------------------

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

3	2	5.33      	6.33      

*/

昵称被占用了 2008-09-18

打赏
举报

估计他要按照 width pvt
排序，不是按照height和id

CREATE TABLE [dbo].[test_tb](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [height] [int] NULL,

    [width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

insert into test_tb 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 45,'5.33','6.33'

select (select count(*) from (select distinct width,pvt from test_tb where width<a.width or width=a.width and pvt<=a.pvt) as t) as id

,height,width,pvt from test_tb a order by width,pvt

go



--结果

id          height      width      pvt        

----------- ----------- ---------- ---------- 

1           50          5.11       6.11      

1           50          5.11       6.11      

1           50          5.11       6.11      

1           50          5.11       6.11      

1           50          5.11       6.11      

2           70          5.22       6.22      

2           70          5.22       6.22      

2           70          5.22       6.22      

2           70          5.22       6.22      

2           70          5.22       6.22      

3           45          5.33       6.33      



（所影响的行数为 11 行）

lixueming3000 2008-09-18

打赏
举报

qianjin036a你这个不是我想要的,Garnett_KG 和happyflystone 这个是我想要的,正在测试实际数据,感谢各位

lgxyz 2008-09-18

打赏
举报



select Height,[width],[pvt],idno=identity(int,1,1) into #t  from test_tb 

group by Height,[width],[pvt] order by Height



select ID=idno,a.Height,a.width,a.pvt from test_tb a

left join #t b

  on a.Height=b.Height and a.width=b.width and a.pvt=b.pvt



drop table #t



/*

ID          Height      width      pvt        

----------- ----------- ---------- ---------- 

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

3           2           5.33       6.33      



（所影响的行数为 11 行）

*/

Garnett_KG 2008-09-18

打赏
举报



--这样？

select (

	SELECT COUNT(DISTINCT height) 

	FROM test_tb 

	WHERE height<=a.height

		AND width<=a.width

		AND pvt<=a.pvt

	) as New_ID,

	height,width,pvt

from test_tb a



/*

NewID  height   width            pvt

----------------------------------------

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

1	0	5.11      	6.11      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

2	1	5.22      	6.22      

3	2	5.33      	6.33      



*/

-晴天 2008-09-18

打赏
举报

CREATE TABLE [dbo].[test_tb](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [height] [int] NULL,

    [width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

insert into test_tb 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 50,'5.11','6.11' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 70,'5.22','6.22' 

union all 

select 45,'5.33','6.33'

select height+1,height,width,pvt from test_tb order by height

go

drop table test_tb

/*

            height      width      pvt

----------- ----------- ---------- ----------

46          45          5.33       6.33      

51          50          5.11       6.11      

51          50          5.11       6.11      

51          50          5.11       6.11      

51          50          5.11       6.11      

51          50          5.11       6.11      

71          70          5.22       6.22      

71          70          5.22       6.22      

71          70          5.22       6.22      

71          70          5.22       6.22      

71          70          5.22       6.22      



(11 行受影响)



*/

-晴天 2008-09-18

打赏
举报

[Quote=引用 5 楼 lixueming3000 的回复:]
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]
那,实际的数据是怎么样的呢?

lixueming3000 2008-09-18

打赏
举报

当然不一样了,那是因为测试数据相同的我都放在一起了,请细看

-狙击手- 2008-09-18

打赏
举报

[Quote=引用 5 楼 lixueming3000 的回复:]
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]

[Quote=引用 7 楼 happyflystone 的回复:]
select pid = (select count(distinct CHECKSUM (height,width,pvt))
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]

lixueming3000 2008-09-18

打赏
举报

insert into test_tb
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 45,'5.33','6.33'

yangkunjie 2008-09-18

打赏
举报

感觉你表达的有问题，或者说表达和举例之间存在差距

cl9132008 2008-09-18

打赏
举报

[Quote=引用 3 楼 happyflystone 的回复:]
select pid = (select count(distinct height)
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]

确实如此！有待更正。。。

-狙击手- 2008-09-18

打赏
举报

select pid = (select count(distinct CHECKSUM (height,width,pvt))
from test_tb where id <= a.id)
,*
from test_tb a

-晴天 2008-09-18

打赏
举报

如果源表未排序,则
select height+1,height,width,pvt from test_tb order by height

lixueming3000 2008-09-18

打赏
举报

上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的

fa_ge 2008-09-18

打赏
举报

结果和表的数据不是一样的吗?看了几遍没看出什么不同,楼下继续

-狙击手- 2008-09-18

打赏
举报

select pid = (select count(distinct height)
from test_tb where id <= a.id)
,*
from test_tb a

水族杰纶 2008-09-18

打赏
举报

select IDnew=Height+1, Height,[width], [pvt] from [dbo].[test_tb]
???

-晴天 2008-09-18

打赏
举报

CREATE TABLE [dbo].[test_tb](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [height] [int] NULL,

    [width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

insert into test_tb

select 0,'5.11','6.11'

union all 

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all

select 0,'5.11','6.11'

union all 

select 1,'5.22','6.22'

union all 

select 1,'5.22','6.22'

union all 

select 1,'5.22','6.22'

union all

select 1,'5.22','6.22'

union all

select 1,'5.22','6.22'

union all

select 2,'5.33','6.33'

select height+1,height,width,pvt from test_tb

go

drop table test_tb

/*

(11 行受影响)

            height      width      pvt

----------- ----------- ---------- ----------

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

1           0           5.11       6.11      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

2           1           5.22       6.22      

3           2           5.33       6.33      



(11 行受影响)

*/

是这样么?

高分请教一个sql语句的写法,在线等待

相关推荐