存储过程中 with as临时表怎么做行数统计？就是分页输出的

FGG丶 2014-08-21 03:18:08

USE [TourSoft_cn]
GO
/****** Object: StoredProcedure [dbo].[PR_lineTJSJ] Script Date: 08/21/2014 08:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PR_lineTJSJ]--线路我发出与收到的统计(含成人小孩全陪出境国内)
@orderww varchar(100)='TotalNum',--排序 Child ChenRen QuanPei Total
@orderuu varchar(10)='',--session UU
@BM varchar(100)='', --单独部门查询
@pageSize int = 15, /*每页显示的记录个数*/
@page int = 1, /*要显示那一页的记录*/
@pageCount int = 1 output, /*查询结果分页后的总页数*/
@Counts int = 1 output , /*查询到的记录数*/
@Sort int = 0--0为升序，1为降序
as
declare @strSQL nvarchar(4000) -- 主语句
declare @strwhere nvarchar(4000) -- where
Declare @FSortType nvarchar(5) --排序方式

declare @P int,@EP int;
set @P=@pageSize*(@page-1)+1;
set @EP=@pageSize*@page;
declare @Sqltxt1 nvarchar(100);
set @pageCount=20;

begin

if @Sort=0
set @FSortType=' asc' --升序
if @Sort=1
set @FSortType=' desc' --降序
set @strwhere= 'group by UU,BM order by '+@orderww+@FSortType
if @BM<>''
set @strwhere='where UU='+@orderuu+' group by UU,BM order by '+@orderww+@FSortType
set @strSQL = 'WITH r AS (
--发出的订单
select
(select z.OrderName) as BM ,
g.OrderUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren,
sum(g.quanpei) as quanpei ,
sum(g.TotalNum) as TotalNum ,
sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID
where g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴''
group by g.OrderUUer,z.OrderName
union all
--收到的订单
select
(select z.OwerName) as BM ,
g.OwerUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren,
sum(g.quanpei) as quanpei ,
sum(g.TotalNum) as TotalNum ,
sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID
where g.OrderID>0 and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'')
group by g.OwerUUer,z.OwerName
)
SELECT ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as chujing FROM r '+@strwhere
exec (@strSQL);
set @Sqltxt1 ='select @Counts=count(*) FROM r'; --这里报错说对象名 'r' 无效。我要输出这个Counts，就是行数

exec sp_executesql @Sqltxt1,N'@Counts int out ',@Counts out
END

...全文

320 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

---涛声依旧--- 2014-08-21

打赏
举报


/*
这样楼主也许会好理解一些
*/

--下面这一句，类似你的 @strSQL 中的r
WITH CTE AS(
SELECT '123' 工号,'张三' 姓名
UNION ALL
SELECT '456' 工号,'李四' 姓名
)
SELECT 工号,姓名 FROM CTE
;

--下面这一句，类似@Sqltxt1 中的CTE
WITH CTE AS(
SELECT '123' 工号,'张三' 姓名
UNION ALL
SELECT '456' 工号,'李四' 姓名
)
SELECT COUNT(1) FROM CTE
;

---涛声依旧--- 2014-08-21

打赏
举报

引用 18 楼 raofeng1993 的回复:

--) --看看这个括号是多还是少 SELECT count(1) FROM CTE'+@strwhere exec (@Sqltxt1); END

还是不行大神[/quote] ) --看看这个括号是多还是少去掉前面的-- 可能少了这个) 这个没有什么技术含量了，自己调试吧，也就是将你的 exec (@strSQL); CTE 语句中的select 字段改为 SELECT count(1) 就行了

FGG丶 2014-08-21

打赏
举报

引用 17 楼 rockyljt 的回复:

USE [TourSoft_cn] GO /****** Object: StoredProcedure [dbo].[PR_lineTJSJ] Script Date: 08/21/2014 08:37:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[PR_lineTJSJ]--线路我发出与收到的统计(含成人小孩全陪出境国内) @orderww varchar(100)='TotalNum',--排序 Child ChenRen QuanPei Total @orderuu varchar(10)='',--session UU @BM varchar(100)='', --单独部门查询 @pageSize int = 15, /*每页显示的记录个数*/ @page int = 1, /*要显示那一页的记录*/ @pageCount int = 1 output, /*查询结果分页后的总页数*/ @Counts int = 1 output , /*查询到的记录数*/ @Sort int = 0--0为升序，1为降序 as declare @strSQL nvarchar(4000) -- 主语句 declare @strwhere nvarchar(4000) -- where Declare @FSortType nvarchar(5) --排序方式 declare @P int,@EP int; set @P=@pageSize*(@page-1)+1; set @EP=@pageSize*@page; declare @Sqltxt1 nvarchar(100); set @pageCount=20; begin if @Sort=0 set @FSortType=' asc' --升序 if @Sort=1 set @FSortType=' desc' --降序 set @strwhere= 'group by UU,BM order by '+@orderww+@FSortType if @BM<>'' set @strwhere='where UU='+@orderuu+' group by UU,BM order by '+@orderww+@FSortType set @strSQL = 'WITH r AS ( --发出的订单 select z.OrderName as BM , g.OrderUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴'' group by g.OrderUUer,z.OrderName union all --收到的订单 select z.OwerName as BM , g.OwerUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') group by g.OwerUUer,z.OwerName ) SELECT ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as chujing FROM r '+@strwhere exec (@strSQL); set @Sqltxt1 = ';WITH CTE AS ( --发出的订单 select z.OrderName as BM , g.OrderUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴'' group by g.OrderUUer,z.OrderName union all --收到的订单 select z.OwerName as BM , g.OwerUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') group by g.OwerUUer,z.OwerName ) --) --看看这个括号是多还是少 SELECT count(1) FROM CTE'+@strwhere exec (@Sqltxt1); END

还是不行大神

---涛声依旧--- 2014-08-21

打赏
举报

USE [TourSoft_cn] GO /****** Object: StoredProcedure [dbo].[PR_lineTJSJ] Script Date: 08/21/2014 08:37:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[PR_lineTJSJ]--线路我发出与收到的统计(含成人小孩全陪出境国内) @orderww varchar(100)='TotalNum',--排序 Child ChenRen QuanPei Total @orderuu varchar(10)='',--session UU @BM varchar(100)='', --单独部门查询 @pageSize int = 15, /*每页显示的记录个数*/ @page int = 1, /*要显示那一页的记录*/ @pageCount int = 1 output, /*查询结果分页后的总页数*/ @Counts int = 1 output , /*查询到的记录数*/ @Sort int = 0--0为升序，1为降序 as declare @strSQL nvarchar(4000) -- 主语句 declare @strwhere nvarchar(4000) -- where Declare @FSortType nvarchar(5) --排序方式 declare @P int,@EP int; set @P=@pageSize*(@page-1)+1; set @EP=@pageSize*@page; declare @Sqltxt1 nvarchar(100); set @pageCount=20; begin if @Sort=0 set @FSortType=' asc' --升序 if @Sort=1 set @FSortType=' desc' --降序 set @strwhere= 'group by UU,BM order by '+@orderww+@FSortType if @BM<>'' set @strwhere='where UU='+@orderuu+' group by UU,BM order by '+@orderww+@FSortType set @strSQL = 'WITH r AS ( --发出的订单 select z.OrderName as BM , g.OrderUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴'' group by g.OrderUUer,z.OrderName union all --收到的订单 select z.OwerName as BM , g.OwerUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') group by g.OwerUUer,z.OwerName ) SELECT ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as chujing FROM r '+@strwhere exec (@strSQL); set @Sqltxt1 = ';WITH CTE AS ( --发出的订单 select z.OrderName as BM , g.OrderUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴'' group by g.OrderUUer,z.OrderName union all --收到的订单 select z.OwerName as BM , g.OwerUUer as UU, sum(g.children) as children, sum(g.chengren) as chengren, sum(g.quanpei) as quanpei , sum(g.TotalNum) as TotalNum , sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei, sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing from uuorder AS g LEFT JOIN uuorderview AS z ON g.orderID = z.orderID where g.OrderID>0 and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') group by g.OwerUUer,z.OwerName ) --) --看看这个括号是多还是少 SELECT count(1) FROM CTE'+@strwhere exec (@Sqltxt1); END

FGG丶 2014-08-21

打赏
举报

引用 14 楼 rockyljt 的回复:


USE [TourSoft_cn]
GO
/****** Object:  StoredProcedure [dbo].[PR_lineTJSJ]    Script Date: 08/21/2014 08:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PR_lineTJSJ]--线路我发出与收到的统计(含 成人 小孩 全陪 出境 国内) 
@orderww varchar(100)='TotalNum',--排序 Child ChenRen QuanPei Total
@orderuu varchar(10)='',--session UU
@BM varchar(100)='', --单独部门查询
@pageSize int = 15,               /*每页显示的记录个数*/
@page int = 1,                    /*要显示那一页的记录*/
@pageCount int = 1 output,        /*查询结果分页后的总页数*/
@Counts int = 1 output ,         /*查询到的记录数*/
@Sort int = 0--0为升序，1为降序
as
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strwhere   nvarchar(4000)       -- where
Declare @FSortType nvarchar(5)        --排序方式

declare @P int,@EP int;
set @P=@pageSize*(@page-1)+1;
set @EP=@pageSize*@page;
declare @Sqltxt1 nvarchar(100);
set @pageCount=20;

begin	

if @Sort=0
set @FSortType=' asc' --升序
if @Sort=1
set @FSortType=' desc' --降序
set @strwhere= 'group by UU,BM order by '+@orderww+@FSortType
if @BM<>''
set @strwhere='where UU='+@orderuu+' group by UU,BM order by '+@orderww+@FSortType
set @strSQL = 'WITH  r AS ( 
--发出的订单
select  
(select z.OrderName) as BM ,
g.OrderUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴''
group by  g.OrderUUer,z.OrderName
union all
--收到的订单
select 
(select z.OwerName) as BM ,
 g.OwerUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0  and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') 
group by g.OwerUUer,z.OwerName
) 
SELECT  ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as  chujing FROM r '+@strwhere
exec (@strSQL);

set @Sqltxt1 = 'WITH  r AS ( 
--发出的订单
select  
(select z.OrderName) as BM ,
g.OrderUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴''
group by  g.OrderUUer,z.OrderName
union all
--收到的订单
select 
(select z.OwerName) as BM ,
 g.OwerUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0  and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') 
group by g.OwerUUer,z.OwerName
) 
SELECT  count(1) FROM r '+@strwhere
exec (@Sqltxt1);
END

消息 102，级别 15，状态 1，第 6 行 ')' 附近有语法错误。

FGG丶 2014-08-21

打赏
举报

引用 12 楼 rockyljt 的回复:

r原来是CTE啊，那样肯定不行的了完整代码如下：

哥你的代码呢

---涛声依旧--- 2014-08-21

打赏
举报


USE [TourSoft_cn]
GO
/****** Object:  StoredProcedure [dbo].[PR_lineTJSJ]    Script Date: 08/21/2014 08:37:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:	 <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PR_lineTJSJ]--线路我发出与收到的统计(含 成人 小孩 全陪 出境 国内) 
@orderww varchar(100)='TotalNum',--排序 Child ChenRen QuanPei Total
@orderuu varchar(10)='',--session UU
@BM varchar(100)='', --单独部门查询
@pageSize int = 15,               /*每页显示的记录个数*/
@page int = 1,                    /*要显示那一页的记录*/
@pageCount int = 1 output,        /*查询结果分页后的总页数*/
@Counts int = 1 output ,         /*查询到的记录数*/
@Sort int = 0--0为升序，1为降序
as
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strwhere   nvarchar(4000)       -- where
Declare @FSortType nvarchar(5)        --排序方式

declare @P int,@EP int;
set @P=@pageSize*(@page-1)+1;
set @EP=@pageSize*@page;
declare @Sqltxt1 nvarchar(100);
set @pageCount=20;

begin	

if @Sort=0
set @FSortType=' asc' --升序
if @Sort=1
set @FSortType=' desc' --降序
set @strwhere= 'group by UU,BM order by '+@orderww+@FSortType
if @BM<>''
set @strwhere='where UU='+@orderuu+' group by UU,BM order by '+@orderww+@FSortType
set @strSQL = 'WITH  r AS ( 
--发出的订单
select  
(select z.OrderName) as BM ,
g.OrderUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴''
group by  g.OrderUUer,z.OrderName
union all
--收到的订单
select 
(select z.OwerName) as BM ,
 g.OwerUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0  and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') 
group by g.OwerUUer,z.OwerName
) 
SELECT  ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as  chujing FROM r '+@strwhere
exec (@strSQL);

set @Sqltxt1 = 'WITH  r AS ( 
--发出的订单
select  
(select z.OrderName) as BM ,
g.OrderUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0 and g.OrderUU='+@orderuu+' and g.LYType<>''合作伙伴''
group by  g.OrderUUer,z.OrderName
union all
--收到的订单
select 
(select z.OwerName) as BM ,
 g.OwerUUer as UU,
sum(g.children) as children,
sum(g.chengren) as chengren, 
sum(g.quanpei) as quanpei , 
sum(g.TotalNum) as TotalNum ,
 sum(case when g.HTType=''国内'' or (g.HTType is null and g.ToCountry=''中国'') then g.TotalNum else 0 end) as guonei,
 sum(case when g.HTType=''出境'' or (g.HTType is null and g.ToCountry<>''中国'') then g.TotalNum else 0 end) as chujing
from uuorder AS g LEFT  JOIN  uuorderview AS z ON g.orderID = z.orderID  
where   g.OrderID>0  and g.OwerUU='+@orderuu+' or (g.OrderUU='+@orderuu+' and g.LYType=''合作伙伴'') 
group by g.OwerUUer,z.OwerName
) 
SELECT  count(1) FROM r '+@strwhere
exec (@Sqltxt1);
END

FGG丶 2014-08-21

打赏
举报

引用 11 楼 blandwolf 的回复:

CTE只能在紧跟的下一句有效我的理解是'select @Counts=count(*) FROM r'需要和前面的在一起，不然肯定出错

该咋办

---涛声依旧--- 2014-08-21

打赏
举报

r原来是CTE啊，那样肯定不行的了完整代码如下：

向东流 2014-08-21

打赏
举报

CTE只能在紧跟的下一句有效我的理解是'select @Counts=count(*) FROM r'需要和前面的在一起，不然肯定出错

FGG丶 2014-08-21

打赏
举报

引用 7 楼 rockyljt 的回复:

set @Sqltxt1 ='select @Counts=count(*) FROM r'; --这里报错说对象名 'r' 无效。我要输出这个Counts，就是行数 exec sp_executesql @Sqltxt1,N'@Counts int out ',@Counts out
--上面两句直接改为这一句就行了啊
select count(*) FROM r;

不行还是找不到r

FGG丶 2014-08-21

打赏
举报

引用 8 楼 dotnetstudio 的回复:

你这个r 肯定是无效的啊，建议你弄个临时表，然后


insert into #temp       //加上这个
SELECT  ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as  chujing FROM r '+@strwhere

最后对临时表统计条数即可

r已经是临时表了

KeepSayingNo 2014-08-21

打赏
举报

你这个r 肯定是无效的啊，建议你弄个临时表，然后


insert into #temp       //加上这个
SELECT  ROW_NUMBER() OVER (ORDER BY sum('+@orderww+')'+@FSortType+') AS RowsID,DENSE_RANK() OVER(ORDER BY sum('+@orderww+')'+@FSortType+') AS Num,BM,UU,sum(children) as children ,sum(chengren) as chengren,sum(quanpei) as quanpei,sum(TotalNum) as TotalNum,sum(guonei) as guonei,sum(chujing) as  chujing FROM r '+@strwhere

最后对临时表统计条数即可

---涛声依旧--- 2014-08-21

打赏
举报

set @Sqltxt1 ='select @Counts=count(*) FROM r'; --这里报错说对象名 'r' 无效。我要输出这个Counts，就是行数 exec sp_executesql @Sqltxt1,N'@Counts int out ',@Counts out


--上面两句直接改为这一句就行了啊
select count(*) FROM r;

FGG丶 2014-08-21

打赏
举报

[yabao=]3[/yabao]

FGG丶 2014-08-21

打赏
举报

引用 2 楼 shmilywcd 的回复:

看的很累，友情帮顶

能给力不哥子

FGG丶 2014-08-21

打赏
举报

引用 3 楼 rockyljt 的回复:


set @Sqltxt1 =N'select @Counts=count(*) FROM r'; --这里要个N哦,sp_executesql 要求的nvarchar类型

也不行啊

---涛声依旧--- 2014-08-21

打赏
举报


set @Sqltxt1 =N'select @Counts=count(*) FROM r'; --这里要个N哦,sp_executesql 要求的nvarchar类型

天-笑 2014-08-21

打赏
举报

看的很累，友情帮顶

FGG丶 2014-08-21

打赏
举报

人人人人人。。救命啦

日期类型 date 7字节用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，7个字节分别表示世纪、年、月、日、时、分和秒二进制数据类型 row 1~2000字节可变长二进制数据，在具体...

dual：亚表，临时用。如：desc dual；／from dual； rollback：回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次(DDL和DCL语句会自动提交，不能回溯)。可以用commit语句提交，这样就回溯不回了。 ...

Informatica 项目1薪酬统计项目EDW层明细表临时表1sql 36_Informatica 项目1薪酬统计项目EDW层明细表临时表2ETL 37_Informatica 项目1薪酬统计EDW层明细表1 38_Informatica 项目1薪酬统计EDW层明细表2 39_...

有关分页 SQL 的资料很多，有的使用存储过程，有的使用游标。...其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。方法1：适用于 SQL Server 2000/2005 SELECT TOP 页大小 ...

在编写分页存储过程前我们先为数据库创建一个测试表，这个测试表明叫做order，当中有3个字段，分别是or_id,orName,dateSta;下面创建表脚本： CREATE TABLE [dbo].[Orders]( [or_id] [int] IDENTITY(1,1) NOT ...

应用实例

27,579

社区成员

68,558

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章

存储过程中 with as临时表 怎么做行数统计？ 就是分页输出的

存储过程中 with as临时表怎么做行数统计？就是分页输出的