乱序身份字段 - sql2000 [英] out of order identity field - sql2000

问题描述

大家好


我在插入到新创建的

表中时发现意外结果，该表具有数据类型为int identity（1,1）的字段。


基本上我在插入表格时排序的顺序不是

反映在身份字段中的值的顺序。


我是否错误地认为它应该反映

排序的顺序？


代码是......


创建表tmp（一个varchar（50），L float，C int identity（1,1））

插入到tmp（A，L）选择Aa，Ll从tmp1顺序Aa，Ll


我不明白为什么tmp.C中的值不在订单中

建议等级。


评论最为赞赏

Bevan

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don''t understand why the values in tmp.C aren''t in the order
suggested by the sort.

Any comments most appreciated
Bevan

推荐答案

尝试订购C

< be ******* @ gmail.com>在消息中写道

新闻：11 ********************* @ h76g2000cwa.googlegro ups.com ...

Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

大家好

我在插入新创建的具有数据类型为int identity（1,1）的字段的表时发现了意想不到的结果。

>基本上我在插入表格时排序的顺序不会按照身份字段中的值的顺序反映出来。

我是否错误地认为它应该反映顺序从
排序？

代码是......

创建表tmp（一个varchar（50），L float，C int identity（1,1） ））插入到tmp（A，L）中选择Aa，Ll从tmp1顺序由Aa，Ll

我不明白为什么tmp.C中的值不是''按顺序排序


任何评论最感谢
Bevan

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don''t understand why the values in tmp.C aren''t in the order
suggested by the sort.

Any comments most appreciated
Bevan

你好Mike


感谢您的评论 - C是
插入我希望会以与

种类相同的顺序递增Aa，Ll


干杯

Bevan


Mike C＃写道：

Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:

尝试订购C

< be ****** *@gmail.com>在消息中写道
新闻：11 ********************* @ h76g2000cwa.googlegro ups.com ...

Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

你好所有

当我插入一个新创建的具有数据类型为int identity（1,1）的字段的表时，我发现了意想不到的结果。

基本上是顺序我在插入表格时不会按照身份字段中的值的顺序反映出来。

我是否错误地认为它应该反映来自排序？

代码是......

创建表tmp（一个varchar（50），L float，C int identity（1,1））
我不明白为什么tmp.C中的值不在顺序中
建议排序。

任何评论最赞赏
Bevan

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don''t understand why the values in tmp.C aren''t in the order
suggested by the sort.

Any comments most appreciated
Bevan

您不能依赖IDENTITY列按特定顺序分配o顺便提一下，顺序没有差距。
。尝试手动分配等级值

代替：


CREATE TABLE #tmp（A VARCHAR（50），

L FLOAT，

C INT NOT NULL PRIMARY KEY）


CREATE TABLE＃tmp1（Aa VARCHAR（50），

Ll FLOAT（50 ），

PRIMARY KEY（Aa，L1））


INSERT INTO＃tmp1（Aa，L1）

SELECT'' ABC''，123.45

UNION SELECT''DEF''，456.12

UNION SELECT''XYZ''，999.99

UNION SELECT' 'RST''，023.43

UNION SELECT''GHI''，146.56


INSERT INTO #tmp（A，L，C）

SELECT t1.Aa，t1.Ll，COUNT（*）Rank

FROM＃tmp1 t1

INNER JOIN＃tmp1 t2

ON t1.Aa> = t2.Aa

AND t2.Ll> = t2.Ll

GROUP BY t1.Aa，t1.Ll

ORDER BY t1.Aa，t1.Ll


SELECT C，A，L

FROM #tmp

订购C


DROP TABLE＃tmp1

DROP TABLE #tmp

< be ******* @ G mail.com>在消息中写道

news：11 ********************* @ i40g2000cwc.googlegro ups.com ...

You can''t rely on an IDENTITY column to be assigned in a particular order or
to not have gaps in the sequence, btw. Try assigning a rank value manually
instead:

CREATE TABLE #tmp (A VARCHAR(50),
L FLOAT,
C INT NOT NULL PRIMARY KEY)

CREATE TABLE #tmp1 (Aa VARCHAR(50),
Ll FLOAT(50),
PRIMARY KEY (Aa, Ll))

INSERT INTO #tmp1 (Aa, Ll)
SELECT ''ABC'', 123.45
UNION SELECT ''DEF'', 456.12
UNION SELECT ''XYZ'', 999.99
UNION SELECT ''RST'', 023.43
UNION SELECT ''GHI'', 146.56

INSERT INTO #tmp (A, L, C)
SELECT t1.Aa, t1.Ll, COUNT(*) Rank
FROM #tmp1 t1
INNER JOIN #tmp1 t2
ON t1.Aa >= t2.Aa
AND t2.Ll >= t2.Ll
GROUP BY t1.Aa, t1.Ll
ORDER BY t1.Aa, t1.Ll

SELECT C, A, L
FROM #tmp
ORDER BY C

DROP TABLE #tmp1
DROP TABLE #tmp
<be*******@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

嗨Mike

感谢您的评论 - C是
插入目标表中的字段，我希望它会以与
排序相同的顺序递增Aa，Ll
干杯
Bevan

Mike C＃写道：

Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:

尝试订购C

<是******* @ gmail.com>在消息中写道
新闻：11 ********************* @ h76g2000cwa.googlegro ups.com ...

Try ORDER BY C
<be*******@gmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

> ;大家好
>
>插入到新创建的
>中时，我发现了意想不到的结果。具有数据类型为int identity（1,1）的字段的表。
>
>基本上我插入表格时排序的顺序不是
>反映在身份字段中的值的顺序。
>
>假设它应该反映来自
>的顺序，我是错误的吗？排序？
>
>代码是......
>
> create table tmp（一个varchar（50），L float，C int identity（1,1））
>插入到tmp（A，L）中选择Aa，Ll从tmp1顺序Aa，Ll
>
>我不明白为什么tmp.C中的值不在顺序中
>通过排序建议。
>
>任何评论最赞赏
> Bevan
>

> Hi All
>
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
>
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
>
> Have I been wrong in assuming that it should reflect the order from the
> sort?
>
> The code is ...
>
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>
> and I don''t understand why the values in tmp.C aren''t in the order
> suggested by the sort.
>
> Any comments most appreciated
> Bevan
>

这篇关于乱序身份字段 - sql2000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！