按分组进行分页

[Yole]

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [a]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
select * from a order by [品名]
/*
ID          品名     入库数量        入库时间
----------- ------ ----------- -----------------------
2           方便面    60          2013-01-03 00:00:00.000
3           方便面    50          2013-01-03 00:00:00.000
5           方便面    50          2013-01-05 00:00:00.000
1           矿泉水    100         2013-01-02 00:00:00.000
4           矿泉水    80          2013-01-04 00:00:00.000

(5 行受影响)
*/

--需求：id：2，3，5一页；id：1,4一页

按品名分组分页

[开着拖拉机泡妞]

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [a]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
go

--需求：id：2，3，5一页；id：1,4一页
--建立一个品名表
;with t as
(
select 
	px=ROW_NUMBER()over(order by getdate()),* from(
		select distinct [品名] from a)a
),
m as
(
select t.px,a.ID,a.品名,a.入库时间,a.入库数量 from t,a where t.品名=a.品名
)
select * from m where px=1
/*
px	ID	品名	入库时间	入库数量
------------------------------------------------
1	2	方便面	2013-01-03 00:00:00.000	60
1	3	方便面	2013-01-03 00:00:00.000	50
1	5	方便面	2013-01-05 00:00:00.000	50
*/

--下一页就是px=2，再下一页就是px=3......这样每个相同品名的px值一样，每页只会显示同一个品名的数据了

[昵称90天可改一次]

if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [a]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
select * from a order by [品名]

WITH List
AS(
	SELECT a.ID,
			a.品名,
			a.入库数量,
			a.入库时间
		FROM	a 	
)
SELECT	*
FROM	List l
WHERE	l.品名='方便面'