sql server：选择所述语言的颜色作为项目列表 [英] sql server: select colors in stated language for list of items

查看：62 发布时间：2019/6/15 14:36:17 SQL Server

本文介绍了sql server：选择所述语言的颜色作为项目列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好！

我几乎是新手用sql ..但是尝试建立一个查询..我的知识太复杂了：

问题是处理不同语言的颜色

我有  COLORS  table   颜色 以下字段
 ID EN DE FR PT 
  1  white weiss blanc branco 
  2  black schwarz noir preto 
  3  red rot rouge vermelho

.....

然后我有ITEMS表，其中包括其他产品，有一个颜色字段..一些石灰这个

 ID ItemID模型颜色
  1  MT002 S9500黑色#white＃red 
  2  MZ412 W8 black＃red 
  3  MZ415 W8s black＃white 
  4  MZ499 N9500红色

给出以下参数

语言=FR

SelectedProduct ='MZ412'，'MZ415'

带有查询，我想选择所选产品的型号选定语言的相对颜色;即：我期待这样的输出：

型号CLRS

W8 blanc＃rouge

W8s noir #blanc

我这样做了：

  SELECT  Items.Model，
（ SELECT  fr  FROM 颜色其中 colors.EN  in （ case  何时 charindex（' ＃'，items.Colors）=  0  然后 items.colors  else   left （items.Colors，charindex（' ＃'，items.Colors）-1） end ））
 + ' ＃' +（ SELECT  fr  FROM 颜色 where  colors.EN  in （ case   when  charindex（' ＃'，items.Colors）=  0    items.colors  else   right （items.Colors，charindex（' ＃'， reverse（items.Colors）） -  1） end ）） as  CLRS 
  FROM 项 WHERE  Items.ID  IN （'  MZ412'，'  MZ415'）

有效..但仅限于1或2种颜色。。

如果没有颜色可以修复..但是如果有3种或更多颜色呢？

可以给出路径的概念关注？

谢谢

Jan

解决方案

嗨...

检查下面的SQL Logic ...你可以用一个UDF来吐出价值......

   -   拆分功能 
 创建  function  fnsplitstring（ @ str   nvarchar （max）， @ sep   nvarchar （max））
 返回  table  
  as  
 返回 
   为（
 选择强制转换（ 0  为  bigint ） as  idx1，charindex（ @ sep ， @ str ）idx2 
  union  全部 
 选择 idx2 + 1，charindex（ @ sep ， @ str ，idx2 + 1）
 来自 a 
 其中 idx2> 0 
）
 选择 substring（ @ str ，idx1， coalesce （ nullif （idx2， 0 < /跨度>），升en（ @ str ）+ 1）-idx1） as  value 
 来自 a 
 
   -   示例数据 
  go  
 创建 表 #colors（ID  int ，EN  nvarchar （ 100 ），DE  nvarchar （ 100 ），FR  nvarchar （ 100 ），PT  nvarchar （ 100 ））
 将 插入 #colors（Id，ZH ，DE，FR，PT）
 选择  1 ，N '  white'，N '  weiss'，N '  blanc'，N '  branco'  union   all  
 选择  2 ，N '  black '，N '  schwarz'，N  '  noir'，N '  preto'   union   all  
  select   3 ，N '  red'，N '  rot '，N '  rouge'，N  '  vermelho' 
 
  go  
 创建 表 #Items（ID  int ，ItemID < span class =code-keyword> varchar （ 100 ），Model  varchar （< span class =code-digit> 100 ），颜色 varchar （ 100 ）） 
 
 将 插入 #Items（Id，ItemID，Model，Colors）
 选择  1 ，' < span class =code-string> MT002'，'  S9500'，'  black＃white＃red'  union   all  
 选择  2 ，'  MZ412'，'  W8'，'  black＃red'  union   all  
 选择  3 ，'  MZ415'，'  W8s'，'  black＃white'  union   all  
 选择  4 ，'  MZ499'，'  N9500'，< span class =code-string>'  red'  union   all  
 选择  5 ，'  MZ599'，'  N0500'，' ' 
 
   -   所选语言 
 声明  @ Language   varchar （ 10 ）
 
 选择 @ Language = '  DE' 
 
   -   给定数据 
 选择 ID，EN，DE， FR，PT 来自 #colors 
 选择 ID，ItemID，模型，颜色来自 #Items 
 
   -   必填数据 
 选择 ID，ItemID，模型，颜色，
 isnull（stuff（（ select  N ' ＃' +  case  何时 @ Language = '  EN' 然后 C.EN 
 何时 @ Language = '  DE' 然后 C.DE 
  when  @ Language = '  FR' 然后 C.FR 
 当 @ Language = '  PT' 然后 C.PT 
  else  ' ' 
 结束  as  [ text （）] 
 来自 dbo.fnSplitString（colors，' ＃'）M 
  inner   join  #colors C  ON  C .EN = M.Value  for  xml path（' '））
， 1 ， 1 ，' '）
，' '）
 来自 #Items 
 
   -   删除临时表 
  drop   table  #Colors 
  drop   table  #Items

问候，

GVPrabu

您好，

请查找代码示例。希望它有所帮助

go
创建表颜色（ID int identity （ 1 ， 1 ），EN varchar （ 100 ），DE varchar （ 100 ），FR varchar （ 100 ），PT varchar （ 100 ））
插入颜色值（' white'，' weiss'，' blanc'，' branco'）
插入颜色值（' black'，' schwarz'，' noir'，' preto'）
Insert colors values （' red'，' rot'，' rouge'，' vermelho'）
选择 * 来自 colors
go
创建表项目（ID int identity （ 1 ， 1 ），ItemID varchar （ 100 ），型号 varchar （ 100 ），颜色 varchar （ 100 ））

插入项值（' MT002'，' S9500'，' black＃white＃red'）
插入项< span class =code-keyword> values （' MZ412'，' W8'，' black＃red'）
插入项值（ ' MZ415'，' W8s'，' black＃white'）
插入项值（' MZ499 '，' N9500'，' red'）

go
alter 函数 GetColorCode
（
@ItemID va rchar （ 100 ），
@ Language varchar （ 100 ）
）
返回 < span class =code-keyword> varchar （max）
as
开始
声明 @ retValue varchar （max）

set @ retValue = ' '
声明 @ Length int
声明 @ ColorSeperatorpos int
声明 @ ColorValue varchar （ 100 ）
声明 @ CurrentColor varchar （ 100 ）
选择 @ ColorValue =颜色来自项其中 ItemID = @ItemID

set @ ColorSeperatorpos = CHARINDEX（' ＃'， @ ColorValue ）
（ @ ColorSeperatorpos > 0 ）
开始
选择 @ CurrentColor = SUBSTRING（ @ ColorValue ， 0 ， @ ColorSeperatorpos ）
选择 @retValue = @ retValue +（ case
何时 @ Language = ' EN' then en
@ Language = ' DE' 然后 de
@ Language = ' FR' fr
@ Language = ' PT' 然后 PT
结束）+ ' ＃'
来自 colors 其中 en = @ CurrentColor

设置 @ ColorValue = SUBSTRING（ @ ColorValue ，@ ColorSeperatorpos + 1，LEN（ @ ColorValue ） - @ ColorSeperatorpos ）
设置 @ ColorSeperatorpos = CHARINDEX（' ＃'， @ ColorValue ）
结束

if （ @ ColorValue <> ' '）
开始
选择 @ retValue = @ retValue +（ case
@ Language = ' EN' 然后 en
当 @ Language = ' DE ' 然后 de
@Language = ' FR' 然后 fr
@语言 = ' PT' 然后 PT
end ）+ ' ＃ '
来自 colors 其中 en = @ColorValue
结束

返回子串（ @ retValue ， 0 ，LEN（ @ retValue ））
结束

go

声明 @ Language varchar （ 100 ）= ' fr'
SELECT Items.Model，dbo.GetColorCode（Items.ItemID， @ Language ） FROM 项目

问候，Mahe ...

< blockquote>世界充满了不同的奇妙方法;）

我不喜欢使用 CASE WHEN ... END [ ^ ]声明。我的目的是使用公用表格表达式 [ ^ ]（CTE）。这不完全是你想要的，但它显示了解决问题的方法。

   -   颜色表变量 
  DECLARE   @ colors   TABLE （ID  INT   IDENTITY （ 1 ， 1 ），EN  VARCHAR （ 30 ），DE  VARCHAR （ 30 ），FR  VARCHAR （ 30 ），PT  VARCHAR （ 30 ））
 
  INSERT   INTO   @ colors （EN，DE，FR，PT）
  SELECT  '  white'，'  weiss'，'  blanc'，'  branco' 
  UNION   ALL   SELECT  '  black'，'  schwarz' ，'  noir'，'  preto' 
  UNION   ALL   SELECT  '  red'，' 腐烂，  rouge'，'  vermelho' 
 
   -   < span class =code-comment> items table variable  
  DECLARE   @ items  表（ID  INT   IDENTITY （< span class =code-digit> 1 ， 1 ），ItemID  VARCHAR （< span class =code-digit> 30 ），Model  VARCHAR （ 30 ），颜色 VARCHAR （ 100 ））
 
  INSERT   INTO   @ items （ItemID，型号，颜色）
 < span class =cod e-keyword> SELECT  '  MT002'，'  S9500'，' 黑色#white＃red' 
  UNION   ALL   SELECT  '  MZ412'， '  W8'，'  black＃red ' 
  UNION   ALL   SELECT  '  MZ415'，'  W8s'，'  black＃white' 
  UNION   ALL   SELECT  '  MZ499'，'  N9500'，'  red' 
 
   -   输入参数： 
  DECLARE   @ lng   VARCHAR （ 30 ）
  SET  @ lng = '  FR' 
 
  DECLARE   @ products   VARCHAR （ 30 ）
  SET   @ products  = ' <跨度class =code-string> MT002，MZ412，MZ415' 
 
   -   首次CTE  
   -   将ItemID拆分为单独的行 
;  WITH  SelectedProducts  AS  
（
   -   初始值 
  SELECT   LEFT （ @ products ，CHARINDEX（' ，'， @ products ） -  1） AS  ItemID， RIGHT （ @ products ，LEN（ @ products ） - CHARINDEX（' ，'， @ products ）） AS 剩余
  WHERE  CHARINDEX（' ，'， @ products ）> 0 
  UNION   ALL  
   -  递归部分 
  SELECT   LEFT (Remainder, CHARINDEX(',', Remainder)-1) AS ItemID, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',', Remainder)) AS Remainder 
 \tFROM SelectedProducts 
 \tWHERE CHARINDEX(',', Remainder)>0 
 \tUNION ALL 
 \tSELECT Remainder AS ItemID, NULL AS Remainder 
 \tFROM SelectedProducts 
 \tWHERE CHARINDEX(',', Remainder)=0 
 ), CurrentColors AS 
 --second CTE, split colors  
 \t( 
 \t\t--initial value 
 \t\tSELECT sp.ItemID, i.Model, LEFT(i.Colors, CHARINDEX('#',i.Colors)-1) AS Color, RIGHT(i.Colors, LEN(i.Colors)-CHARINDEX('#',i.Colors)) AS CRemainder 
 \t\tFROM SelectedProducts AS sp INNER JOIN @items AS i ON i.ItemID = sp.ItemID 
 \t\tWHERE CHARINDEX('#',i.Colors)>0 
 \t\tUNION ALL 
 \t\t--recursive part 
 \t\tSELECT ItemID, Model, LEFT(CRemainder, CHARINDEX('#',CRemainder)-1) AS Color, RIGHT(CRemainder, LEN(CRemainder)-CHARINDEX('#',CRemainder)) AS CRemainder 
 \t\tFROM CurrentColors 
 \t\tWHERE CHARINDEX('#',CRemainder)>0 
 \t\tUNION ALL 
 \t\tSELECT ItemID, Model, CRemainder AS Color, NULL AS CRemainder 
 \t\tFROM CurrentColors 
 \t\tWHERE CHARINDEX('#',CRemainder)=0 
 \t) 
 --final result set 
 \t\tSELECT cc.ItemID, cc.Model, cc.Color AS EN, c.DE, c.FR, c.PT 
 \t\tFROM CurrentColors AS cc INNER JOIN @colors AS c ON cc.Color=c.EN 
 \t\tORDER BY cc.ItemID

Result:

ItemID  Model   EN      DE      FR      PT 
 MT002\tS9500\tblack\tschwarz\tnoir\tpreto 
 MT002\tS9500\twhite\tweiss\tblanc\tbranco 
 MT002\tS9500\tred\trot\trouge\tvermelho 
 MZ412\tW8\tred\trot\trouge\tvermelho 
 MZ412\tW8\tblack\tschwarz\tnoir\tpre to 
 MZ415\tW8s\tblack\tschwarz\tnoir\tpreto 
 MZ415\tW8s\twhite\tweiss\tblanc\tbranco

By The Way: i would suggest you to change design of Items table. Instead using english names of colors, use them ID. Also, colors column should store values:

1#2#3 
 2#3 
 ...etc

Why? It is easiest to fetch color by its ID, than its name (in English).

If you would like to fetch color Id’s in any language, you can use UNPIVOT[^] table to achieve that:

SELECT ID, ColorName, Lngg 
 FROM ( 
     SELECT * 
     FROM @colors 
     ) AS pvt 
 UNPIVOT(ColorName FOR Lngg IN([EN],[DE],[FR],[PT])) AS unpvt 
 WHERE Lngg = @lng

Result (in case of ’FR’ as an input parameter):

ID      ColorName\tLngg 
 1\tblanc\t\tFR 
 2\tnoir\t\tFR 
 3\trouge\t\tFR

Hi everybody!

I'm almost newbie with sql.. but trying build a query.. too complicated for my knowledge:

Issue is concering handling colors in different languages

I have "COLORS" table with colors with following fields
ID	EN	DE	FR	PT
1	white	weiss	blanc	branco
2	black	schwarz	noir	preto
3	red	rot	rouge	vermelho

.....

then I have "ITEMS" table with products that, among others, has a "colors" field.. something lime this

ID	ItemID	Model	Colors
1	MT002	S9500	black#white#red
2 	MZ412	W8	black#red
3	MZ415	W8s	black#white
4       MZ499   N9500   red

given the following parameters

Language="FR"
SelectedProduct="'MZ412','MZ415'"

with a query , I want select the models of the selected product with relative colors in selected language; ie: I expect something like this as output:

Model CLRS
W8 blanc#rouge
W8s noir#blanc

I did this:

SELECT Items.Model, 
(SELECT fr  FROM Colors where colors.EN in (case when charindex('#',items.Colors) = 0 then items.colors else left(items.Colors,charindex('#',items.Colors)-1)end )) 
+'#'+(SELECT fr  FROM Colors where colors.EN in (case when charindex('#',items.Colors) = 0 then items.colors else right(items.Colors,charindex('#',reverse(items.Colors))-1)end )) as CLRS 
FROM Items WHERE Items.ID IN ('MZ412','MZ415')

that works.. but only if have 1 or 2 colors..
can fix if have no color.. but what about if have 3 or more colors?

can give an idea of the path to follow?

Thanks

Jan

解决方案

hi...

check the below SQL Logic... you can use one UDF for Spit the values....

-- split function
create function fnsplitstring(@str nvarchar(max),@sep nvarchar(max))
returns table
as
return
with a as(
	select cast(0 as bigint) as idx1,charindex(@sep,@str) idx2
	union all
	select idx2+1,charindex(@sep,@str,idx2+1)
	from a
	where idx2>0
)
select substring(@str,idx1,coalesce(nullif(idx2,0),len(@str)+1)-idx1) as value
from a

-- Sample Data
go
Create Table #colors (ID int , EN nvarchar(100), DE nvarchar(100),FR nvarchar(100), PT nvarchar(100))
Insert into #colors(Id, EN,DE,FR,PT)
select 1,N'white',N'weiss',N'blanc',N'branco' union all 
select 2,N'black',N'schwarz',N'noir',N'preto' union all 
select 3,N'red',N'rot',N'rouge',N'vermelho'

go
Create Table #Items(ID int ,ItemID varchar(100), Model varchar(100),Colors varchar(100))
 
Insert into #Items (Id, ItemID, Model, Colors)
select 1,'MT002','S9500','black#white#red' union all
select 2,'MZ412','W8','black#red' union all
select 3,'MZ415','W8s','black#white' union all
select 4,'MZ499','N9500','red' union all
select 5,'MZ599','N0500','' 

-- Selected Language
declare @Language varchar(10)

select @Language='DE'

-- Given Data
select ID, EN,DE,FR,PT from #colors  
select ID, ItemID, Model, colors from #Items

-- Required Data
select ID, ItemID, Model, colors,
	isnull(stuff((select N'#'+ case when @Language='EN' then C.EN
							        when @Language='DE' then C.DE
							        when @Language='FR' then C.FR
							        when @Language='PT' then C.PT
							   else ''
							   end as [text()]
				  from dbo.fnSplitString(colors,'#') M
				  inner join #colors C ON C.EN=M.Value  for xml path('')) 
		  ,1,1,'')
	,'')
from  #Items

-- Drop temp tables
drop table #Colors
drop table #Items

Regards,
GVPrabu

Hi,
Please find the code sample. Hope it helps

go
Create Table colors (ID int identity(1,1), EN varchar(100), DE varchar(100),FR	varchar(100), PT varchar(100))
Insert colors values('white','weiss','blanc','branco')
Insert colors values('black','schwarz','noir','preto')
Insert colors values('red','rot','rouge','vermelho')
select * from colors 
go
Create Table Items(ID int identity(1,1),ItemID varchar(100), Model varchar(100),Colors varchar(100))
 
Insert Items values('MT002','S9500','black#white#red')
Insert Items values('MZ412','W8','black#red')
Insert Items values('MZ415','W8s','black#white')
Insert Items values('MZ499','N9500','red')


go
 alter Function GetColorCode
 (
 @ItemID varchar(100),
 @Language varchar(100)
 )
 returns varchar(max)
 as
 Begin 
       Declare @retValue varchar(max)
		
	set @retValue = ''
	Declare @Length int
	Declare @ColorSeperatorpos int
	Declare @ColorValue varchar(100)
	Declare @CurrentColor varchar(100)
	Select @ColorValue  = Colors from Items where  ItemID   = @ItemID
		
	set  @ColorSeperatorpos = CHARINDEX('#', @ColorValue)
	While  (@ColorSeperatorpos  > 0)
	Begin 
  	        Select @CurrentColor  = SUBSTRING(@ColorValue, 0, @ColorSeperatorpos)
		Select @retValue =@retValue   + ( case 
		                                   when @Language = 'EN' then en 
  					           when @Language = 'DE' then de 
  					           when @Language = 'FR' then fr 
 						   when @Language = 'PT' then PT
						end  )   + '#'
			from colors where en =  @CurrentColor

			Set @ColorValue = SUBSTRING(@ColorValue, @ColorSeperatorpos+1, LEN(@ColorValue)- @ColorSeperatorpos)
			Set @ColorSeperatorpos =  CHARINDEX('#', @ColorValue)
		End
		
		if(@ColorValue <> '' )
		Begin 
		     Select @retValue =@retValue   + ( case 
							when @Language = 'EN' then en 
							when @Language = 'DE' then de 
							when @Language = 'FR' then fr 
							when @Language = 'PT' then PT
							end  )   + '#'
			from colors where en =  @ColorValue
		End
		
		return Substring(@retValue,0,LEN(@retValue) )
 End

go 
 
Declare @Language varchar(100) ='fr'
SELECT Items.Model, dbo.GetColorCode( Items.ItemID, @Language) FROM Items

Regards,Mahe...

The World is full of different wonderful approaches ;)

I don't like to use CASE WHEN ... END[^] statement. My purposal is to use Common Table Expressions[^] (CTE). It isn't exactly what you want, but it shows the way how to resolve your problem.

--colors table variable
DECLARE @colors TABLE (ID INT IDENTITY(1,1), EN VARCHAR(30), DE VARCHAR(30), FR VARCHAR(30), PT VARCHAR(30))

INSERT INTO @colors (EN, DE, FR, PT)
SELECT 'white', 'weiss', 'blanc', 'branco'
UNION ALL SELECT 'black', 'schwarz', 'noir', 'preto'
UNION ALL SELECT 'red', 'rot', 'rouge', 'vermelho'

--items table variable
DECLARE @items TABLE(ID INT IDENTITY(1,1), ItemID VARCHAR(30), Model VARCHAR(30), Colors VARCHAR(100))

INSERT INTO @items (ItemID, Model, Colors)
SELECT 'MT002', 'S9500', 'black#white#red'
UNION ALL SELECT 'MZ412', 'W8', 'black#red'
UNION ALL SELECT 'MZ415', 'W8s', 'black#white'
UNION ALL SELECT 'MZ499', 'N9500', 'red'

--input parameters:
DECLARE @lng VARCHAR(30)
SET @lng='FR'

DECLARE @products VARCHAR(30)
SET @products = 'MT002,MZ412,MZ415'

--first CTE
--split ItemID into separate rows
;WITH SelectedProducts AS
(
	--initial values
	SELECT LEFT(@products, CHARINDEX(',', @products)-1) AS ItemID, RIGHT(@products, LEN(@products)-CHARINDEX(',', @products)) AS Remainder
	WHERE CHARINDEX(',', @products)>0
	UNION ALL
	--recursive part
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ItemID, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',', Remainder)) AS Remainder
	FROM SelectedProducts
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS ItemID, NULL AS Remainder
	FROM SelectedProducts
	WHERE CHARINDEX(',', Remainder)=0
), CurrentColors AS
--second CTE, split colors 
	(
		--initial value
		SELECT sp.ItemID, i.Model, LEFT(i.Colors, CHARINDEX('#',i.Colors)-1) AS Color, RIGHT(i.Colors, LEN(i.Colors)-CHARINDEX('#',i.Colors)) AS CRemainder
		FROM SelectedProducts AS sp INNER JOIN @items AS i ON i.ItemID = sp.ItemID
		WHERE CHARINDEX('#',i.Colors)>0
		UNION ALL
		--recursive part
		SELECT ItemID, Model, LEFT(CRemainder, CHARINDEX('#',CRemainder)-1) AS Color, RIGHT(CRemainder, LEN(CRemainder)-CHARINDEX('#',CRemainder)) AS CRemainder
		FROM CurrentColors
		WHERE CHARINDEX('#',CRemainder)>0
		UNION ALL
		SELECT ItemID, Model, CRemainder AS Color, NULL AS CRemainder
		FROM CurrentColors
		WHERE CHARINDEX('#',CRemainder)=0
	)
--final result set
		SELECT cc.ItemID, cc.Model, cc.Color AS EN, c.DE, c.FR, c.PT
		FROM CurrentColors AS cc INNER JOIN @colors AS c ON cc.Color=c.EN
		ORDER BY cc.ItemID

Result:

ItemID  Model   EN      DE      FR      PT
MT002	S9500	black	schwarz	noir	preto
MT002	S9500	white	weiss	blanc	branco
MT002	S9500	red	rot	rouge	vermelho
MZ412	W8	red	rot	rouge	vermelho
MZ412	W8	black	schwarz	noir	preto
MZ415	W8s	black	schwarz	noir	preto
MZ415	W8s	white	weiss	blanc	branco

By The Way: i would suggest you to change design of Items table. Instead using english names of colors, use them ID. Also, colors column should store values:

1#2#3
2#3
...etc

Why? It is easiest to fetch color by its ID, than its name (in English).

If you would like to fetch color Id's in any language, you can use UNPIVOT[^] table to achieve that:

SELECT ID, ColorName, Lngg
FROM (
    SELECT *
    FROM @colors
    ) AS pvt
UNPIVOT(ColorName FOR Lngg IN([EN],[DE],[FR],[PT])) AS unpvt
WHERE Lngg = @lng

Result (in case of 'FR' as an input parameter):

ID      ColorName	Lngg
1	blanc		FR
2	noir		FR
3	rouge		FR

这篇关于sql server：选择所述语言的颜色作为项目列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

sql server：选择所述语言的颜色作为项目列表 [英] sql server: select colors in stated language for list of items

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

sql server：选择所述语言的颜色作为项目列表 [英] sql server: select colors in stated language for list of items

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭