T-SQL常见基础疑点问答整理(2)
T-SQL常见基础疑点问答整理(2)
(1)请参见:
http://topic.csdn.net/u/20080726/23/45568f53-069f-472e-80b0-1361a72109de.html
贴子只代表个人看法和观点，仅作交流使用，如有错误，敬请指正。
/* T-SQL常见基础疑点整理(2) 由fcuandy整理，而非原创,原创都是MS 2008-7-27*/IF object_id('tb') IS NOT NULL DROP TABLE tbGOIF object_id('ta') IS NOT NULL DROP TABLE taGOCREATE TABLE tb(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))GOINSERT tb SELECT 1,1UNION ALL SELECT 2,'bb'UNION ALL SELECT 3,50UNION ALL SELECT 3,50GOCREATE TABLE ta(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))GOINSERT ta SELECT 1,1UNION ALL SELECT 2,'bb'UNION ALL SELECT 3,50GO--*************************************************--(1)与UNION相关的常见问题SELECT * FROM taUNIONSELECT * FROM tb/*4 rows未指定ALL,ta.row1与tb.row1重复,....而 tb.row4的id=4,无重复记录，所以是4条*/SELECT * FROM taUNION ALLSELECT * FROM tb/*7 rows指定ALL并集得到二表中所有行*/SELECT DISTINCT cid,v FROM taUNIONSELECT DISTINCT cid,v FROM tb/*3 rowsta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复，所以结果为三行*/SELECT DISTINCT cid,v FROM taUNION ALLSELECT DISTINCT cid,v FROM tb/*6 rowsta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复,但指定了ALL,则二结果集全合并，得到6行*/SELECT cid,v FROM taUNIONSELECT cid,v FROM tb/*3 rowsselect cid,v from tb得到4行,其中三四两行重复。 ta得到三行，而前两行与ta的结果集前两行一一对应，第三行与tb的三四行重复，得到3行。*//*----------------------SELECT 1,2,3UNIONSELECT 1,32,34,4----------------------错，因为union进行并集时，列数需要相同，且类型一致或可隐式转换 *//*SELECT 1,2,'a'UNIONSELECT 2,4,1错，'a'无法隐式转换为int, 当多个（不同类型)操作数进行运算时系统总是尝试将低精度转换为高精度，以减少转换的精度损失. 可参考改用----------------------SELECT 1,2,'a'UNIONSELECT 2,4,'1'----------------------或者RTRIM(1)或CAST(1 AS VARCHAR(10))等方式来强制转换。可以参见下例:*/DECLARE @i DECIMAL(10,2),@n INTSELECT @i=1.00,@n=5SELECT @n * @i/*结果为5.00而不是5*/DECLARE @x VARCHAR(10),@y INTSELECT @x='2',@y=3SELECT @x+@y/*为何结果是5而不是23? 上面已说明了，隐式向高精确转换的问题*/SELECT @x + RTRIM(@y)/*这里是23*/--为何我指定了ORDER BY ，但是老报错?/*SELECT * FROM ta ORDER BY cid DESCUNION ALLSELECT * FROM tb ORDER BY cid DESC*//*union操作符中，ORDER BY 只能放在最后一个select statement后，它对合集有效.可改写为*/SELECT * FROM taUNION ALLSELECT * FROM tbORDER BY cid DESC/*问：这种写法是没有语法问题，但与我的本意不一致了，我是希望ta的在前面，然后才是tb的记录以虚列解决*/SELECT *,idx=0 FROM taUNION ALLSELECT *,idx=1 FROM tbORDER BY idx,cid DESC--*************************************************--*************************************************--(2)我经常执行一些语句时，老是报错，怎么也找不到问题在哪?/*我执行:EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout D:\test.txt -c -t, -T' 但是执行下面语句就报错---------------------------DECLARE @filePath VARCHAR(100)SET @filePath = 'd:\test.txt'EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T' ---------------------------因为xp_cmdshell后的命令字串是个常量字串，所以这样写不符合要求.如果@filePath是变量的话，可以用以下方式解决双重exec嵌套*/DECLARE @filePath VARCHAR(100)SET @filePath = 'd:\test.txt'EXEC(' EXEC master..xp_cmdshell ''bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T'' ')/*通常犯这类错误的还有:----------------------DECLARE @k INTSET @k=1EXEC sp_msforeachtable @command1='SELECT * FROM ? WHERE cid=' + RTRIM(@k),@whereand=' AND name IN (''ta'',''tb'')'----------------------@command1被要求为是常量，不能使用变量拼接,同样，采用exec嵌套*/DECLARE @k INTSET @k=1EXEC(' EXEC sp_msforeachtable @command1=''SELECT * FROM ? WHERE cid=' + @k + ''',@whereand='' AND name IN (''''ta'''',''''tb'''')'' ')/*与此同时，还有一些问题，比如在第整理贴1里我提到sp_executeSQL,为什么这么写也报错?----------------------DECLARE @field VARCHAR(10),@m INT,@sql VARCHAR(2000)SET @field='cid'SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUTSELECT @m----------------------仔细看sp_excutesql的联机手册说明,它的参数都被要求为n系参数，ntext,nvarchar,nchar等等可以转换为ntext类型的参数.那么改写很容易:*/DECLARE @field VARCHAR(10),@m INT,@sql NVARCHAR(4000)SET @field='cid'SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUTSELECT @m/*将@sql声明为nvarchar即可. 这里我将2000的长度改为4000是因为:您将@sql定义为varchar(2000),意思是2000的varchar够长度存储要执行的命令串，但改为nvarchar后由双字节存储，存储量小了一半，如果仍是2000,那么可能不能满足要求*//*此类问题总结:这类问题，您需要仔细查看相关的手册细看它的要求。至于xp_cmdshell,sp_msforeachtable为什么参数要求常，sp_executeSQL为什么必须要ntext参数, 我只能说，这是人家规定的，用人家的东西就照人家的要求做, 具体为什么，我也不知道，跟它们的内部实现机制有关吧。当然了，sp_msforeachtable 有这个存储过程的源代码，有兴趣的可以看看找出为什么。就像我很多年前刚学计算机时，总是入不了门，为什么 dir 就可以得到磁盘上的目录文件列表，而不是list? 数学，物理上的结果可以推算出来的，这个为什么是这样？ 想搞懂这个问题，弄得我糊涂了半年^^*/--*************************************************--*************************************************--(3)我在写语句时，遇到一些问题，语法未错，也不报错，但总是结果不是我想要的,排除(2)里描述的问题----------------------------DECLARE @s VARCHARSET @s='bb'SELECT * FROM tb WHERE v=@s/*明明有v='bb'的记录为什么我得不到?在这一部分，我需要提醒的是一些基础细节问题造成的问题@s被定义为varchar,而在delcare时，未指明长度，那么默认长度为1,您相当于定义：DECLARE @s VARCHAR(1)SET @s='bb' --因为长度只有1,则@s='b',后面不用我说了。*/DECLARE @t TABLE(d DATETIME)INSERT @t SELECT GETDATE()UNION ALL SELECT '2008-1-1'DECLARE @d DATETIMESET @d=2008-5-1SELECT * FROM @t WHERE DATEDIFF(yy,d,@d)=0/*明明有200年的记录，为什么我得不到结果?那么在问这个问题前，您先查一下您的@d*/SELECT @d/*1905-06-26 00:00:00.000为何是这样?粗心，失之毫厘，差之千里,给@d赋值时少了定界符SET @d=2008-5-1实际上给了@d一个int值 SET @d=2002以1900-1-1以dd为单位加上2002*/SELECT DATEADD(dd,2002,'1900-1-1')/*1905-06-26 00:00:00.000*//*这样的细节问题还有很多，需要自己写代码时细心加小心*/--*************************************************--(4)我用了isnumeric过滤过了，但为什么还是提示我无法转换?SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30GO/*这里执行正常的*/CREATE UNIQUE CLUSTERED INDEX idx_v ON ta(v)GO/*为什么 下面就出错了?一模一样的语句----------------SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30----------------sqlserver执行基于成本的优化，在v上建立了聚集索引后,引发了查询计划的变更。至于具体它如何操作的，它的执行顺序是怎么样的，您可以查看查询计划另外，这里也不是说只有建了聚集索引才会有这种情况发生，只是想说明,有时候我们想当然的认为它应该怎么样的，实际上并不是所以，在写查询时我们一定要注意语句的适用性*/SELECT * FROM ta WHERE CASE WHEN ISNUMERIC(v)=1 THEN v ELSE 0 END >30/*除此之外，还有很多类似的情况，比如:很多人总认为exists比in快in(单值) 和 = 一样 等等实际上很多情况下只有自己试了才知道,优化器在后面做了很多工作。 由具体的环境，具体的数据分布，具体的设置共同决定。*/--*************************************************--*************************************************--(4)通常一些情况，我老是弄不清。比如表值与标量值DECLARE @classes VARCHAR(100)SET @classes='1,2'SELECT * FROM tb WHERE v IN (@classes)/*@classes是个普通字串, v IN (这里应该是一个集合)当然，一个字串，也即一个元素，也可以是一个集合，只不过集合中只有它一个元素，也就是说 '1,2' 是做为一个整体的不可分割的最小单元(二维关系中我们不考滤集合嵌套的问题,如c#中arraylist a 是个集合, b是a的一个元素，而b本身又是一个arraylist. 或者又如json或xml中的结构){'1,2'} 这是一个集合，它只有 '1,2' 这一个字串元素它与 {1,2} 或 {'1','2'} 这两个集合是有本质区别的那么这时你知道了，你上面的写法相当于是 WHERE v = @classes, 所以取不到记录SELECT * FROM tb WHERE cid=(SELECT cid FROM ta)这句错，因为后者子查询返回一个集合,对于sql查询执行时，左侧外部查询(相对子查询而言)每一行的cid都是个标量或者说集合中的单个元素, 拿元素=集合，从逻辑来说已经错了。而*/SELECT * FROM tb WHERE cid=(SELECT TOP 1 cid FROM ta)/*这句可以执行，因为 单个元素 与 只有单元素的集合 比较，从逻辑计算上来说是错的，但sqlserver在背后为我们做了一些工作。*/