SELECT 赋值与ORDER BY冲突的问题

原创 2006年09月17日 20:19:00

问题描述:

使用 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

 

补充:

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

 

Delphi 6 新特性-VCL单元变化及特性(中文)

   Delphi 6 新特性原著:Borland Corporation翻译:Musicwind®开始日期:2001-07-13结束日期:2001-07-14 (完成VCL单元变化及特性)  声明:...
  • Musicwind
  • Musicwind
  • 2001-08-03 17:13:00
  • 1412

解决Access中Select Top 与Order By 冲突问题

Access中,使用Select Top N 与 Order By 字段 desc时, Top 会不生效,显示了全部记录,解决的办法是将主键字段加下Order By 里面,类似以下: 其中[Ord...
  • alex_xhui
  • alex_xhui
  • 2011-09-28 11:10:14
  • 5556

关于order by后面不能使用参数传递值问题。

今天,在小程序中做到排序功能时,莫名抛出了一个奇怪的错误。'@P3' 附近有语法错误。代码如下: @Override public List getUsers(int index,StringB...
  • sb1ue
  • sb1ue
  • 2012-10-30 21:18:49
  • 3143

解决 ORDER BY expressions must appear in select list

当一条sql语句中同时使用DISTINCT和created_at时,可能会出现 ERROR: for SELECT DISTINCT, ORDER BY expressions must app...
  • qq_32371887
  • qq_32371887
  • 2017-06-15 16:49:43
  • 608

distinct、top和order by

-- ============================================= -- Author: 余波(杭州) -- Creat...
  • yubofighting
  • yubofighting
  • 2011-09-20 16:21:38
  • 5056

SELECT 赋值与ORDER BY冲突的问题

问题描述:使用 SELECT 语句,轮询表中的数据,并且处理变量数据时,如果有ORDER BY语句,则得不到想要的结果,但去掉ORDER BY,结果正常。具体的问题表现参考下面的问题重现代码 问题重现...
  • zjcxc
  • zjcxc
  • 2006-09-17 20:19:00
  • 10216

order by 1,2

select  aa,bb,cc,dd,ff from tables order by 1,2; 即: select  aa,bb,cc,dd,ff from tables order...
  • qingfeng45697
  • qingfeng45697
  • 2015-10-30 10:00:36
  • 163

1为数据库查询的数据中加一排序号,2判断为查询出来的字段赋值

@Override public String getSqlByFileCodeForExport(String strTime, String strCompany) { S...
  • qq_37910658
  • qq_37910658
  • 2017-11-07 10:08:23
  • 203

selectKey

检索数据库中最后一个id赋值给pojo只取当前事务生成的id,防止高并发的冲突 select last_insert_id() ...
  • Lovincc
  • Lovincc
  • 2017-06-09 08:31:38
  • 303

Jquery和JS获取ul中li标签的值以及赋值问题

通过点击li标签,拿到li值,在把值赋给一个文本框 以下是HTML,以及input标签 ...
  • liulihui1988
  • liulihui1988
  • 2017-09-15 14:18:53
  • 2401
收藏助手
不良信息举报
您举报文章:SELECT 赋值与ORDER BY冲突的问题
举报原因:
原因补充:

(最多只允许输入30个字)