Sql Server 存储过程中查询数据无法使用 Union(All)
- 2019 年 10 月 4 日
- 筆記
微软Sql Server数据库中，书写存储过程时，关于查询数据，无法使用Union(All)关联多个查询。
1、先看一段正常的SQL语句，使用了Union(All)查询：
SELECT ci.CustId --客户编号 , ci.CustNam --客户名称 , ci.ContactBy --联系人 , ci.Conacts --联系电话 , ci.Addr -- 联系地址 , ci.Notes --备注信息 , ai2.AreaNam --区域名称,省份名称 , ISNULL(cc.CType, '') AS CType--合同类型 , ISNULL(caat.ArTotal, 0.0) AS ArTotal --截止到当月底，云想系统账欠款余额 FROM CustInfo AS ci INNER JOIN AreaInfo AS ai ON ci.AreaCode = ai.AreaCode INNER JOIN AreaInfo AS ai2 ON ai.PareaCode = ai2.AreaCode LEFT JOIN CustContract AS cc ON cc.CustId = ci.CustId LEFT JOIN CustArApTotal AS caat ON ci.CustId = caat.CustId WHERE ci.CustCatagory = 1 UNION ALL SELECT ci.CustId --客户编号 , ci.CustNam --客户名称 , ci.ContactBy --联系人 , ci.Conacts --联系电话 , ci.Addr -- 联系地址 , ci.Notes --备注信息 , ai2.AreaNam --区域名称,省份名称 , ISNULL(cc.CType, '') AS CType--合同类型 , ISNULL(caat.ArTotal, 0) AS ArTotal --截止到当月底，云想系统账欠款余额 FROM CustInfo AS ci INNER JOIN AreaInfo AS ai ON ci.AreaCode = ai.AreaCode INNER JOIN AreaInfo AS ai2 ON ai.PareaCode = ai2.AreaCode INNER JOIN CustContract AS cc ON cc.CustId = ci.CustId LEFT JOIN CustArApTotal AS caat ON ci.CustId = caat.CustId WHERE ci.CustCatagory = 2
运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；
Union(all) 之后的sql语句查询结果为330条记录。
2、创建视图，将以上SQL查询语句放在视图中：
1 ALTER VIEW [dbo].[VGetCustRelatedInfo2] 2 AS 3 4 SELECT ci.CustId --客户编号 5 , 6 ci.CustNam --客户名称 7 , 8 ci.ContactBy --联系人 9 , 10 ci.Conacts --联系电话 11 , 12 ci.Addr -- 联系地址 13 , 14 ci.Notes --备注信息 15 , 16 ai2.AreaNam --区域名称,省份名称 17 , 18 ISNULL(cc.CType, '') AS CType--合同类型 19 , 20 ISNULL(caat.ArTotal, 0.0) AS ArTotal --截止到当月底，云想系统账欠款余额 21 FROM CustInfo AS ci 22 INNER JOIN AreaInfo AS ai 23 ON ci.AreaCode = ai.AreaCode 24 INNER JOIN AreaInfo AS ai2 25 ON ai.PareaCode = ai2.AreaCode 26 LEFT JOIN CustContract AS cc 27 ON cc.CustId = ci.CustId 28 LEFT JOIN CustArApTotal AS caat 29 ON ci.CustId = caat.CustId 30 WHERE ci.CustCatagory = 1 31 32 UNION ALL 33 34 SELECT ci.CustId --客户编号 35 , 36 ci.CustNam --客户名称 37 , 38 ci.ContactBy --联系人 39 , 40 ci.Conacts --联系电话 41 , 42 ci.Addr -- 联系地址 43 , 44 ci.Notes --备注信息 45 , 46 ai2.AreaNam --区域名称,省份名称 47 , 48 ISNULL(cc.CType, '') AS CType--合同类型 49 , 50 ISNULL(caat.ArTotal, 0) AS ArTotal --截止到当月底，云想系统账欠款余额 51 FROM CustInfo AS ci 52 INNER JOIN AreaInfo AS ai 53 ON ci.AreaCode = ai.AreaCode 54 INNER JOIN AreaInfo AS ai2 55 ON ai.PareaCode = ai2.AreaCode 56 INNER JOIN CustContract AS cc 57 ON cc.CustId = ci.CustId 58 LEFT JOIN CustArApTotal AS caat 59 ON ci.CustId = caat.CustId 60 WHERE ci.CustCatagory = 2 61 62 63 64 65 66 GO
调用视图，运行结果：查询出441条数据，其中Union(all) 之前的sql语句查询结果为101条记录；
Union(all) 之后的sql语句查询结果为330条记录。
3、创建存储过程，代码如下：
1 /************************************************************ 2 * Code formatted by SoftTree SQL Assistant ?v6.5.258 3 * Time: 2014/9/12 16:41:46 4 ************************************************************/ 5 6 GO 7 8 /****** Object: StoredProcedure [dbo].[SP_GetCustRelatedInfo2] Script Date: 09/12/2014 9 10 15:48:17 ******/ 11 SET ANSI_NULLS ON 12 GO 13 14 SET QUOTED_IDENTIFIER ON 15 GO 16 17 18 19 -- ============================================= 20 -- Author: XXX 21 -- Create date: XXX 22 -- Description: XXX 23 -- ============================================= 24 ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2] 25 @custId NVARCHAR(30) --客户编号 26 , 27 @custNam NVARCHAR(1000) --客户名称 28 , 29 @areaNam NVARCHAR(30)--区域、省份名称 30 , 31 @pageSize INT --单页记录条数 32 , 33 @pageIndex INT --当前页左索引 34 , 35 @totalRowCount INT OUTPUT --输出总记录条数 36 AS 37 BEGIN 38 SET NOCOUNT ON; 39 40 DECLARE @RowStart INT; --定义分页起始位置 41 DECLARE @RowEnd INT; --定义分页结束位置 42 43 DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句 44 DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句 45 DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句 46 47 IF @pageIndex > 0 48 BEGIN 49 SET @pageIndex = @pageIndex -1; 50 SET @RowStart = @pageSize * @pageIndex + 1; 51 SET @RowEnd = @RowStart + @pageSize - 1; 52 END 53 ELSE 54 BEGIN 55 SET @RowStart = 1; 56 SET @RowEnd = 999999; 57 END 58 59 IF ISNULL(@pageSize, 0) <> 0 60 BEGIN 61 SET @sql = 62 'With CTE_CustRelatedInfo as ( 63 SELECT ROW_NUMBER () OVER (ORDER BY t.CustId ASC) AS RowNumber, t.* 64 FROM ( 65 SELECT ci.CustId --客户编号 66 , 67 ci.CustNam --客户名称 68 , 69 ci.ContactBy --联系人 70 , 71 ci.Conacts --联系电话 72 , 73 ci.Addr -- 联系地址 74 , 75 ci.Notes --备注信息 76 , 77 ai2.AreaNam --区域名称,省份名称 78 , 79 ISNULL(cc.CType, '') AS CType--合同类型 80 , 81 ISNULL(caat.ArTotal, 0.0) AS ArTotal --截止到当月底，云想系统账欠款余额 82 FROM CustInfo AS ci 83 INNER JOIN AreaInfo AS ai 84 ON ci.AreaCode = ai.AreaCode 85 INNER JOIN AreaInfo AS ai2 86 ON ai.PareaCode = ai2.AreaCode 87 LEFT JOIN CustContract AS cc 88 ON cc.CustId = ci.CustId 89 LEFT JOIN CustArApTotal AS caat 90 ON ci.CustId = caat.CustId 91 WHERE ci.CustCatagory = 1 92 93 UNION ALL 94 95 SELECT ci.CustId --客户编号 96 , 97 ci.CustNam --客户名称 98 , 99 ci.ContactBy --联系人 100 , 101 ci.Conacts --联系电话 102 , 103 ci.Addr -- 联系地址 104 , 105 ci.Notes --备注信息 106 , 107 ai2.AreaNam --区域名称,省份名称 108 , 109 ISNULL(cc.CType, '') AS CType--合同类型 110 , 111 ISNULL(caat.ArTotal, 0) AS ArTotal --截止到当月底，云想系统账欠款余额 112 FROM CustInfo AS ci 113 INNER JOIN AreaInfo AS ai 114 ON ci.AreaCode = ai.AreaCode 115 INNER JOIN AreaInfo AS ai2 116 ON ai.PareaCode = ai2.AreaCode 117 INNER JOIN CustContract AS cc 118 ON cc.CustId = ci.CustId 119 LEFT JOIN CustArApTotal AS caat 120 ON ci.CustId = caat.CustId 121 WHERE ci.CustCatagory = 2 122 ) 123 AS t 124 WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加 125 END 126 ELSE 127 BEGIN 128 SET @sql = 129 'SELECT t.* 130 FROM ( 131 SELECT ci.CustId --客户编号 132 ,ci.CustNam --客户名称 133 , 134 ci.ContactBy --联系人 135 , 136 ci.Conacts --联系电话 137 , 138 ci.Addr -- 联系地址 139 , 140 ci.Notes --备注信息 141 , 142 ai2.AreaNam --区域名称,省份名称 143 , 144 ISNULL(cc.CType, '') AS CType--合同类型 145 , 146 ISNULL(caat.ArTotal, 0.0) AS ArTotal --截止到当月底，云想系统账欠款余额 147 FROM CustInfo AS ci 148 INNER JOIN AreaInfo AS ai 149 ON ci.AreaCode = ai.AreaCode 150 INNER JOIN AreaInfo AS ai2 151 ON ai.PareaCode = ai2.AreaCode 152 LEFT JOIN CustContract AS cc 153 ON cc.CustId = ci.CustId 154 LEFT JOIN CustArApTotal AS caat 155 ON ci.CustId = caat.CustId 156 WHERE ci.CustCatagory = 1 157 158 UNION ALL 159 160 SELECT ci.CustId --客户编号 161 , 162 ci.CustNam --客户名称 163 , 164 ci.ContactBy --联系人 165 , 166 ci.Conacts --联系电话 167 , 168 ci.Addr -- 联系地址 169 , 170 ci.Notes --备注信息 171 , 172 ai2.AreaNam --区域名称,省份名称 173 , 174 ISNULL(cc.CType, '') AS CType--合同类型 175 , 176 ISNULL(caat.ArTotal, 0) AS ArTotal --截止到当月底，云想系统账欠款余额 177 FROM CustInfo AS ci 178 INNER JOIN AreaInfo AS ai 179 ON ci.AreaCode = ai.AreaCode 180 INNER JOIN AreaInfo AS ai2 181 ON ai.PareaCode = ai2.AreaCode 182 INNER JOIN CustContract AS cc 183 ON cc.CustId = ci.CustId 184 LEFT JOIN CustArApTotal AS caat 185 ON ci.CustId = caat.CustId 186 WHERE ci.CustCatagory = 2 187 ) 188 AS t 189 WHERE 1=1 '; 190 END 191 192 IF ISNULL(@custId, '') <> '' 193 BEGIN 194 --根据客户id查询 195 SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%'''; 196 END 197 198 IF ISNULL(@custNam, '') <> '' 199 BEGIN 200 --根据客户名称 模糊查询 201 SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%'''; 202 END 203 204 IF ISNULL(@areaNam, '') <> '' 205 BEGIN 206 --根据区域、省份名称 207 SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%'''; 208 END 209 210 IF ISNULL(@pageSize, 0) <> 0 211 BEGIN 212 SET @Sql = @Sql + ') '; 213 214 SET @SqlCount = @Sql + 215 ' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;'; 216 217 SET @SqlSelectResult = @Sql + 218 ' SELECT * FROM CTE_CustRelatedInfo 219 WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart) 220 + 221 ' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';'; 222 223 PRINT (@SqlSelectResult);--打印输出sql语句 224 225 EXEC sp_executesql @SqlSelectResult;--执行sql查询 226 227 EXEC sp_executesql @SqlCount, 228 N'@Temp int output', 229 @totalRowCount OUTPUT ; --执行count统计 230 END 231 ELSE 232 BEGIN 233 SET @Sql = @sql + ' order by t.CustId ASC '; 234 SET @totalRowCount = 0; --总记录数 235 PRINT (@Sql);--打印输出sql语句 236 EXEC (@Sql);----打印输出sql语句 237 END 238 239 SET NOCOUNT OFF; 240 END 241 GO
调用存储过程 ：
DECLARE @totalRowCount INT EXEC SP_GetCustRelatedInfo2 '','','',10000,1,@totalRowCount OUT
运行结果：查询出330条记录。
以上结果说明：Sql Server 存储过程中查询语句无法直接使用 Union(All)。使用之后，程序不报错，但是查询结果会丢失Union(All)之前的所有查询记录，只保留最后一个Union(All)之后查询语句的查询结果记录。
解决方法：
方案1：先创建视图，将使用Union(All)关键字的sql查询语句放在视图中，然后再存储过程中调用视图。如下：
1 USE [BPMIS_TEST] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[SP_GetCustRelatedInfo2] Script Date: 09/12/2014 15:48:17 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 12 13 -- ============================================= 14 -- Author: 张传宁 15 -- Create date: 2014-9-11 16 -- Description: 获取对账单评估明细表信息列表 17 -- ============================================= 18 ALTER PROCEDURE [dbo].[SP_GetCustRelatedInfo2] 19 @custId NVARCHAR(30) --客户编号 20 , 21 @custNam NVARCHAR(1000) --客户名称 22 , 23 @areaNam NVARCHAR(30)--区域、省份名称 24 , 25 @pageSize INT --单页记录条数 26 , 27 @pageIndex INT --当前页左索引 28 , 29 @totalRowCount INT OUTPUT --输出总记录条数 30 AS 31 BEGIN 32 SET NOCOUNT ON; 33 34 DECLARE @RowStart INT; --定义分页起始位置 35 DECLARE @RowEnd INT; --定义分页结束位置 36 37 DECLARE @Sql NVARCHAR(MAX); --拼接SQL语句 38 DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查询结果语句 39 DECLARE @SqlCount NVARCHAR(MAX); --Sql Count计数语句 40 41 IF @pageIndex > 0 42 BEGIN 43 SET @pageIndex = @pageIndex -1; 44 SET @RowStart = @pageSize * @pageIndex + 1; 45 SET @RowEnd = @RowStart + @pageSize - 1; 46 END 47 ELSE 48 BEGIN 49 SET @RowStart = 1; 50 SET @RowEnd = 999999; 51 END 52 53 IF ISNULL(@pageSize, 0) <> 0 54 BEGIN 55 SET @sql = 56 'With CTE_CustRelatedInfo as ( 57 SELECT ROW_NUMBER () OVER (ORDER BY t.CustId ASC) AS RowNumber, t.* 58 FROM VGetCustRelatedInfo2 AS t 59 WHERE 1=1 ';--此处CTE表达式右括号不写，在后面根据条件判断，追加 60 END 61 ELSE 62 BEGIN 63 SET @sql = 64 'SELECT t.* 65 FROM VGetCustRelatedInfo2 AS t 66 WHERE 1=1 '; 67 END 68 69 IF ISNULL(@custId, '') <> '' 70 BEGIN 71 --根据客户id查询 72 SET @Sql = @Sql + ' AND t.CustId like ''%' + @custId + '%'''; 73 END 74 75 IF ISNULL(@custNam, '') <> '' 76 BEGIN 77 --根据客户名称 模糊查询 78 SET @Sql = @Sql + ' AND t.CustNam like ''%' + @custNam + '%'''; 79 END 80 81 IF ISNULL(@areaNam, '') <> '' 82 BEGIN 83 --根据区域、省份名称 84 SET @Sql = @Sql + ' AND t.AreaNam like ''%' + @areaNam + '%'''; 85 END 86 87 IF ISNULL(@pageSize, 0) <> 0 88 BEGIN 89 SET @Sql = @Sql + ') '; 90 91 SET @SqlCount = @Sql + 92 ' SELECT @Temp = COUNT(*) FROM CTE_CustRelatedInfo;'; 93 94 SET @SqlSelectResult = @Sql + 95 ' SELECT * FROM CTE_CustRelatedInfo 96 WHERE RowNumber Between ' + CONVERT(VARCHAR(10), @RowStart) 97 + 98 ' And ' + CONVERT(VARCHAR(10), @RowEnd) + ';'; 99 100 PRINT (@SqlSelectResult);--打印输出sql语句 101 102 EXEC sp_executesql @SqlSelectResult;--执行sql查询 103 104 EXEC sp_executesql @SqlCount, 105 N'@Temp int output', 106 @totalRowCount OUTPUT ; --执行count统计 107 END 108 ELSE 109 BEGIN 110 SET @Sql = @sql + ' order by t.CustId ASC '; 111 SET @totalRowCount = 0; --总记录数 112 PRINT (@Sql);--打印输出sql语句 113 EXEC (@Sql);----打印输出sql语句 114 END 115 116 SET NOCOUNT OFF; 117 END 118 119 120 121 GO
方案2：在存储过程中先创建临时表，将多个Union(All)前后的sql查询语句的查询结果插入到临时表中，然后操作临时表，最后做其他的处理。
