在select语句中选择多个递增列 [英] selecting multiple incrementing columns in a select statement

问题描述

嗨



我需要一个select语句，可以选择两列作为增量列。

需要这两个字段的两个字段不是表中的列。

他们需要在选择语句中选择。



例如。

< pre lang =text> ID | KeyID |名称|号码|
1 | 1 |伊万| 21
2 | 2 |杰克| 45
3 | 2 |杰克| 45
4 | 3 |将| 74
5 | 3 |将| 65

我引用的两个字段是ID和KeyID字段。

ID字段为每个选定的记录以1为增量生成，我可以使用IDENTTY（int，1,1）。



但是KeyID字段会带来麻烦。

I需要为每个共享名称的记录选择一个KeyID，并为每个新名称增加一个。正如您在示例中看到的那样。再次请记住，这必须在select语句中完成，因为KeyID字段不存在。



关于如何实现这一点的任何建议？



非常感谢您的回复。



这是查询：

  SELECT  
  IDENTITY （ INT ， 1 ， 1 ） as  KeyValueID，
 dense_rank（） OVER （ ORDER   BY  [importdata.dbo.sheet2 $ 2011.ITEM]） AS  KeyID， importdata.dbo.sheet2 $ 2011.ITEM  AS 项目，importdata.dbo.sheet2 $ 2011.ITEMDESC，cast（''  1'  as   int ） as  SortOrder 
  into  #bf_keyValues 
   -  -   （KeyValueID，KeyID，Item，Description，DefaultSortOrder） 
 来自 importdata .dbo.sheet1 $ 2011 
 内部  join  importdata.dbo.sheet2 $ 2011 
 < span class =code-keyword> on  importdata.dbo.sheet1 $ 2011.CUSTOMER = importdata.dbo.sheet2 $ 2011.CUSTOMER 
  inner   join  importdata.dbo.sheet3 $ 2011 
  on  importdata.dbo.sheet1 $ 2011.CUSTOMER = importdata .dbo.sheet3 $ 2011.CUSTOMER 
  order   by  ITEMDESC  asc  
 
 插入 进入 bf_keyValues 
 选择 * 来自 #bf_keyValues 
 
 选择 * 来自 bf_keyValues 

我需要第二个声明（

 dense_rank（） OVER （ ORDER   BY  [importdata.dbo.sheet2 $ 2011.ITEM]） AS  KeyID 
 

），生成一个ITEM列中显示的每个项目的新编号。有多个项目，有些甚至可能重复。

解决方案

2011.ITEM]） AS KeyID，importdata .dbo.sheet2

2011.ITEM AS 项目，importdata.dbo.sheet2

2011.ITEMDESC，强制转换（' 1' as int ） as SortOrder
into #bf_keyValues
- （KeyValueID，KeyID，Item，Description，DefaultSortOrder）
来自 importdata.dbo.sheet1

Hi

I need a select statement that can select two columns as incremental columns.
the two fields that need this are not columns in the table.
they need to be selecting in a select statement.

eg.

ID |  KeyID | Name | Number | 
1  |   1    | Ivan |  21
2  |   2    | Jake |  45
3  |   2    | Jake |  45
4  |   3    | Will |  74
5  |   3    | Will |  65

the two fields i refer to are the ID and KeyID fields.
the ID fields generates in increments of 1 for each record selected , which I can use the IDENTTY(int,1,1) for.

but the trouble comes in with the KeyID fields.
I need to select a KeyID for each record that shares a name and increment it by one for every new name. as you can see in the example. Again keep in mind this has to be done in a select statement as the KeyID field does not exist.

Any advice on how this can be achieved?

Thank you very much for any reply.

This is the querie:

SELECT
IDENTITY(INT,1,1) as KeyValueID,
  dense_rank() OVER(ORDER BY [importdata.dbo.sheet2$2011.ITEM]) AS KeyID,importdata.dbo.sheet2$2011.ITEM AS Item,importdata.dbo.sheet2$2011.ITEMDESC,cast('1' as int) as SortOrder
into #bf_keyValues
--(KeyValueID,KeyID,Item,Description,DefaultSortOrder)
from importdata.dbo.sheet1$2011
inner join importdata.dbo.sheet2$2011
on importdata.dbo.sheet1$2011.CUSTOMER =  importdata.dbo.sheet2$2011.CUSTOMER
inner join importdata.dbo.sheet3$2011
on importdata.dbo.sheet1$2011.CUSTOMER =  importdata.dbo.sheet3$2011.CUSTOMER
order by ITEMDESC asc

insert into bf_keyValues
select * from #bf_keyValues

select * from bf_keyValues

I need the second statement (

dense_rank() OVER(ORDER BY [importdata.dbo.sheet2$2011.ITEM]) AS KeyID

), to generate a new number for every item that appears in the ITEM column. There are Multiple item and some may even repeat.

解决方案

2011.ITEM]) AS KeyID,importdata.dbo.sheet2

2011.ITEM AS Item,importdata.dbo.sheet2

2011.ITEMDESC,cast('1' as int) as SortOrder into #bf_keyValues --(KeyValueID,KeyID,Item,Description,DefaultSortOrder) from importdata.dbo.sheet1

这篇关于在select语句中选择多个递增列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！