T-SQL常见基础疑点问答总结(1)

 贴子只代表个人看法和观点，仅作交流使用，如有错误，敬请指正。

SQL code

--建立测试环境IF object_id('tb') IS NOT NULL    DROP TABLE tbGOCREATE TABLE tb(id INT IDENTITY(1,1),v VARCHAR(10))GOINSERT tb SELECT 'a'UNION ALL SELECT 'b'INSERT tb SELECT 'x'UNION ALL SELECT 'z'GO

(1)字串变量当数据库对象用

SQL code

--=**********************************************--为什么我执行下面的语句选不到正确数据CREATE PROC p(@field VARCHAR(10),--字段名@value VARCHAR(10) --值)AS    SELECT * FROM tb WHERE @field=@valueGOEXEC p 'v','a'GODROP PROC pGO

--为什么我执行下面语句报错

SQL code

CREATE PROC p(@table VARCHAR(10),--表名@value VARCHAR(10) --值)AS    SELECT * FROM @table WHERE v=@value

说明:
在这二个存储过程中，@table,@field,@value都被定义为varchar
第一个实际上执行的是两个变量的比较，它的作用相当于
IF @field=@value
SELECT * FROM tb
语法未错，意思上却大错特错

第二个实际上执行的是
SELECT * FROM 一个字串 WHERE v=@value
如何能从一个字串中查询结果集呢？错误的把字串当成表对象来理解.
请记住@table是个表名,它是个字段，而非表对象,不是object

可以用EXEC执行动态语句来解决，比如

SQL code

EXEC('SELECT * FROM [' + @table + '] WHERE v=''' + @value + '''')

(2)为什么我在执行一个批语句(可能是存储过程，
      也可能是个FUNCTION,也可能只是几条语句的组合)时，
      提示错误，我照着提示的错误，检查，但是那里没有报错啊
--=**********************************************
比如，上面的第二个存储过程,@table明明是存储过程的输入参数，它为什么提示我@table未定义
在上面，我已经讲了这句为什么出错的原因，
当然@table如果是表变量的话，那么那句select是不会有问题的，
但表变量不能用做输入参数。但它为什么这样提示呢?
这与sql内部机制有关,sql查询语句执行前先由命令解析器进行语法检查，如果语法检查未通过，
会扔出错误信息（通常这里的提示是精确的），
当语法检查通过，则将其编译为可执行的内部格式(查询树),
而非语法错误时，因为是执行时报错,执行期间是内部格式代码，只能扔出个大致错误信息.
了解了这一点，当您的sql语句报错后，先检查是否语法错误，
如果不是，那么需要仔细检查了，因为按着错误提示去找，很有可能兜圈子。
--=**********************************************

(3)为什么我明明定义了@n变量，却提示我变量不存在?

SQL code

--=**********************************************DECLARE @field VARCHAR(10),@n INTSET @field='v'EXEC('SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a''')

上面有三条语句:
rows 语句
1 定义两变量
2 给@field赋值
3 执行动态语句

那么这三条语句，在执行时进行语法检查和编译,大至为:

SQL code

DECLARESETEXEC

这是编译后要执行三步操作，它们在一进程空间中进行编译。
随着语句的运行，exec内部的语句被购造成'select @n=count(*) from tb where v=''a'''
这时，EXEC要执行的这条语句，继续要被命令解析器进行检查和编译,
它的编译内存空间与外部这个批不同，术语我可能表达不太清楚，
只想说明一点，动态语句在执行过程中才被首次编译,所以在这个空间中，提示@n未定义.

解决方式，sqlserver为我们提供了sp_executesql来完成这个操作。
上面要完成的操作，可以改写为:

SQL code

DECLARE @field VARCHAR(10),@n INT,@sql NVARCHAR(4000)SET @field='v'SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a'''EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@n OUTPUTSELECT @n--=**********************************************

(4)以下语句为什么提示我临时表不存在，明明我生成了临时表的
--=**********************************************

SQL code

EXEC('SELECT * INTO # FROM tb')SELECT * FROM #

说明:
在上面解释@n为何未定义时说了关于编译时进程内空间的问题。
我们再联合局部临时表的生名周期，局部临时表在当间会话进程中有效
EXEC内部的语句是新开辟的一个进程空间执行的，所以当它执行完毕后，
生命周期结束，因此外部无法再访问

那为什么下面的又可以呢?

SQL code

SELECT * INTO # FROM tbEXEC('SELECT * FROM #')PRINT 'aa'DROP TABLE #--=********************************************

这个很明显,上面执行了三行，在一个批中,直到PRINT 'aa'结束后，
进程结束，#临时表才会被销毁，因为它的生命周期在整个会话中.
所以当EXEC执行时它仍存在

(5)为什么我给一个变量赋值，得到的不是我想要的,即为什么@v的值不是a,而是z

SQL code

--=**********************************************DECLARE @v VARCHAR(10)SELECT @v=v FROM tbSELECT @v

说明
SELECT 变量=字段 FROM tb
这种赋值与SET赋值的主要区别:
a, SET赋值，一次只能给一个变量赋值，而SELECT 则可多个
b, SET赋值语义更明确，是赋值。而SELECT 可能是赋值也可能是数据查询
c, 最重要的一个,SELECT 可以从表中取值，而SET不能。
说到这有人会说 SET @v=(SELECT TOP 1 v FROM tb) 也可以,
这样确实可以，但实际上它还是调用SELECT来完成.
d, SELECT赋值时是滚动赋值(或许用词不科学），我来说明一下我的'滚动赋值'指的什么
即，当SELECT v FROM tb有多个结果时，
SELECT @v=v FROM tb 在产生结果集的过程中，每得到一条记录，
@v都被赋一次值，也就是说，语句会滚动结果集，每次都对@v赋值。
这样，也就产生了递规查询变量:"

SQL code

SELECT @v=@v+v FROM tb

这样就解释了为什么您的语句得到的结果为z而不是a
try:

SQL code

SELECT TOP 3 @v=v FROM tb --滚动到结果集的第三行,或者说，结果集中只有三行，取最后一行--=**********************************************

(6)我要获得tb上新插入记录的标识值，为什么我的@@identity全局函数取的值不对?

SQL code

--=**********************************************IF object_id('ta') IS NOT NULL    DROP TABLE taCREATE TABLE ta(id INT IDENTITY(1,1),b_id INT,logTime DATETIME NOT NULL DEFAULT GETDATE())GOCREATE TRIGGER t ON tbFOR INSERTAS    INSERT ta (b_id) SELECT id FROM INSERTEDGOINSERT tb SELECT 'dd'SELECT @@identity  --为什么这里得到的不是5,而是1?

说明:
@@identity是个全局函数，返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后)
我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert，所以这里得到的是ta中新增的标识值
建议使用 SCOPE_IDENTITY

SQL code

INSERT tb SELECT 'ww'SELECT SCOPE_IDENTITY() --这里得到6,因为它返回当前会话，当前作用域最后产生的标识值--=**********************************************

(7)为什么我执行distinct去重复后没有效果?如下，我想得到v值不同的记录，也即结果集中每个v值只出现一次

SQL code

--=**********************************************INSERT tb SELECT 'a'UNION ALL SELECT 'b'SELECT * FROM tb  -- 8 rowsSELECT DISTINCT v,id FROM tb --8 rows--=**********************************************

出现这个意外，您需要注意两点:
1,DISTINCT 有效范围是后面的字段列表,而非紧告其的第一个字段
2,为什么DISTINCT 会有这么个奇怪的特点？这与二维关系统有关
就说tb中v=a的记录id有1,7
DISTINCT v,id 如果仅对v有效，那么引id有1,7,在一行一列中如何填充数据？是用1还是用7?
您太难为它了，因为它不知道如何为您将id填充到结果集中.
其实这个问题就是常见的同组取一条，那么你需要指定多一个条件，
每组(v相同的记录为一组)取id最大的或最小的，那么这样的语句，写的很多，论坛上问的也很多了，我就不再累赘了。

先整到这，明天继续

P.S:帮你整理了一下排版,希望没有曲解你的意思哈(hery2002):)