注册 登录
编程论坛 ASP.NET技术论坛

在分页查询中能不能让distinct不包含rownum

烧包谷 发布于 2012-09-17 20:59, 478 次点击
WITH result AS (
"SELECT b.Id as ID,b.Number as Number,b.UserName as Creater,b.BusinessTheme as Them,d.DeptName as DeptName,b.CreateTime as CreateTime,fp.UpdateTime as UpdateTime,u.staffid as Staffid,t.username as AccpetName");
,ROW_NUMBER() OVER(ORDER BY b.Id asc) AS rownum FROM BusinessApply b
 join FlowProcess fp on b.Id=fp.BizOrderId join tbl_dept d on b.UserDeptId=d.Id join tbl_user as u on u.userid = b.UserId join CooperativeAudiTasks as c on c.ProcessId=fp.Id join tbl_user as t on t.userid=c.UserId
 join FlowOperatorLog fol  on b.id=fol.BizOrderNo WHERE c.Status=0 )
SELECT TOP(@pageSize) * FROM result WHERE rownum > (@pageSize*@paeIndex)
在这一sql中,能不能用distinct。谢谢大家指点。
3 回复
#2
烧包谷2012-09-17 21:00
第一次贴的sql语句中错了
WITH result AS (
SELECT b.Id as ID,b.Number as Number,b.UserName as Creater,b.BusinessTheme as Them,d.DeptName as DeptName,b.CreateTime as CreateTime,fp.UpdateTime as UpdateTime,u.staffid as Staffid,t.username as AccpetName");
 ,ROW_NUMBER() OVER(ORDER BY b.Id asc) AS rownum FROM BusinessApply b
join FlowProcess fp on b.Id=fp.BizOrderId join tbl_dept d on b.UserDeptId=d.Id join tbl_user as u on u.userid = b.UserId join CooperativeAudiTasks as c on c.ProcessId=fp.Id join tbl_user as t on t.userid=c.UserId
join FlowOperatorLog fol  on b.id=fol.BizOrderNo WHERE c.Status=0 )
 SELECT TOP(@pageSize) * FROM result WHERE rownum > (@pageSize*@paeIndex)
 在这一sql中,能不能用distinct。谢谢大家指点。
#3
belin20002012-09-18 13:16
为什么不用存储过程呢?
#4
烧包谷2012-09-30 07:05
额,我储存过程写的很差!
1