关于identity(int,1,1)语句的用法，急死人了。
----------------------------------------------------------------------
相同的identity(int,1,1)语句，
第一句返回值正确（my_id按顺序排列）
第二句/第三句 返回值不正确（my_id没有按顺序排列），（只多了一个列a.id_pingm）
请问为什么？
----------------------------------------------------------------------
SELECT identity(int,1,1) as my_id, c.id_bom_b, c.gx_int
into #tmp_shch_cg
FROM cmt_bom_m1 c
left outer join cmt_bom_b a on c.id_bom_b=a.id_bom_b
WHERE a.id_pingm = 'BB10001998'
order by c.id_bom_b, c.gx_int
select * from #tmp_shch_cg order by gx_int
drop table #tmp_shch_cg
----------------------------------------------------------------------
my_id id_bom_b gx_int
1 CA10005208 1
2 CA10005208 2
3 CA10005208 3
4 CA10005208 4
5 CA10005208 5
6 CA10005208 6
7 CA10005208 7
8 CA10005208 8
9 CA10005208 9
10 CA10005208 10
11 CA10005208 11
12 CA10005208 12
13 CA10005208 13
14 CA10005208 14
15 CA10005208 15
16 CA10005208 16
17 CA10005208 17
18 CA10005208 18
19 CA10005208 19
20 CA10005208 20
21 CA10005208 21
22 CA10005208 22
23 CA10005208 23
24 CA10005208 24
25 CA10005208 25
26 CA10005208 26
27 CA10005208 27
28 CA10005208 28
29 CA10005208 29
30 CA10005208 30
31 CA10005208 31
32 CA10005208 32
33 CA10005208 33
34 CA10005208 34
35 CA10005208 35
36 CA10005208 36
37 CA10005208 37
38 CA10005208 38
39 CA10005208 39
40 CA10005208 40
----------------------------------------------------------------------
SELECT identity(int,1,1) as my_id, c.id_bom_b, c.gx_int, a.id_pingm
into #tmp_shch_cg1
FROM cmt_bom_m1 c
left outer join cmt_bom_b a on c.id_bom_b=a.id_bom_b
WHERE a.id_pingm = 'BB10001998'
order by c.id_bom_b, c.gx_int
select * from #tmp_shch_cg1 order by gx_int
drop table #tmp_shch_cg1
----------------------------------------------------------------------
SELECT identity(int,1,1) as my_id, *
into #tmp_shch_cg2
from
(SELECT c.id_bom_b, c.gx_int, a.id_pingm
FROM cmt_bom_m1 c
left outer join cmt_bom_b a on c.id_bom_b=a.id_bom_b
WHERE a.id_pingm = 'BB10001998') b
order by b.id_bom_b, b.gx_int
select * from #tmp_shch_cg2 order by gx_int
drop table #tmp_shch_cg2
----------------------------------------------------------------------
my_id id_bom_b gx_int id_pingm
1 CA10005208 1 BB10001998
26 CA10005208 2 BB10001998
6 CA10005208 3 BB10001998
8 CA10005208 4 BB10001998
27 CA10005208 5 BB10001998
28 CA10005208 6 BB10001998
29 CA10005208 7 BB10001998
2 CA10005208 8 BB10001998
3 CA10005208 9 BB10001998
32 CA10005208 10 BB10001998
4 CA10005208 11 BB10001998
5 CA10005208 12 BB10001998
33 CA10005208 13 BB10001998
7 CA10005208 14 BB10001998
9 CA10005208 15 BB10001998
10 CA10005208 16 BB10001998
11 CA10005208 17 BB10001998
34 CA10005208 18 BB10001998
12 CA10005208 19 BB10001998
13 CA10005208 20 BB10001998
35 CA10005208 21 BB10001998
14 CA10005208 22 BB10001998
15 CA10005208 23 BB10001998
16 CA10005208 24 BB10001998
17 CA10005208 25 BB10001998
30 CA10005208 26 BB10001998
18 CA10005208 27 BB10001998
36 CA10005208 28 BB10001998
19 CA10005208 29 BB10001998
20 CA10005208 30 BB10001998
37 CA10005208 31 BB10001998
25 CA10005208 32 BB10001998
22 CA10005208 33 BB10001998
40 CA10005208 34 BB10001998
24 CA10005208 35 BB10001998
38 CA10005208 36 BB10001998
39 CA10005208 37 BB10001998
23 CA10005208 38 BB10001998
21 CA10005208 39 BB10001998
31 CA10005208 40 BB10001998
问题点数：20、回复次数：5Top
1 楼NinGoo（http://www.NinGoo.net）回复于 2006-08-04 07:59:49 得分 10
SELECT identity(int,1,1) as my_id, *
into #tmp_shch_cg2
from
(SELECT c.id_bom_b, c.gx_int, a.id_pingm
FROM cmt_bom_m1 c
left outer join cmt_bom_b a on c.id_bom_b=a.id_bom_b
WHERE a.id_pingm = 'BB10001998' order by c.id_bom_b, c.gx_int) b
select * from #tmp_shch_cg2 order by gx_int
Top
2 楼zjdyzwx（十一月猪）回复于 2006-08-04 08:16:12 得分 0
难道和索引有关系？
不是很明白
期待高人解答Top
3 楼myperry（綠草原 向★★★★★★奋斗）回复于 2006-08-04 20:56:27 得分 0
以上后两条语句在我的电脑上执行总是不正常，但相同的数据库，在另外一台电脑上执行却正常。
不知是不是SQL那里出了问题？
identity(int,1,1)与那些设置有关呢？
Top
4 楼Carol_zyr（）回复于 2006-08-21 16:07:10 得分 0
等待高人的指点......Top
5 楼lzhs（快乐至上）回复于 2006-08-21 17:17:25 得分 10
你在你本机上先不要加Identity函数，直接执行三条查询语句，看查询出来的结果的排序是不是一样的。然后再分析。
Top
