my97

http://www.my97.net/demo/index.htm

https://blog.csdn.net/xiangff_csdn/article/details/78688506

SELECT * FROM (
    SELECT ROW_NUMBER() OVER ( ORDER BY p.CreateTime desc ) AS RowNumber,
    COUNT(1) OVER() AS Total,
     p.OrderId, p.Id,ui.RealName,p.Point,ui.UId,ui.UserPhone,pui.UserType,
case when p.UId=p.UserId then '自己' else '上级' end as dx,

case when ui.RealName is null then ui.NickName else  ui.RealName end as mingcheng,

case when pui.RealName is null then pui.NickName else  pui.RealName end as shangji,


pui.RealName as Uname,pui.UserPhone as Uphone,
p.Status,p.CreateTime,p.ModifyTime,o.OrderNum
from UserInfo ui
left join PointRebate p on ui.Id=p.UserId
left join UserInfo pui on p.UId=pui.Id
left join dbo.Orders o on o.id=p.OrderId
where 1=1 and p.OrderId is not null
) AS t
WHERE RowNumber BETWEEN {0} AND {1}
ORDER BY t.RowNumber

and  (isnull(ui.NickName ,'')+isnull(ui.RealName,'')) like '%{0}%'//多个字段模糊查询