我現在有二個表一個物料表（ITEM_LN1），一個顏色表（COLOR1）其中有字段colorid,colorname, 其中物料表裡有個字段item_name
它的值是這樣寫的（abc b01) 看清楚了，前面三個字母加一個空格加後面三個字母（後面這三個字母是顏色表的主鍵ID）
難點就是寫一個SQL語句關連到這二張表，既可以把item_name這個字段的值取出顏色ID，又可以根據這個ID查出ID所對應的顏色名字item_name colorid colorname
望高手指點，，在線等。。
它的值是這樣寫的（abc b01) 看清楚了，前面三個字母加一個空格加後面三個字母（後面這三個字母是顏色表的主鍵ID）
難點就是寫一個SQL語句關連到這二張表，既可以把item_name這個字段的值取出顏色ID，又可以根據這個ID查出ID所對應的顏色名字item_name colorid colorname
望高手指點，，在線等。。
select item_name, colorid, colorname from ITEM_LN1 a
join COLOR1 b
on right(rtrim(item_name),3)=colorid
RIGHT(item_name,LEN(item_name)-CHARINDEX(' ',item_name))AS colorid ,colorname
FROM ITEM_LN1 AS I JOIN COLOR1 C ON I.colorid=I.RIGHT(item_name,LEN(item_name)-CHARINDEX(' ',item_name))
item_name, colorid, colorname
from
ITEM_LN1 a,COLOR1 b
where
right(rtrim(item_name),3)=colorid
select
item_name, colorid, colorname
from
ITEM_LN1 a,COLOR1 b
where
ltrim(right(rtrim(item_name),3))=colorid
--最多就四个吧？
select
item_name, colorid, colorname
from
ITEM_LN1 a,COLOR1 b
where
ltrim(right(rtrim(item_name),4))=colorid
select item_name, colorid, colorname
from ITEM_LN1 inner join COLOR1
on right(item_name,len(item_name)-charindex(' ',item_name)) = colorid
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-19 11:29:12=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: ITEM_LN1
if object_id('TEM_LN1') is not null drop table ITEM_LN1
create table ITEM_LN1 (item_name varchar(100) )
insert into ITEM_LN1
select 'def r02' UNION ALL
SELECT 'ABC B01'
go
--> 测试数据: color1
if object_id('tempdb.dbo.color1') is not null drop table color1
create table color1 (colorid varchar(3),colorname varchar(3))
insert into color1
select 'b01','蓝1' union all
select 'b02','蓝2' union all
select 'r02','红2'select * from ITEM_LN1select * from color1
----------------查询------------
--自定义函数
CREATE FUNCTION F_GET(@item_name VARCHAR(8000),@TYPE VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @S VARCHAR(8000)
IF @TYPE='ID'
SELECT @S=STUFF(@item_name,1,CHARINDEX(' ',@item_name),'')
ELSE
SELECT @S=colorname FROM color1 WHERE colorid=STUFF(@item_name,1,CHARINDEX(' ',@item_name),'')
RETURN @S
END--正式查询
SELECT item_name,DBO.F_GET(item_name,'ID') 颜色ID,DBO.F_GET(item_name,'NAME') 颜色名称 FROM ITEM_LN1
----------------结果--------------
/*
item_name 颜色ID 颜色名称
def r02 r02 红2
ABC B01 B01 蓝1
*/
select a.item_name,b.colorid,b.colorname
from ITEM_LN1 a,COLOR1 b
where right(a.item_name,3)=b.colorid
colorid colorname
12 BLACK 黑色
R01 RED 紅色
B01 BROWN 啡色
G01 Green 綠色ITEM_LN1表
item_name
TSO01791 NA22501 D01. NA22501
TSO01791 NA22502 B01. NA22502就這二張表，高手們吃完飯幫我看看，等會見
colorid colorname
12 BLACK 黑色
R01 RED 紅色
B01 BROWN 啡色
G01 Green 綠色ITEM_LN1表
item_name
TSO01791 NA22501 D01 NA22501
TSO01791 NA22502 B01 NA22502
INSERT COLOR
SELECT '12 BLACK' , '黑色 ' UNION
SELECT 'R01 RED' , '紅色'UNION
SELECT 'B01 BROWN' , '啡色 'UNION
SELECT 'G01 Green' , '綠色' CREATE TABLE ITEM_LN1(item_name VARCHAR(50))
INSERT ITEM_LN1
SELECT 'TSO01791 NA22501 D01 NA22501' UNION
SELECT 'TSO01791 NA22502 B01 NA22502' --SELECT * FROM COLOR
--SELECT * FROM ITEM_LN1SELECT item_name,colorid,colorname FROM COLOR C,ITEM_LN1 I WHERE CHARINDEX
(','+LEFT(colorid,CHARINDEX(' ',colorid)-1)+',',','+REPLACE(item_name,' ',',')+',')>0
item_name colorid colorname
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
TSO01791 NA22502 B01 NA22502 B01 BROWN 啡色 （所影响的行数为 1 行）?
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-19 12:41:00=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: [color1]
if object_id('[color1]') is not null drop table [color1]
create table [color1] (colorid varchar(3),colorname varchar(5),c3 varchar(4))
insert into [color1]
select '12','BLACK','黑色' union all
select 'R01','RED','紅色' union all
select 'B01','BROWN','啡色' union all
select 'G01','Green','綠色'
go
--> 测试数据: [ITEM_LN1]
if object_id('[ITEM_LN1]') is not null drop table [ITEM_LN1]
create table [ITEM_LN1] (item_name varchar(8000))
insert into [ITEM_LN1]
select 'TSO01791 NA22501 D01 NA22501' union all
select 'TSO01791 NA22502 B01 NA22502'select * from [color1]
select * from [ITEM_LN1]
----------------查询------------
--自定义函数一
--SQL Server Split函数
--Author:zc_0101
--说明：
--支持分割符多字节
--使用方法
--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0')
Create FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100))
RETURNS @tmp TABLE(
ID inT IDENTITY PRIMARY KEY,
short_str varchar(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str,@Long_str)=1
SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
ELSE
SET @long_str_Tmp=@Long_str
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1
SET @long_str_Tmp=@long_str_Tmp+@split_str
ELSE
SET @long_str_Tmp=@long_str_Tmp
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)+@split_str_length
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str
END
RETURN
END
--自定义函数二
CREATE FUNCTION F_GET(@item_name VARCHAR(8000),@TYPE VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @S VARCHAR(8000)
IF @TYPE='ID'
SELECT @S=(select SHORT_STR from DBO.F_SQLSERVER_SPLIT(@item_name,' ') WHERE ID=3)
ELSE
SELECT @S=colorname FROM color1 WHERE colorid=(select SHORT_STR from DBO.F_SQLSERVER_SPLIT(@item_name,' ') WHERE ID=3)
RETURN @S
END--正式查询
SELECT item_name,DBO.F_GET(item_name,'ID') 颜色ID,DBO.F_GET(item_name,'NAME') 颜色名称 FROM ITEM_LN1
----------------------结果--------------------
/*
item_name 颜色ID 颜色名称
TSO01791 NA22501 D01 NA22501 D01 NULL
TSO01791 NA22502 B01 NA22502 B01 BROWN
*/
color1表
colorid colorname
12 黑色
R01 紅色
B01 啡色
G01 綠色
ITEM_LN1表
item_name
NA22501 D01
NA22502 B01 我照你的改了下，報這樣的錯：Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
我改成我的數據庫名也報這樣的錯：Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SABRINA.F_SQLSERVER_SPLIT'.
1樓的：select a.ITEM_CODE（原來的ITEM_NAME） COLOR_CODE（原來的顏色ID）b. COLOR_DESCRIPTION2（之間這個字段太長所以用colorname顏色名） from ITEM_LN1 a
join COLOR1 b
on right(rtrim(a.ITEM_CODE),3)=b.COLOR_CODE
3樓7樓的：select a.ITEM_CODE, b.COLOR_CODE, b.COLOR_DESCRIPTION2 from ITEM_LN1 a,COLOR1 b where ltrim(right(rtrim(a.ITEM_CODE),3))=b.COLOR_CODE
11樓的：select a.ITEM_CODE, b.COLOR_CODE, b.COLOR_DESCRIPTION2 from ITEM_LN1 a inner join COLOR1 b
on right(a.ITEM_CODE,len(a.ITEM_CODE)-charindex(' ',a.ITEM_CODE)) =b.COLOR_CODE全部都是隻顯示了字段名，一條數據也顯示不到，很怪
我的顏色表（COLOR1）這樣的：
COLOR_CODE
R01 RED 紅色
B01 BROWN 啡色
G01 Green 綠色
A02 Aubergine AUBERGINE
Y01 Yellow 黃色
P01 TAUPE 土色
A01 ASH 灰色
C02 Coffee Express Coffee Express
下面的ITEM_LN1表字段名至少有80多個，所以為了你們看，才寫了幾個出來 ITEM_CODE
WA8542 B81.
WA8542 C56.
W15406 B01.
W15406 C17.
W16406 C17.
W2105 B35.
W2105 B51.
N5527 B12.
N5527 B33.
N5526 B12.
N5526 B33.
這是我從數據庫中復制出來的一個字段
怎麼用你們上面的一條也查不出來，為什麼？是哪出問題了
INSERT COLOR
SELECT '12 ' , '黑色 ' UNION
SELECT 'R01 ' , '紅色'UNION
SELECT 'B01 ' , '啡色 'UNION
SELECT 'G01 ' , '綠色' CREATE TABLE ITEM_LN1(item_name VARCHAR(50))
INSERT ITEM_LN1
SELECT 'NA22501 D01' UNION
SELECT 'NA22502 B01' SELECT * FROM COLOR
SELECT * FROM ITEM_LN1SELECT item_name,colorid,colorname FROM COLOR C,ITEM_LN1 I --SELECT ','+REPLACE(item_name,' ',',')+','FROM ITEM_LN1
WHERE CHARINDEX
(','+RTRIM(LTRIM(colorid))+',',','+REPLACE(item_name,' ',',')+',')>0--DROP TABLE COLOR,ITEM_LN1
item_name colorid colorname
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
NA22502 B01 B01 啡色 （所影响的行数为 1 行）
N5526 B33
你这数字还有.这东西
select item_name, colorid, colorname from ITEM_LN1 ti
left join COLOR1 c
on right(rtrim(item_name),3)=colorid
我自己添一條insert into ab values('112','abc b01'); 'abc b01'就沒問題
WHERE CHARINDEX
(','+RTRIM(LTRIM(colorid))+',',','+REPLACE(REPLACE(item_name,' ',','),'.',','))>0这样就行了,
汗一个大哥，你先把上面那两个函数在查询分析器里执行一下，然后再用那个
DBO.F_GET 进行查询！那个是函数名不是数据库名！
Line 8: Incorrect syntax near 'color=#FF0000'.
為什麼加這個'color=#FF0000'.
Invalid object name 'DBO.F_GET'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.F_GET'.
一直是報這個錯，DBO.F_GET，函數我執行過
SELECT ITEM_CODE,DBO.F_GET(ITEM_CODE,'ID') 颜色ID,DBO.F_GET(ITEM_CODE,'NAME') 颜色名称 FROM ITEM_LN1
select * from sysobjects where id in (object_id('F_GET'),object_id('F_SQLSERVER_SPLIT')) 执行一下看是不是有两条结果，如果不是则说明跟没有创建这两个函数
F_SQLSERVER_SPLIT 1451346618 TF 1 2 1612710177 0 0 0 2009-08-19 15:27:32.140 0 0 0
F_GET 1483346732 FN 1 0 1610612736 0 0 0 2009-08-19 15:27:48.263 0 0 0
WHERE CHARINDEX
(','+RTRIM(LTRIM(colorid))+',',','+REPLACE(REPLACE(item_name,' ',','),'.',','))>0我本来是这意思的,
只是改了一下那里
INSERT COLOR
SELECT '12 ' , '黑色 ' UNION
SELECT 'R01 ' , '紅色'UNION
SELECT 'B01 ' , '啡色 'UNION
SELECT 'G01 ' , '綠色' CREATE TABLE ITEM_LN1(item_name VARCHAR(50))
INSERT ITEM_LN1
SELECT 'NA22501 D01.' UNION
SELECT 'NA22502 B01.' SELECT * FROM COLOR
SELECT * FROM ITEM_LN1SELECT item_name,colorid,colorname FROM COLOR C,ITEM_LN1 I --SELECT ','+REPLACE(item_name,' ',',')+','FROM ITEM_LN1
WHERE CHARINDEX
('.'+RTRIM(LTRIM(colorid))+'.','.'+REPLACE(item_name,' ','.'))>0--DROP TABLE COLOR,ITEM_LN1item_name colorid colorname
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
NA22502 B01. B01 啡色 （所影响的行数为 1 行）
WA8084 A16. A16 灰色
WA8084 A16. 米白色
WA8084 B65. B65 咖啡色
WA8084 B65. 米白色
WA8084 B66. B66 深啡色
WA8084 B66. 米白色
WA8084 T09. T09 土色
WA8084 T09. 米白色
WA9701 A16. A16 灰色
WA9701 A16. 米白色
WA9701 B65. B65 咖啡色
WA9701 B65. 米白色
WA9701 B66. B66 深啡色
WA9701 B66. 米白色
WA9701 T09. T09 土色
這樣顯示的
执行
SELECT item_name,DBO.F_GET(item_name,'ID') 颜色ID,DBO.F_GET(item_name,'NAME') 颜色名称 FROM ITEM_LN1报错？我在sql 2000和2005中执行都没有问题
O08 OFF WHITE 米白色
L02 LINEN 米白色
。。
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.F_GET'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.F_GET'.
這個錯誤
W33
O08
L02
12
WA8084 A16. 米白色
WA8084 B65. B65 咖啡色
WA8084 B65. 米白色
WA8084 B66. B66 深啡色
WA8084 B66. 米白色
WA8084 T09. T09 土色
WA8084 T09. 米白色
WA9701 A16. A16 灰色
WA9701 A16. 米白色
WA9701 B65. B65 咖啡色
WA9701 B65. 米白色
WA9701 B66. B66 深啡色
WA9701 B66. 米白色
WA9701 T09. T09 土色
昭你这样的结果,米白色的COLORID都是空的呀???晕
WA8084 A16. A16 灰色
WA8084 A16. 米白色
WA8084 B65. B65 咖啡色
WA8084 B65. 米白色 查出來的也隻有356條，
我查了下item_ln1表，裡面總共有25670條不同的ITEM_CODE
--自定义函数二
CREATE FUNCTION F_GET(@item_name VARCHAR(8000),@TYPE VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @S VARCHAR(8000)
IF @TYPE='ID'
SELECT @S=(select SHORT_STR from DBO.F_SQLSERVER_SPLIT(@item_name,' ') WHERE ID=3)
ELSE
SELECT @S=colorname FROM color1 --这里是你的颜色表名，你改成你的表名了吗？
WHERE colorid=(select SHORT_STR from DBO.F_SQLSERVER_SPLIT(@item_name,' ') WHERE ID=3)
RETURN @S
END
W25 CREAM 米白色
W33 Off White 米白色
O08 OFF WHITE 米白色
064 WINTER WHITE 米白色
L02 LINEN 米白色
H03 HONEYWHEAT 米白色
W24 WINTER WHITE 米白色
W82 Avorio/oro chiaro 米白色
W89 BEIGE#42 米白色
W92 Snow.Honey 米白色
NO2 NOISETTE 米白色
PAR Parchment 米白色
001 IVORY 米白色
065 OFF WHITE 米白色
WF1 Vintage White 米白色
BL9 Vanilla/Brown 米白色/中啡
ZE5 117-ANGEL 米白色
ZE6 102-NATURAL 米白色
WS6 WHITE CLOUD 米白色
P87 PEWTER/SOFT GOLD/IVORY 銀灰色/淺金色/米白色
ZI4 102-NATURAL 米白色
SOA SOAP STONE 米白色
J03 J030 米白色
BL7 Black/Off White 克色/米白色
P98 PANTONE BURGANDY 早紅/杏仁米白色
TD1 TURTLE DOVE 米白色
PAN PANNA 米白色
S00 SOAPSTONE 米白色
CR0 cream 米白色
WP4 F223 OFFWHITE 米白色
EC2 ECRU 米白色
PL1 Plaster 米白色
WC5 Buff 米白色
BSI BLACK/SANDSHELL/IVORY 黑色/沙色/米白色
WG3 VANILLA 米白色
WG6 CREAM 米白色
ZC7 ANGEL 米白色
WF4 BISQUE 米白色
WE2 Panna 005 米白色
ALA ALABASTER 米白色
CR2 CREAM 米白色
ZE1 117-ANGEL/COGNAC 米白色
SOAP STONE 米白色
MP5 Ice/Morus 米白色/深啡色
WP5 F213 OFFWHITE 米白色
SELECTITEM_CODE,COLOR_CODE,COLOR_DESCRIPTION2 FROM COLOR1 C,ITEM_LN1 I--SELECT ','+REPLACE(item_name,' ',',')+','FROM ITEM_LN1
WHERECHARINDEX
(','+RTRIM(LTRIM(COLOR_CODE))+',',','+REPLACE(REPLACE(ITEM_CODE,' ',','),'.',','))>0
查出來都是每條重復了一次，重復的那個就會顯示米白色
WHERE CHARINDEX
('.'+REPLACE(LEFT(RTRIM(LTRIM(colorid)),3),' ','')+'.','.'+REPLACE(item_name,' ','.'))>0也许是楼主的COLORID的问题,可能是因为有空值的问题,,方法应该就是这样了,楼主自己再好好看看,
W25 CREAM 米白色
W33 Off White 米白色
O08 OFF WHITE 米白色
064 WINTER WHITE 米白色
L02 LINEN 米白色
H03 HONEYWHEAT 米白色
W24 WINTER WHITE 米白色
W82 Avorio/oro chiaro 米白色
W89 BEIGE#42 米白色
W92 Snow.Honey 米白色
NO2 NOISETTE 米白色
PAR Parchment 米白色
001 IVORY 米白色
065 OFF WHITE 米白色
WF1 Vintage White 米白色
BL9 Vanilla/Brown 米白色/中啡
ZE5 117-ANGEL 米白色
ZE6 102-NATURAL 米白色
WS6 WHITE CLOUD 米白色
P87 PEWTER/SOFT GOLD/IVORY 銀灰色/淺金色/米白色
ZI4 102-NATURAL 米白色
SOA SOAP STONE 米白色
J03 J030 米白色
BL7 Black/Off White 克色/米白色
P98 PANTONE BURGANDY 早紅/杏仁米白色
TD1 TURTLE DOVE 米白色
PAN PANNA 米白色
S00 SOAPSTONE 米白色
CR0 cream 米白色
WP4 F223 OFFWHITE 米白色
EC2 ECRU 米白色
PL1 Plaster 米白色
WC5 Buff 米白色
BSI BLACK/SANDSHELL/IVORY 黑色/沙色/米白色
WG3 VANILLA 米白色
WG6 CREAM 米白色
ZC7 ANGEL 米白色
WF4 BISQUE 米白色
WE2 Panna 005 米白色
ALA ALABASTER 米白色
CR2 CREAM 米白色
ZE1 117-ANGEL/COGNAC 米白色
SOAP STONE 米白色
MP5 Ice/Morus 米白色/深啡色
WP5 F213 OFFWHITE 米白色你的COLORID不是最前面的三个么?
('.'+REPLACE(LEFT(RTRIM(LTRIM(colorid)),3),' ','')+'.','.'+REPLACE(item_name,' ','.'))>0是不是指1-3位的，如果是，我把3換成10好了，那樣1-10位都可以查得到，省事多了
WHERECHARINDEX
(','+RTRIM(LTRIM(COLOR_CODE))+',',','+REPLACE(REPLACE(ITEM_CODE,' ',','),'.',','))>0 沒有LEFT
select
distinct(ITEM_CODE),COLOR_CODE,COLOR_DESCRIPTION2 FROM COLOR1 C,ITEM_LN1 I --SELECT ','+REPLACE(item_name,' ',',')+','FROM ITEM_LN1
WHERE CHARINDEX
('.'+REPLACE(LEFT(RTRIM(LTRIM(COLOR_CODE)),3),' ','')+'.','.'+REPLACE(ITEM_CODE,' ','.'))>0 and I.INVOICE='WINV001418' and COLOR_CODE!=''
它第二行不是顯示空格嗎，我就把COLOR_CODE設為不為空，就可以不讓它顯示米白色了，這樣應該可以達到要求了，具體還不是很清楚，不管了，遇到問題了在說，哈哈