/*
    T-SQL常见基础疑点整理(2)
    由fcuandy整理，而非原创,原创都是MS
    2008-7-27
*/
IF object_id('tb') IS NOT NULL
    DROP TABLE tb
GO
IF object_id('ta') IS NOT NULL
    DROP TABLE ta
GO

CREATE TABLE tb(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GO

INSERT tb SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
UNION ALL SELECT 3,50
GO

CREATE TABLE ta(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GO

INSERT ta SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
GO

--*************************************************
--(1)与UNION相关的常见问题


SELECT * FROM ta
UNION
SELECT * FROM tb
/*4 rows
未指定ALL,ta.row1与tb.row1重复,....而 tb.row4的id=4,无重复记录，所以是4条
*/

SELECT * FROM ta
UNION ALL
SELECT * FROM tb
/*7 rows
指定ALL并集得到二表中所有行
*/


SELECT DISTINCT cid,v FROM ta
UNION
SELECT DISTINCT cid,v FROM tb
/*3 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复，所以结果为三行
*/
SELECT DISTINCT cid,v FROM ta
UNION ALL
SELECT DISTINCT cid,v FROM tb
/*6 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复,但指定了ALL,则二结果集全合并，得到6行
*/
SELECT cid,v FROM ta
UNION
SELECT cid,v FROM tb
/*3 rows
select cid,v from tb得到4行,其中三四两行重复。 ta得到三行，而前两行与ta的结果集前两行一一对应，第三行与tb的三四行重复，得到3行。
*/


/*
----------------------
SELECT 1,2,3
UNION
SELECT 1,32,34,4
----------------------
错，因为union进行并集时，列数需要相同，且类型一致或可隐式转换 
*/

/*
SELECT 1,2,'a'
UNION
SELECT 2,4,1
错，'a'无法隐式转换为int, 当多个（不同类型)操作数进行运算时系统总是尝试将低精度转换为高精度，以减少转换的精度损失. 可参考

改用
----------------------
SELECT 1,2,'a'
UNION
SELECT 2,4,'1'
----------------------
或者
RTRIM(1)或CAST(1 AS VARCHAR(10))等方式来强制转换。可以参见下例:
*/
DECLARE @i DECIMAL(10,2),@n INT
SELECT @i=1.00,@n=5
SELECT @n * @i
/*结果为5.00而不是5*/
DECLARE @x VARCHAR(10),@y INT
SELECT @x='2',@y=3
SELECT @x+@y
/*为何结果是5而不是23? 上面已说明了，隐式向高精确转换的问题*/
SELECT @x + RTRIM(@y)
/*这里是23*/

--为何我指定了ORDER BY ，但是老报错?
/*
SELECT * FROM ta ORDER BY cid DESC
UNION ALL
SELECT * FROM tb ORDER BY cid DESC
*/
/*
union操作符中，ORDER BY 只能放在最后一个select statement后，它对合集有效.
可改写为
*/
SELECT * FROM ta
UNION ALL
SELECT * FROM tb
ORDER BY cid DESC
/*
问：这种写法是没有语法问题，但与我的本意不一致了，我是希望ta的在前面，然后才是tb的记录
以虚列解决
*/
SELECT *,idx=0 FROM ta
UNION ALL
SELECT *,idx=1 FROM tb
ORDER 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 INT
SET @k=1
EXEC sp_msforeachtable @command1='SELECT * FROM ? WHERE cid=' + RTRIM(@k),@whereand=' AND name IN (''ta'',''tb'')'
----------------------
@command1被要求为是常量，不能使用变量拼接,同样，采用exec嵌套
*/
DECLARE @k INT
SET @k=1
EXEC('
    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 OUTPUT
SELECT @m
----------------------
仔细看sp_excutesql的联机手册说明,它的参数都被要求为n系参数，ntext,nvarchar,nchar等等可以转换为ntext类型的参数.
那么改写很容易:
*/
DECLA