model modelname partdes
1. wsp 800124 LBL,BLANK,800-LEVEL PN
2. wsp 800144 ASY,ELMECH,PANEL,FIXED CONFIG,CIO1701
3. wsw 870014 SCR,M,PAN,PH, M3.0x 6mm,ZNwPCH
4. wsw 870144 PCBA,Bacardi,Fixed-config
5. was 700144 LBL,DUAL POW CD WARNING,AMALFI
6. was 700144 CABASY, CONSOLE, RJ45
7. was 700144 LBL,DUAL POW CD WARNING,AMALFI
8. www 990144 IC,DRAM-SYN,512Kx32x4,125MHz,TSOP86,R/F
9. www 990144 PCBFAB,O8L,IMAG,BUCKEYE,MOTHERBOARD
10. wst 90144 IC,PPC405GP-3DD,32BIT,200MHz,BGA456
...
現在的問題是﹕怎樣把字段partdes中的最后一位小數點后的數據選出來(而且不能有重復的數據)﹕
如﹕800-LEVEL PN
CIO1701
ZNwPCH
Fixed-config
AMALFI
RJ45
R/F
.....
該用怎樣的SQL語句?
21 个解决方案
#1
该使用游标来
使用具体数据库的语言
比如说SQL SERVER T-SQL
或者读到程序中处理
使用具体数据库的语言
比如说SQL SERVER T-SQL
或者读到程序中处理
#2
本來就是SQL SERVER T-SQL
#3
select distinct right(partdes,len(partdes)-charindex(reverse(partdes))) as description
#4
select reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tablename
#5
to Rotaxe(程序员) ( ) 信誉:100
不行啊﹗報錯﹕ The charindex function requires 2 to 3 arguments.
不行啊﹗報錯﹕ The charindex function requires 2 to 3 arguments.
#6
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
#7
to sdhdy(大江东去...) ( ) 信誉:102
雖然可以但同時報下面的錯誤﹐而且有重復的數據
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
雖然可以但同時報下面的錯誤﹐而且有重復的數據
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#8
to sdhdy(大江东去...) ( ) 信誉:102
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
報錯﹕Server: Msg 174, Level 15, State 1, Line 1
The left function requires 2 arguments.
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
報錯﹕Server: Msg 174, Level 15, State 1, Line 1
The left function requires 2 arguments.
#9
為何沒人幫小弟一把???????各位大大.........
#10
declare @table table(partdes varchar(50))
insert @table values('LBL,BLANK,800-LEVEL PN')
insert @table values('ASY,ELMECH,PANEL,FIXED CONFIG,CIO1701')
insert @table values('SCR,M,PAN,PH, M3.0x 6mm,ZNwPCH')
insert @table values('PCBA,Bacardi,Fixed-config')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('CABASY, CONSOLE, RJ45')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('IC,DRAM-SYN,512Kx32x4,125MHz,TSOP86,R/F')
insert @table values('PCBFAB,O8L,IMAG,BUCKEYE,MOTHERBOARD')
insert @table values('IC,PPC405GP-3DD,32BIT,200MHz,BGA456')
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
/*
--------------------------------------------------
800-LEVEL PN
AMALFI
BGA456
CIO1701
Fixed-config
MOTHERBOARD
R/F
RJ45
ZNwPCH
(所影响的行数为 9 行)
*/
insert @table values('LBL,BLANK,800-LEVEL PN')
insert @table values('ASY,ELMECH,PANEL,FIXED CONFIG,CIO1701')
insert @table values('SCR,M,PAN,PH, M3.0x 6mm,ZNwPCH')
insert @table values('PCBA,Bacardi,Fixed-config')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('CABASY, CONSOLE, RJ45')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('IC,DRAM-SYN,512Kx32x4,125MHz,TSOP86,R/F')
insert @table values('PCBFAB,O8L,IMAG,BUCKEYE,MOTHERBOARD')
insert @table values('IC,PPC405GP-3DD,32BIT,200MHz,BGA456')
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
/*
--------------------------------------------------
800-LEVEL PN
AMALFI
BGA456
CIO1701
Fixed-config
MOTHERBOARD
R/F
RJ45
ZNwPCH
(所影响的行数为 9 行)
*/
#11
to shuiniu(飞扬的梦) ( ) 信誉:100
報錯﹕ Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
報錯﹕ Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#12
嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚.............各位大俠幫幫忙啊﹗小弟有急用啊﹗
#13
那就是你的数据有问题!
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
where ..... --过滤不合法数据!
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
where ..... --过滤不合法数据!
#14
但
select ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table 卻可以
select ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table 卻可以
#15
不会吧!完整的贴上来看看!
#16
但也有一些問題﹕如
(259 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
(259 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#17
检查一下你的substring函数的有参数值不合法!
#18
這怎么检查啊
#19
那你得自己找!
可以部分部分的运行!找出在那一条记录出的错!
可以部分部分的运行!找出在那一条记录出的错!
#20
怎樣部分部分的運行?
#21
运行的时候加一下条件!
举例:
id name
1 a
2 b
3 c
.....
100 ..
where id <=20
where id between 21 and 50
where id >=51
举例:
id name
1 a
2 b
3 c
.....
100 ..
where id <=20
where id between 21 and 50
where id >=51
#1
该使用游标来
使用具体数据库的语言
比如说SQL SERVER T-SQL
或者读到程序中处理
使用具体数据库的语言
比如说SQL SERVER T-SQL
或者读到程序中处理
#2
本來就是SQL SERVER T-SQL
#3
select distinct right(partdes,len(partdes)-charindex(reverse(partdes))) as description
#4
select reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tablename
#5
to Rotaxe(程序员) ( ) 信誉:100
不行啊﹗報錯﹕ The charindex function requires 2 to 3 arguments.
不行啊﹗報錯﹕ The charindex function requires 2 to 3 arguments.
#6
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
#7
to sdhdy(大江东去...) ( ) 信誉:102
雖然可以但同時報下面的錯誤﹐而且有重復的數據
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
雖然可以但同時報下面的錯誤﹐而且有重復的數據
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#8
to sdhdy(大江东去...) ( ) 信誉:102
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
報錯﹕Server: Msg 174, Level 15, State 1, Line 1
The left function requires 2 arguments.
select distinct reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1)) from tabename
報錯﹕Server: Msg 174, Level 15, State 1, Line 1
The left function requires 2 arguments.
#9
為何沒人幫小弟一把???????各位大大.........
#10
declare @table table(partdes varchar(50))
insert @table values('LBL,BLANK,800-LEVEL PN')
insert @table values('ASY,ELMECH,PANEL,FIXED CONFIG,CIO1701')
insert @table values('SCR,M,PAN,PH, M3.0x 6mm,ZNwPCH')
insert @table values('PCBA,Bacardi,Fixed-config')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('CABASY, CONSOLE, RJ45')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('IC,DRAM-SYN,512Kx32x4,125MHz,TSOP86,R/F')
insert @table values('PCBFAB,O8L,IMAG,BUCKEYE,MOTHERBOARD')
insert @table values('IC,PPC405GP-3DD,32BIT,200MHz,BGA456')
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
/*
--------------------------------------------------
800-LEVEL PN
AMALFI
BGA456
CIO1701
Fixed-config
MOTHERBOARD
R/F
RJ45
ZNwPCH
(所影响的行数为 9 行)
*/
insert @table values('LBL,BLANK,800-LEVEL PN')
insert @table values('ASY,ELMECH,PANEL,FIXED CONFIG,CIO1701')
insert @table values('SCR,M,PAN,PH, M3.0x 6mm,ZNwPCH')
insert @table values('PCBA,Bacardi,Fixed-config')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('CABASY, CONSOLE, RJ45')
insert @table values('LBL,DUAL POW CD WARNING,AMALFI')
insert @table values('IC,DRAM-SYN,512Kx32x4,125MHz,TSOP86,R/F')
insert @table values('PCBFAB,O8L,IMAG,BUCKEYE,MOTHERBOARD')
insert @table values('IC,PPC405GP-3DD,32BIT,200MHz,BGA456')
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
/*
--------------------------------------------------
800-LEVEL PN
AMALFI
BGA456
CIO1701
Fixed-config
MOTHERBOARD
R/F
RJ45
ZNwPCH
(所影响的行数为 9 行)
*/
#11
to shuiniu(飞扬的梦) ( ) 信誉:100
報錯﹕ Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
報錯﹕ Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#12
嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚嗚.............各位大俠幫幫忙啊﹗小弟有急用啊﹗
#13
那就是你的数据有问题!
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
where ..... --过滤不合法数据!
select distinct ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table
where ..... --过滤不合法数据!
#14
但
select ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table 卻可以
select ltrim(reverse(left(reverse(rtrim(partdes)),charindex(',',reverse(rtrim(partdes)))-1))) from @table 卻可以
#15
不会吧!完整的贴上来看看!
#16
但也有一些問題﹕如
(259 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
(259 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
#17
检查一下你的substring函数的有参数值不合法!
#18
這怎么检查啊
#19
那你得自己找!
可以部分部分的运行!找出在那一条记录出的错!
可以部分部分的运行!找出在那一条记录出的错!
#20
怎樣部分部分的運行?
#21
运行的时候加一下条件!
举例:
id name
1 a
2 b
3 c
.....
100 ..
where id <=20
where id between 21 and 50
where id >=51
举例:
id name
1 a
2 b
3 c
.....
100 ..
where id <=20
where id between 21 and 50
where id >=51