T-SQL 中的变量

2021-09-10 00:00:00 sql variables tsql sql-server

我的表中只有 1 列，在这个表中有像 990x70、980x50 这样的输入.我需要x"左右的值来计算这两个值的英寸.使用此代码，我只从数据库中获取最后注册的条目.如何获取所有条目?(注意:我必须在这个项目中使用变量.)

I have only 1 column in my table, in this table there are inputs like 990x70, 980x50. I need the values left and right of the 'x' to calculate inches of these 2 values. With this code I take only last registered entry from database. How can I get all entries? (Note: I have to use variables in this project.)

   declare @Value1 numeric(18,1)
   declare @Value2 numeric(18,1)


   select 
   @Value2 = SUBSTRING(
       [Values], 
       CHARINDEX('x', [Values]) + 1, 
       LEN([Values])) ,

   @Value1 = SUBSTRING(
       [Values], 
       1, 
       CHARINDEX('x', [Values]) - 1)

   from myTable

   select @Value1=@Value1/(2.54)
   select @Value2=@Value2/(2.54)
   select @Value1,@Value2 from myTable

我的表中有 4 种不同的尺寸，我得到了 4 次相同的结果.我想获得所有结果，而不仅仅是 1.

There are 4 different sizes in my table and I get the same result 4 times. I want to get all results not only 1.

推荐答案

现在，诚然，我不是很清楚你在问什么.但听起来像这样应该可以工作:

Now, admittedly, I'm not totally clear on what you're asking. But it sounds like something like this should work:

SELECT CONVERT(NUMERIC(18,1), SUBSTRING([Values], CHARINDEX('x', [Values]) + 1, LEN([Values]))) / 2.54,
       CONVERT(NUMERIC(18,1), SUBSTRING([Values], 1, CHARINDEX('x', [Values]) - 1)) / 2.54
FROM myTable

这应该和您正在做的事情一样，但没有任何变量(在您的使用中，这些变量本质上是一维的).

That should just do the same thing as you're doing, but without any of the variables (which are, in your usage, inherently one-dimensional).

相关文章