前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Sql Server 存储过程中查询数据无法使用 Union(All)

Sql Server 存储过程中查询数据无法使用 Union(All)

作者头像
张传宁IT讲堂
发布2019-09-17 17:00:54
4.9K0
发布2019-09-17 17:00:54
举报
文章被收录于专栏:.NET企业级解决方案应用与咨询

  微软Sql Server数据库中,书写存储过程时,关于查询数据,无法使用Union(All)关联多个查询。

1、先看一段正常的SQL语句,使用了Union(All)查询:

代码语言:javascript
复制
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查询语句放在视图中:

代码语言:javascript
复制
 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、创建存储过程,代码如下:

代码语言:javascript
复制
  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查询语句放在视图中,然后再存储过程中调用视图。如下:

代码语言:javascript
复制
  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查询语句的查询结果插入到临时表中,然后操作临时表,最后做其他的处理。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2014-09-12 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档