如何在SQL中将值转换为1 [英] How to convert the values to 1 in SQL

问题描述

我在将下面的数据转换为整数时遇到了困难。在我创建的存储过程中，我需要将检索到的数据转换为整数（1,2,3,4），而不使用更新表函数。



例如，我有数据

1345

1345

1345

1346

1346

1346

1347

1347

1347

1348
1348

1348



我希望将所有1345转换为1,1346转换为2,1347转换为3和1348转换为4.

如何创建查询以显示如下输出？



1

1

1

2

2

2

3

3

3

4

4

4

Hi, I'm facing difficulty in converting the data below to integer. In the stored procedure I've created, I need to convert the data retrieved to integer (1,2,3,4), without using update table function.

For example, I have the data
1345
1345
1345
1346
1346
1346
1347
1347
1347
1348
1348
1348

I wish to convert all 1345 to 1, 1346 to 2, 1347 to 3 and 1348 to 4.
How to create a query to get the output displayed as follow?

1
1
1
2
2
2
3
3
3
4
4
4

推荐答案

如果我理解正确，您需要根据数据的最小值列出每个数字序号。如果是这种情况，请考虑以下事项：

If I understand you correctly, you need to list each numbers ordinal based on the minimum value of the data. If that is the case, consider the following:

create table sampledata (
   col1 int
);

insert into sampledata values ( 1345 );
insert into sampledata values ( 1345 );
insert into sampledata values ( 1345 );
insert into sampledata values ( 1346 );
insert into sampledata values ( 1346 );
insert into sampledata values ( 1346 );
insert into sampledata values ( 1347 );
insert into sampledata values ( 1347 );
insert into sampledata values ( 1347 );
insert into sampledata values ( 1348 );
insert into sampledata values ( 1348 );
insert into sampledata values ( 1348 );


select a.col1,
       a.col1 - b.minvalue + 1 as ordinal
from   sampledata a,
       (select min(col1) as minvalue from sampledata) b
order by 1;

添加Mika的解决方案......您应该了解SQL的排名功能：https://msdn.microsoft.com/en-us/library/ms189798.aspx [ ^ ]



使用它们你可以用一个简单的行来解决它：

To add Mika's solution...You should learn about the ranking functions of SQL: https://msdn.microsoft.com/en-us/library/ms189798.aspx[^]

Using them you can solve it with one simple line:

SELECT *, DENSE_RANK () OVER (ORDER BY COL1) AS RANK FROM SAMPLEDATA

（SAMPLEDATA与Mika解决方案相同）

(SAMPLEDATA is the same table from Mika's solution)

这篇关于如何在SQL中将值转换为1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！