更新时间:2021-08-23 00:52:41
请阅读我对该问题的评论。
如果你想比较尺寸,你需要的只是:
Please, read my comment to the question.
If youo want to compare Dimensions, all you need to to is:
尺寸
产品次> |   次> | ValueOfDimension 次> |
---|---|---|
1 |   | 1|
1 |   | 3 |
1 |   | 5 |
2 |   | 1 |
2 |   | 3 |
2 | 7 |
维度
来自字符串变量Dimension
for each productProduct | ValueOfDimension | |
---|---|---|
1 | 1 | |
1 | 3 | |
1 | 5 | |
2 | 1 | |
2 | 3 | |
2 | 7 |
Dimension
from string variable
如果您提供更多详细信息,我保证会更新我的答案。
看看这里:
If you provide more details, i'll promise to update my answer.
Have a look here:
DECLARE @tmp TABLE (Product INT IDENTITY(1,1), Dimension VARCHAR(30))
INSERT INTO @tmp (Dimension)
VALUES('1,3,5'),
('1,3,7')
DECLARE @input VARCHAR(30) = '1,5,3'
;WITH FirstCTE AS
(
SELECT Product, CONVERT(INT, LEFT(Dimension, CHARINDEX(',', Dimension)-1)) AS MyValue, RIGHT(Dimension , LEN(Dimension) - CHARINDEX(',', Dimension)) AS Remainder
FROM @tmp
WHERE CHARINDEX(',', Dimension)>0
UNION ALL
SELECT Product, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM FirstCTE
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT Product, CONVERT(INT, Remainder) AS MyValue, NULL AS Remainder
FROM FirstCTE
WHERE CHARINDEX(',', Remainder)=0
),
SecondCTE AS
(
SELECT CONVERT(INT, LEFT(@input, CHARINDEX(',', @input)-1)) AS MyValue, RIGHT(@input, LEN(@input) - CHARINDEX(',', @input)) AS Remainder
WHERE CHARINDEX(',', @input)>0
UNION ALL
SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM SecondCTE
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT CONVERT(INT, Remainder) AS MyValue, NULL AS Remainder
FROM SecondCTE
WHERE CHARINDEX(',', Remainder)=0
)
SELECT t1.Product, t1.MyValue AS MyValue1, t1.Remainder AS Remainder1, t2.MyValue AS MyValue2, t2.Remainder AS Remainder2
FROM FirstCTE AS t1 INNER JOIN SecondCTE AS t2 ON t1.MyValue = t2.MyValue
As you can see, i used CTE[^].
[/EDIT]