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

 

补充:

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

 

优化MYSQL数据库的方法

1、建立索引:   a、(普通索引)->   创建:CREATE INDEX ON tablename (索引字段)   修改:ALTER TABLE tablename ADD ...
  • waiwai021
  • waiwai021
  • 2016年03月02日 09:35
  • 383

MySQL Order By实现原理分析和Filesort优化

目录(?)[-] 利用有序索引获取有序数据 1 按照索引对结果进行排序order by 使用索引是有条件 文件排序 1 MySQL 需要使用filesort 实现排序的实例2 My...
  • a925907195
  • a925907195
  • 2017年05月05日 21:50
  • 346

select的选择和赋值

举实例说明: Volvo Saab Opel Audi BMW 1、获取选中的select的option的值value和文本text     var checkVal...
  • zbyluxzing
  • zbyluxzing
  • 2015年08月01日 12:27
  • 3299

浅谈jquery关于select框的取值和赋值

jQuery("#select_id").change(function(){}); // 1.为Select添加事件,当选择其中一项时触发      var checkValue = jQuery...
  • huanbinzhou333
  • huanbinzhou333
  • 2013年03月13日 10:45
  • 19405

js 获取select的值 / js动态给select赋值

jQuery获取Select选择的Text和Value: 语法解释: 1. $("#select_id").change(function(){//code...});   //为Select添加事件...
  • ch5256865
  • ch5256865
  • 2015年09月07日 14:37
  • 24522

jquery select取值,赋值操作

select">jquery select取值,赋值操作 一、获取Select  获取select 选中的 text :  $("#ddlRegType").find("option...
  • Java_WuYY
  • Java_WuYY
  • 2016年06月29日 11:59
  • 782

何时使用SET和SELECT为变量赋值

我们经常使用SET和SELECT来为变量复制,但是有时候,只能选其一来使用,下面来看看这些例子,本例中使用AdventureWorks数据库来做演示。 通过查询返回值:        当你把查询返...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2013年01月15日 00:16
  • 20445

JS给<select>的<option>赋值&JS发异步请求获取后台列表

HTML的下拉框标签里面的各个选项option,有时候不能写死,尤其在实现多个下拉框多级联动的时候,后继的option往往需要动态添加。 动态添加option主要有2种:一是用JS添加静态数据,第二种...
  • ljheee
  • ljheee
  • 2017年03月11日 16:03
  • 5964

【bootstrap】--- 动态给select赋值(未完)

我做的这个页面现在需要回显数据,也就是如果这个学生已经填写了个人信息,那么当它再次来到这个页面的时候,他填写的信息还是需要存在的。     我做的页面分为三部分,第一部分是个人信息的填写,大部分...
  • u013045959
  • u013045959
  • 2016年07月01日 08:06
  • 6597

jquery操作select下拉框:取值,赋值,删除

1.jquery对select的取值 测试1 测试2 测试3 测试4 用上面的select举例说明: 取得value: var value=$("#test")...
  • gaoweizang
  • gaoweizang
  • 2016年09月23日 10:30
  • 5684
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SELECT 赋值与ORDER BY冲突的问题
举报原因:
原因补充:

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