如何为选择两个表数据创建过程 [英] how to create procedue for select two table data

问题描述

问题是当我们选择颜色名称并且表中的colorid为8.2或多个颜色ID时
这是我的桌子

problem is when we select color name and colorid is 8,2 or more than one color id in table
it is my table

modelid  modelno            colorid

66	S108	                 1
67	S108-Metallic Red	8,2
68	Sleec	                9
69	Sleec-White	        1
70	Vibe                    2
71	Vibe-Black	        6
84	Aura	                2
114	S09 Metallic Red	1,2
115	S09 test	        1
117	S09 mobile tester	1
120	S08	                1,2
121	S09 mobile	        1

色表

color table

colorid    color name
1	Metallic Red
2	Black
3	Military Green
4	Golden Yellow
5	Metallic Blue
6	Gold
7	Special Black
8	White
9	Dark Silver
10	Pink
11	Dark Brown

如何选择颜色名称的型号
在此先感谢

how to select model with color name
thank in advance

推荐答案

请参阅此 ^ ]以获得各种想法.

Refer to this post[^] for various ideas.

我建​​议使用第三张表作为"modelcolor".从模型表中删除colorid列，并沿着这些行创建第三个表

I''d suggest using a third table for ''modelcolor''. Remove the colorid column from your model table and create a third table along these lines

id        modelid        colorid
1         67             8
2         67             9

您现在可以像这样查询...

You can now query like so...

SELECT 
    modelid,  modelno, colorname
FROM 
    Model m
INNER JOIN
    ModelColor mc ON M.modelid = MC.modelid
INNER JOIN
    Color C ON MC.colorid = C.colorID

现在，您的数据已规范化，此SQL将返回所有模型和可能的颜色选项

Your data is now normalised and this SQL will return all the models and possible color options

此处为:

Here it is :

select modelid, modelno, s.colorid, [color name] from (
select modelid, modelno ,colorId from
(
  SELECT
     modelno, modelid, CAST('<r>' + REPLACE(colorid, ',', '</r><r>') + '</r>' AS XML) coloridXml
  FROM Model
) newColorid
CROSS APPLY (
  SELECT
    colorId.value('.', 'int') colorId
  FROM newColorid.coloridXml.nodes('r') AS colorIds(colorId)
) splited
) s  inner join color c on s.colorid= c.colorid

希望对您有所帮助.

Hope it helps.

这篇关于如何为选择两个表数据创建过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！