代老王开贴一问：SQL复杂规则问题!

Question

示例数据如下:
品号          品名
3250-01393002 BOT-#58
3250-01360100 BOT-3.4OZ-圆形
3250-01385000 BOT-M205
3250-01384000 BOT-M980
3250-01337000 BOT-TB125(55ML瓶)
3250-0130A000 BOT-W0606B
3240-00182044 COM-0240-雾银-透明
3240-00A65016 COM-0367-4(FOR EYE)-透明/PMS8020C-带镜
3240-00B77006 COM-0439-5(四竖)-雾黑底-雾黑盖透明带镜
3240-00B62020 COM-0479-2-雾银-透明
3240-00B9Y007 COM-0536-6-雾银底-透明盖
3240-00B9S000 COM-BPI0502-PMS8020C底-PMS8020C内座-PMS8020C/透明-带镜
3240-00B9F000 COM-M0441-透明底-雾银盖/带镜
3240-01B33000 COM-M190-1-底-塑胶(PS料)
3240-02B33000 COM-M190-1-盖
3240-00181021 粉盒-0390-雾银底-透明盖 0390
3240-00A68001 粉盒-M350-珍珠银-透明 M350
4340-01013001 粉扑-M351-圆形-粉红色 粉扑
4340-01010009 粉扑-NEW M820 粉扑
4340-01010003 粉扑-长方形-海棉 粉扑
4340-01010008 粉扑-长方形-一面海棉一面植绒-NEW M820 粉扑
4340-01001000 粉扑-方形 FOR 小M890 粉扑
.........................
要依据品名字段内包含的数据取出品名字段内一些数据,规则如下:包含值         取值规则 
BOT            取BOT-后字符串
COM            取第一和第二个'-'间字符串
粉盒           取第一和第二个'-'间字符串
粉扑           直接取粉扑
..........................
依据规则,希望得到结果如下:
品号          品名                                                       取值                                                        
3250-01393002 BOT-#58                                                     #58
3250-01360100 BOT-3.4OZ-圆形                                              3.4OZ-圆形
3250-01385000 BOT-M205                                                    M205
3250-01384000 BOT-M980                                                    M980
3250-01337000 BOT-TB125(55ML瓶)                                           TB125(55ML瓶)
3250-0130A000 BOT-W0606B                                                  W0606B
3240-00182044 COM-0240-雾银-透明                                          0240
3240-00A65016 COM-0367-4(FOR EYE)-透明/PMS8020C-带镜                      0367
3240-00B77006 COM-0439-5(四竖)-雾黑底-雾黑盖透明带镜                      0439
3240-00B62020 COM-0479-2-雾银-透明                                        0479
3240-00B9Y007 COM-0536-6-雾银底-透明盖                                    0536
3240-00B9S000 COM-BPI0502-PMS8020C底-PMS8020C内座-PMS8020C/透明-带镜      BPI0502
3240-00B9F000 COM-M0441-透明底-雾银盖/带镜                                M0441
3240-01B33000 COM-M190-1-底-塑胶(PS料)                                    M190
3240-02B33000 COM-M190-1-盖                                               M190
3240-00181021 粉盒-0390-雾银底-透明盖 0390                                0390
3240-00A68001 粉盒-M350-珍珠银-透明 M350                                M350
4340-01013001 粉扑-M351-圆形-粉红色 粉扑                                粉扑
4340-01010009 粉扑-NEW M820 粉扑                                        粉扑
4340-01010003 粉扑-长方形-海棉 粉扑                                粉扑
4340-01010008 粉扑-长方形-一面海棉一面植绒-NEW M820 粉扑                粉扑
4340-01001000 粉扑-方形 FOR 小M890 粉扑                                粉扑
...............如上,取值规则有400-500种, 我的想法是如果能建立一规则表,然后将取值规则放入一字段中,而后关联后再依据规则取值就好了,但具体如何操作,请高手指点!

Answer 1

case left(品名,4) when 'BOT-' then

Answer 2

Select 
品号,
品名,
(Case When CharIndex('BOT',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,Len(品名))
When CharIndex('COM',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)-1))
When CharIndex(N'粉盒',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)-1))
When CharIndex(N'粉扑',品名)>0 Then N'粉扑'
End) As 取值
From TableName

Answer 3

paoluo,你好!
我的这个规则太多,最少有400-500种规则,这样效率是否很底?我的想法是建立一规则表,一字段放规则,一字段放取值规则,不知道这样可行否,如可行如何实现?

Answer 4

比如说这样:
规则表
规则         取值规则
BOT         SubString(品名,CharIndex('-',品名)+1,Len(品名))
COM         SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)-1))

Answer 5

上面的有點問題，改下先
Create Table TEST
(品号 Varchar(15),
 品名 Nvarchar(100))
Insert TEST Select '3250-01393002', N'BOT-#58'
Union All Select'3250-01360100', N'BOT-3.4OZ-圆形'
Union All Select'3250-01385000', N'BOT-M205'
Union All Select'3250-01384000', N'BOT-M980'
Union All Select'3250-01337000', N'BOT-TB125(55ML瓶)'
Union All Select'3250-0130A000', N'BOT-W0606B'
Union All Select'3240-00182044', N'COM-0240-雾银-透明'
Union All Select'3240-00A65016', N'COM-0367-4(FOR EYE)-透明/PMS8020C-带镜'
Union All Select'3240-00B77006', N'COM-0439-5(四竖)-雾黑底-雾黑盖透明带镜'
Union All Select'3240-00B62020', N'COM-0479-2-雾银-透明'
Union All Select'3240-00B9Y007', N'COM-0536-6-雾银底-透明盖'
Union All Select'3240-00B9S000', N'COM-BPI0502-PMS8020C底-PMS8020C内座-PMS8020C/透明-带镜'
Union All Select'3240-00B9F000', N'COM-M0441-透明底-雾银盖/带镜'
Union All Select'3240-01B33000', N'COM-M190-1-底-塑胶(PS料)'
Union All Select'3240-02B33000', N'COM-M190-1-盖'
Union All Select'3240-00181021', N'粉盒-0390-雾银底-透明盖 0390'
Union All Select'3240-00A68001', N'粉盒-M350-珍珠银-透明 M350'
Union All Select'4340-01013001', N'粉扑-M351-圆形-粉红色 粉扑'
Union All Select'4340-01010009', N'粉扑-NEW M820 粉扑'
Union All Select'4340-01010003', N'粉扑-长方形-海棉 粉扑'
Union All Select'4340-01010008', N'粉扑-长方形-一面海棉一面植绒-NEW M820 粉扑'
Union All Select'4340-01001000', N'粉扑-方形 FOR 小M890 粉扑'
GO
Select 
品号,
品名,
(Case When CharIndex('BOT',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,Len(品名))
When CharIndex('COM',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)+1)-CharIndex('-',品名)-1)
When CharIndex(N'粉盒',品名)>0 Then SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)+1)-CharIndex('-',品名)-1)
When CharIndex(N'粉扑',品名)>0 Then N'粉扑'
End) As 取值
From TEST
GO
Drop Table TEST
--Result
/*
品号 品名 取值
3250-01393002 BOT-#58 #58
3250-01360100 BOT-3.4OZ-圆形 3.4OZ-圆形
3250-01385000 BOT-M205 M205
3250-01384000 BOT-M980 M980
3250-01337000 BOT-TB125(55ML瓶) TB125(55ML瓶)
3250-0130A000 BOT-W0606B W0606B
3240-00182044 COM-0240-雾银-透明 0240
3240-00A65016 COM-0367-4(FOR EYE)-透明/PMS8020C-带镜 0367
3240-00B77006 COM-0439-5(四竖)-雾黑底-雾黑盖透明带镜 0439
3240-00B62020 COM-0479-2-雾银-透明 0479
3240-00B9Y007 COM-0536-6-雾银底-透明盖 0536
3240-00B9S000 COM-BPI0502-PMS8020C底-PMS8020C内座-PMS8020C/透明-带镜 BPI0502
3240-00B9F000 COM-M0441-透明底-雾银盖/带镜 M0441
3240-01B33000 COM-M190-1-底-塑胶(PS料) M190
3240-02B33000 COM-M190-1-盖 M190
3240-00181021 粉盒-0390-雾银底-透明盖 0390 0390
3240-00A68001 粉盒-M350-珍珠银-透明 M350 M350
4340-01013001 粉扑-M351-圆形-粉红色 粉扑 粉扑
4340-01010009 粉扑-NEW M820 粉扑 粉扑
4340-01010003 粉扑-长方形-海棉 粉扑 粉扑
4340-01010008 粉扑-长方形-一面海棉一面植绒-NEW M820 粉扑 粉扑
4340-01001000 粉扑-方形 FOR 小M890 粉扑 粉扑
*/

Answer 6

可以这么放规则表规则           取值规则
BOT            SubString(品名,CharIndex('-',品名)+1,Len(品名))
COM            SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)-1))
粉盒           SubString(品名,CharIndex('-',品名)+1,CharIndex('-',品名,CharIndex('-',品名)-1))
粉扑           N'粉扑'查询：
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' when 品名 like '''+规则+'%'' then '+取值规则
from 规则表
exec('Select 
品号,
品名,
(Case '+@sql+'
End) As 取值
From TableName
')

Answer 7

--提供思路供参考
--创建一个函数如下
create function fn_spit(
@split char(1)='-',--分隔符
@start int,--起始位置
@end int=0 --末位置(0：表示到字符结尾,1：表示到第一个分隔符后面依此类推)
)
returns varchar(4000)
as
begin
declare @s varchar(4000)return @s
end
go
--创建一个表如下
create table t1(pinming varchar(10),split char(1),start int,eend int)
insert into t1
select 'BOT','-',1,0 union 
select 'COM','-',1,2 union
select '粉盒','-',1,2 union 
select '粉扑','-',0,1create table t2(pinming varchar(10),split char(1),start int,eend int)
insert into t2
select 'BOT-asd','-',1,0 union 
select 'COM-asdfd','-',1,2 union
select '粉盒23','-',1,2 union 
select '粉扑wr','-',0,1go
--应用(假设示例表为t0)
update a set 取值=dbo.fn_spit(b.pinming,b.split,b.start,b.eend)
from  t0 a,t1 b where  a.品名 like b.pinming+'%'

Answer 8

create table tt(品号 varchar(20),品名 varchar(100))
insert into tt select '3250-01393002' ,'BOT-#58'
union all select '3250-01360100' ,'BOT-3.4OZ-圆形'
union all select '3250-01385000' ,'BOT-M205'
union all select '3250-01384000' ,'BOT-M980'
union all select '3250-01337000' ,'BOT-TB125(55ML瓶)'
union all select '3250-0130A000' ,'BOT-W0606B'
union all select '3240-00182044' ,'COM-0240-雾银-透明'
union all select '3240-00A65016' ,'COM-0367-4(FOR EYE)-透明/PMS8020C-带镜'
union all select '3240-00B77006' ,'COM-0439-5(四竖)-雾黑底-雾黑盖透明带镜'
union all select '3240-00B62020' ,'COM-0479-2-雾银-透明'
union all select '3240-00B9Y007' ,'COM-0536-6-雾银底-透明盖'
union all select '3240-00B9S000' ,'COM-BPI0502-PMS8020C底-PMS8020C内座-PMS8020C/透明-带镜'
union all select '3240-00B9F000' ,'COM-M0441-透明底-雾银盖/带镜'
union all select '3240-01B33000' ,'COM-M190-1-底-塑胶(PS料)'
union all select '3240-02B33000' ,'COM-M190-1-盖'
union all select '3240-00181021' ,'粉盒-0390-雾银底-透明盖 0390'
union all select '3240-00A68001' ,'粉盒-M350-珍珠银-透明 M350'
union all select '4340-01013001' ,'粉扑-M351-圆形-粉红色 粉扑'
union all select '4340-01010009' ,'粉扑-NEW M820 粉扑'
union all select '4340-01010003' ,'粉扑-长方形-海棉 粉扑'
union all select '4340-01010008' ,'粉扑-长方形-一面海棉一面植绒-NEW M820 粉扑'
union all select '4340-01001000' ,'粉扑-方形 FOR 小M890 粉扑'create table tb(包含值 varchar(20),取值规则 varchar(100))
insert into tb select 'BOT','SubString(品名,CharIndex(''-'',品名)+1,CharIndex(''-'',品名,CharIndex(''-'',品名)-1))'
union all select 'COM','SubString(品名,CharIndex(''-'',品名)+1,CharIndex(''-'',品名,CharIndex(''-'',品名)-1))'declare @sql varchar(8000)
set @sql='select 品号,品名,case '
select @sql=@sql+'when charindex('''+包含值+''',品名)>0 then ('+取值规则+')' from tb
exec(@sql+'end as 取值 from tt')
drop table tb,tt

Answer 9

楼上和Haiwer(海阔天空) 正解,不过遗漏了规则里直接取值部分
结贴.谢过各位大牛:)

Answer 10

这个应该很简单
先用CHARINDEX对包含值检索,再用CASE WHEN,至于怎么取相应规则的值就是SUBSTRING的事情了.
只不过写起来可能烦琐一些.

Answer 11

又长见识了,select @a=@a+'……'
把我的交叉表改了 hoho