# sql 统计查询 分页的问题

0

id name bid cid  time

1   jerry   1     1   2015-11-09
2   tom    1     1   2015-11-08
3   lucy    2     1   2015-11-07
4   tom    1     1   2015-11-08
5   lucy    3     1   2015-11-07

bid   name
1     状态1
2     状态2
3     状态3

cid  name
1     公司1

0

-- 给一段SQLServer 2005+ 下的代码

-- 设置每页条数和取第几页
declare @pagesize int, @pagenum int
set @pagesize = 20
set @pagenum = 1

select C.name as [公司], t.time, t.[状态1], t.[状态2], t.[状态3]
from
(
select
cid,
time,
sum(case bid when 1 then 1 else 0 end) as [状态1],
sum(case bid when 2 then 1 else 0 end) as [状态2],
sum(case bid when 3 then 1 else 0 end) as [状态3],
row_number() over (order by cid, time) as [rank]
from A group by cid, time
)t inner join C
on t.rank between (@pagenum-1)*@pagesize +1  and @pagenum*@pagesize and t.cid = C.cid

0

Firen | 园豆：5483 (大侠五级) | 2015-11-19 15:56
0

int strPage=(pageIndex-1)*pageSize;

int endPage=pageIndex*pageSize;

select top 20 * from

(

select row_number() over(order by a.排序的列 desc)rownum,* from 表名 a

)temp where temp.rownum>=starPage and temp<=endPage

get it

0

SELECT * FROM (
SELECT #C.name AS 公司,#A.time AS 时间,#B.NAME FROM #A,#B,#C
where #A.bid = #B.bid AND #A.cid=#C.cid)X
PIVOT(COUNT(name) FOR name IN (状态1,状态2,状态3))a

您需要登录以后才能回答，未注册用户请先注册