【请教】一条sql语句引起的疑惑

Question

if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc varchar(50))insert #temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'select isnull(left(numdesc, 1), 'IsUnknown') ,
isnull(left(numdesc, 2), 'IsUnknown') ,
isnull(left(numdesc, 3), 'IsUnknown') ,
isnull(left(numdesc, 4), 'IsUnknown') ,
isnull(left(numdesc, 5), 'IsUnknown') from #temp where num = 3
----------------------------------结果---------------------------
/*
---- ---- ------ -------- ----------
Is   IsUn IsUnkn IsUnknow IsUnknown(1 行受影响)*/
难道将left(numdesc, 1)的数据类型转换为了varchar(2)
但是
select isnull(left(null,1),'IsUnknown'),
       isnull(left(null,2),'IsUnknown'),
       isnull(left(null,3),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),1),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),2),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),3),'IsUnknown')
/*
                         
---- ---- ---- ---- ---- ------
I    I    I    Is   IsUn IsUnkn(1 行受影响)*/
请问这是为什么呢？

Answer 1

sql版本：
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
Nov 24 2008 13:01:59 
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Answer 2

select isnull(cast(left(numdesc, 3) as varchar(10)), 'IsUnknown') from #temp where num = 3
/**----------
IsUnknown(1 行受影响)
**/if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc nvarchar(50))
insert #temp 
select 1, 'one' 
union select 2, 'two' 
union select 3, null 
union select 4, 'four'select isnull(left(numdesc,3), 'IsUnknown') from #temp where num = 3
/**----
IsU(1 行受影响)
**/

Answer 3

我猜测是隐式转换
根据numdesc的类型，长度就是你输入的数字

Answer 4

如果 check_expression 不为 NULL，则返回它的值；否则，在将 replacement_value 隐式转换为 check_expression 的类型（如果这两个类型不同）后，则返回前者。

Answer 5

left(numdesc, 1)  它的类型应该是 varchar(1)  

Answer 6

联机看了好多遍了，关键是我的结果和这个行不通呀，你在你电脑上试试，你啥版本的sql？

Answer 7

但是我执行
select isnull(left(numdesc, 1), 'IsUnknown')  from #temp where num = 3得到的是 Is  而不是 I
这点是我最大的疑惑

Answer 8

08 SP1
------------------------------
select isnull(substring(numdesc, 1,1), 'IsUnknown') ,  isnull(substring(numdesc,1, 2), 'IsUnknown') ,  isnull(substring(numdesc,1, 3), 'IsUnknown') ,  isnull(substring(numdesc,1, 4), 'IsUnknown') ,  isnull(substring(numdesc,1, 5), 'IsUnknown') from #temp where num = 3
  |--Compute Scalar(DEFINE:([Expr1004]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(1)),'I'), [Expr1005]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(2)),'Is'), [Expr1006]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(3)),'IsU'), [Expr1007]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(4)),'IsUn'), [Expr1008]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(5)),'IsUnk')))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#temp]), WHERE:([tempdb].[dbo].[#temp].[num]=(3)))
 
05 SP3 CU5
--------------------
select isnull(substring(numdesc, 1,1), 'IsUnknown') ,  isnull(substring(numdesc,1, 2), 'IsUnknown') ,  isnull(substring(numdesc,1, 3), 'IsUnknown') ,  isnull(substring(numdesc,1, 4), 'IsUnknown') ,  isnull(substring(numdesc,1, 5), 'IsUnknown') from #temp where num = 3
  |--Compute Scalar(DEFINE:([Expr1004]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(1)),'Is'), [Expr1005]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(2)),'IsUn'), [Expr1006]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(3)),'IsUnkn'), [Expr1007]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(4)),'IsUnknow'), [Expr1008]=isnull(substring([tempdb].[dbo].[#temp].[numdesc],(1),(5)),'IsUnknown')))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#temp]), WHERE:([tempdb].[dbo].[#temp].[num]=(3)))
这明显是个黑匣子，你想知道？问微软去！

Answer 9

select left(numdesc, 1) as a ,left(numdesc, 2) as b into testtest  
from #temp where num = 3看看表

Answer 10

select left(null, 1) as a ,left(null, 2) as b into testtest1 

Answer 11

我记得好像是《数据库系统概论》里提到过，如果某处是null值，实际上它是有某个标志位来标识此处是null，判断数据是否为空实际上就是判断标志位的，以下都是我的猜想，没有证实过，欢迎批评指正，大家共同探讨，禁止人身攻击假设sqlserver中存在如下一个基类,所有类型通过它派生class Object
{
private: bool IsNull;(void*) p_memory;//这地方我不知道怎么描述它，其实用可变类型更恰当，总之指向一块内存
Object(){bool=true;p_memory=new /*此处分配一字节的空间*/}
~Objcect(){delete p_memory;}
}当我们直接isnull(某字段,'defaultvalue')时，SqlServer实际上把最小的1字节转换成char(1),
但当我们cast(某字段 as varchar(10)),sqlserver 加p_memory的空间改为20字节,varchar(1)占两字节
然后再isnull时，实际上此处的空间是20字节了，所以可以容纳后面的defaultvalue

Answer 12

--sql2008if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc varchar(50))insert #temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'select isnull(left(numdesc, 1), 'IsUnknown') ,
isnull(left(numdesc, 2), 'IsUnknown') ,
isnull(left(numdesc, 3), 'IsUnknown') ,
isnull(left(numdesc, 4), 'IsUnknown') ,
isnull(left(numdesc, 5), 'IsUnknown') from #temp where num = 3------------------------
I Is IsU IsUn IsUnkf1： Returns varchar when character_expression is a non-Unicode character data type.Returns nvarchar when character_expression is a Unicode character data type.
你的返回nvarchar类型， 偶也很奇怪， 不明真相啊

Answer 13

小爱，再看了一下，似乎应该这样解释。跟字序集是相关的。COLLATION控制着字符的排序，同时也控制这字符的储存方式，是1个字节代表一个字符，还是2个字节代表一个字符。比如：if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS)insert #temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'select isnull(left(numdesc, 1), 'IsUnknown') ,
isnull(left(numdesc, 2), 'IsUnknown') ,
isnull(left(numdesc, 3), 'IsUnknown') ,
isnull(left(numdesc, 4), 'IsUnknown') ,
isnull(left(numdesc, 5), N'IsUnknown') 
from #temp where num = 3
/*
---- ---- ---- ---- -----
I    Is   IsU  IsUn IsUnk(1 row(s) affected)
*/SQL_Latin1_General_CP1_CI_AS规则下，1个字节表示一个字符。if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc varchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS)insert #temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'select isnull(left(numdesc, 1), 'IsUnknown') ,
isnull(left(numdesc, 2), 'IsUnknown') ,
isnull(left(numdesc, 3), 'IsUnknown') ,
isnull(left(numdesc, 4), 'IsUnknown') ,
isnull(left(numdesc, 5), N'IsUnknown') 
from #temp where num = 3 Chinese_Taiwan_Stroke_CI_AS规则下，2个字节表示一个字符。LEFT(NULL,x) 返回了x个字节，然后再按字序规则存储字符。----------分割线----------------------------------------------
 

Answer 14

select isnull(left(null,1),'IsUnknown'),
       isnull(left(null,2),'IsUnknown'),
       isnull(left(null,3),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),1),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),2),'IsUnknown'),
       isnull(left(cast(null as varchar(10)),3),'IsUnknown')这种方式的
left(null,1)
left(null,2)
left(null,3)
似乎都只返回了1个byte.

Answer 15

应该是和默认的排序规则有关的
if object_id('tempdb..#temp') is not null drop table #temp
go
create table #temp (num int, numdesc varchar(50) collate  Latin1_General_CI_AS )insert #temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'select isnull(left(numdesc,1), 'IsUnknown') ,
isnull(left(numdesc,2), 'IsUnknown') ,
isnull(left(numdesc,3), 'IsUnknown') ,
isnull(left(numdesc,4), 'IsUnknown') ,
isnull(left(numdesc,5), 'IsUnknown') from #temp where num = 3---- ---- ---- ---- -----
I    Is   IsU  IsUn IsUnk

Answer 16

create table #temp (num int, numdesc varchar(50) collate  Latin1_General_CI_AS )