奇怪的排序问题

[zheninchangjiang]

select * from (select a.itemid,b.itemname,a.result,c.itemvaluemin,c.itemvaluemax,h.showorder aaaa
from ( select C as result,'112' as itemid from exp_taelement where id=40
union all select Si as result,'113' as itemid from exp_taelement where id=40
union all select Mn as result,'100' as itemid from exp_taelement where id=40
union all select P as result,'108' as itemid from exp_taelement where id=40
union all select S as result,'105' as itemid from exp_taelement where id=40
union all select Cr as result,'114' as itemid from exp_taelement where id=40
union all select Ni as result,'115' as itemid from exp_taelement where id=40
union all select Ca as result,'125' as itemid from exp_taelement where id=40
union all select Al as result,'117' as itemid from exp_taelement where id=40
union all select Cu as result,'118' as itemid from exp_taelement where id=40
union all select Alsol as result,'126' as itemid from exp_taelement where id=40
union all select V as result,'120' as itemid from exp_taelement where id=40
union all select W as result,'121' as itemid from exp_taelement where id=40
union all select B as result,'122' as itemid from exp_taelement where id=40
union all select Sn as result,'123' as itemid from exp_taelement where id=40
union all select as1 as result,'124' as itemid from exp_taelement where id=40) a
join (select * from openquery(ora9qc,'select * from qc.bd_citem')) b
on a.itemid=b.itemid
left join (select * from openquery(ora9qc,
'select itemid,itemvaluemin,itemvaluemax from bd_cstdetail a join bd_cstandard b on a.cstid=b.cstid where a.cstid=''00000007''')) c
on a.itemid=c.itemid
left join (select * from data_tranmodule where stkid is null) h
on a.itemid=h.itemid) aaa order by aaaa

---
100 Mn 1.3700000000000001 1.2 1.6 2.0
105 S 2.9000000000000001E-2 0 .045 5.0
108 P 4.3999999999999997E-2 0 .045 4.0
112 C 0.20200000000000001 .17 .25 1.0
113 Si 0.63 .4 .8 3.0
114 Cr 5.9999999999999998E-2 0 .3 6.0
115 Ni 4.0000000000000001E-2 0 .3 7.0
117 Al 3.0000000000000001E-3 NULL NULL 9.0
118 Cu 0.11 0 .3 8.0
120 V 0.0 NULL NULL 12.0
121 W 0.0 NULL NULL 13.0
122 B 0.0 NULL NULL 14.0
123 Sn 0.0 NULL NULL 15.0
124 As1 0.0 NULL NULL 16.0
125 Ca 0.0 NULL NULL 11.0
126 Alsol 0.0 NULL NULL 10.0

语句后加上 order by aaa,结果就只有一条记录了,排序列值就是最后一列--
112 C 0.20200000000000001 .17 .25 1.0
不知道为什么,已经安装了sp4了,虽说select @@version还是sp2
怎么办怎么办???

[中国风]

select @@version还是sp2 ?
楼主是不是装的XP系统sp2
---8.00.2039 (sp4)
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

[zheninchangjiang]

居然不能改,那就补充一下了order by aaaa 一开始是没有加上去的,我复制的时候粗糙了一点,也给复制上了

[dawugui]

我一运行,' '00000007 ' ' '这个地方有错.
要不你把结构,数据和结果拿出来看看.

[samfeng_2003]

呵呵， 引用邹建老大的东西给ＬＺ 做做参考！

SELECT 赋值与ORDER BY冲突的问题
问题描述:
使用 SELECT 语句，轮询表中的数据，并且处理变量数据时，如果有ORDER BY语句，则得不到想要的结果，但去掉ORDER BY，结果正常。
具体的问题表现参考下面的问题重现代码

问题重现代码
-- 测试数据
DECLARE @T TABLE(id int,value nvarchar(16))
INSERT INTO @T SELECT
1, N'好人' UNION ALL SELECT
2, N'坏人' UNION ALL SELECT
3, N'吃饭' UNION ALL SELECT
4, N'垃圾'

-- 赋值处理
DECLARE @str nvarchar(4000)
SET @str = N'我不是一个好人，也不是垃圾'
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>')
FROM @T
WHERE CHARINDEX(value, @str) > 0
--ORDER BY CHARINDEX(value, @str) DESC
SELECT @str

/* -- 结果(当赋值处理语句注释掉ORDER BY 时)
我不是一个<u>好人</u>，也不是<u>垃圾</u>
-- */

/* -- 结果(当赋值处理语句加上ORDER BY 时)
我不是一个<u>好人</u>，也不是垃圾
-- */

问题分析：
两个处理语句的结果不同，通过查看它们的执行计划应该可以看出原因所在，为此，通过
SET SHOWPLAN_ALL ON
输出了两种执行语句的执行计划（仅StmtText部分，有兴趣的读者在自己的电脑上测试的时候，可以去了解其他部分的信息）
StmtText Step
DECLARE @str nvarchar(4000) SET @str = N'我不是一个好人，也不是垃圾' 　
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>') FROM @T WHERE CHARINDEX(value, @str) > 0 4
|-- Compute Scalar(DEFINE:([Expr1002]=replace([@str], @T.[value], '<u>'+@T.[value]+'</u>'))) 3
|-- Filter(WHERE:(charindex(@T.[value], [@str], NULL)>0)) 2
|-- Table Scan(OBJECT:(@T)) 1

DECLARE @str nvarchar(4000) SET @str = N'我不是一个好人，也不是垃圾' 　
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>') FROM @T WHERE CHARINDEX(value, @str) > 0 ORDER BY CHARINDEX(value, @str) DESC 5
|-- Sort(ORDER BY:([Expr1003] DESC)) 4
|-- Compute Scalar(DEFINE:([Expr1002]=replace([@str], @T.[value], '<u>'+@T.[value]+'</u>'), [Expr1003]=charindex(@T.[value], [@str], NULL))) 3
|-- Filter(WHERE:(charindex(@T.[value], [@str], NULL)>0)) 2
|-- Table Scan(OBJECT:(@T)) 1
从上面的列表可以看出，两种处理的最大差异，在于赋值前，是否有ORDER BY 子句，从一般的理解上，可能会认为是否排序并不重要，但换个角度来看问题，就比较容易理解为什么有ORDER BY子句后得不到我们想要的结果了：
当有ORDER BY子句时，对于SELECT @str = 这种赋值处理，SQL Server认为赋值处理肯定只会保留最后一条记录的处理结果，而ORDER BY子句确定了数据顺序，也就知道最后一条记录是那个，因此只会处理ORDER BY的最后一条记录。（读者可以自行去测试一下，调整ORDER BY顺序，看看结果是否与我的推论相符）
当没有ORDER BY子句时，因为无法确定数据顺序，所以SQL Server必须扫描满足条件的每条数据来得到结果，这样每扫描一条记录都会处理一次，所以结果是我们所预知的

问题解决方法：
修改处理语句，使查询优化器使用与我们需要结果一致的执行方法，可以解决这个问题。
对于示例中的处理语句，可以调整如下:
DECLARE @str nvarchar(4000)
SET @str = N'我不是一个好人，也不是垃圾'
SELECT @str = REPLACE(@str, value, N'<u>' + value + N'</u>')
FROM(
SELECT TOP 100 PERCENT
value
FROM @T
WHERE CHARINDEX(value, @str) > 0
ORDER BY CHARINDEX(value, @str) DESC
)A
SELECT @str

补充：
此问题的结论只是笔者对于查询分析的一个推论，并无相应的官方文档可以证明，所以欢迎大家发表自己的看法

[samfeng_2003]

我也发现，常常在ＳＱＬ统计以后，比如类似使用动态交叉表语句，如果在生成循环的那步把语句加个排序，很可能就只会得到一条！很郁闷

[zheninchangjiang]

呵呵,我感觉不太正确,因为我有几个条件设置,其他条件设置
如:
select * from data_tranmodule where stkid ='1301'
那得到的结果是正确的,同样的语句,有点郁闷了
我还特别的select * from (.....) 这样来做了,

[zheninchangjiang]

我的SQL比较麻烦,还要链接oracle数据库,所以数据什么的不方便,我换环境也不方便

[zheninchangjiang]

正如楼上所说的啊,我晕

[zsforever]

邹建 对此早就有过普遍认同的结论 ，这是一个 bug ,2005也没改过来，呵呵
替代方案用 top 100 percent