比如我表是这样:
id(主键) typeId(类型) price(价格)
条件:1 根据typeId分组后查出该类型的第一个id (也就是最后结果是有多少种类型就有多少个id)
2 并且要查出该类型的所有价格的平均价格
高手还能做的话把分页也写出来(分页传的是当前页索引,每页数量固定10就好了)
最好是一条sql语句 或者一个存储过程也行
或者能把第一个条件查出来也行
id(主键) typeId(类型) price(价格)
条件:1 根据typeId分组后查出该类型的第一个id (也就是最后结果是有多少种类型就有多少个id)
2 并且要查出该类型的所有价格的平均价格
高手还能做的话把分页也写出来(分页传的是当前页索引,每页数量固定10就好了)
最好是一条sql语句 或者一个存储过程也行
或者能把第一个条件查出来也行
http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
参考此人提问方式。
FROM (SELECT row=ROW_NUMBER()OVER(ORDER BY ID),* FROM Tab1 AS a WHERE NOT EXISTS(SELECT 1 FROM Tab1 WHERE TypeID=a.TYPEID AND ID>a.ID))t
WHERE row BETWEEN (@page-1*@pagerow) AND @page*@pagerow分頁用row_number
select *,(select avg(price) from tb where typeID = t.typeID)avg_price
from tb t
where not exists (select 1 from tb where typeID = t.typeID and id > t.id)
--SQL 2000
select iii identity(int,1,1),*,(select avg(price) from tb where typeID = t.typeID)avg_price into #temp
from tb t
where not exists (select 1 from tb where typeID = t.typeID and id > t.id) --用到临时表select *
from #temp
where iii between (@pageint*10 + 1) and (@pageint + 1)*10--SQL 2005
select *
from (
select *,(select avg(price) from tb where typeID = t.typeID)avg_price,
row_number() over (order by getdate())iii
from tb t
where not exists (select 1 from tb where typeID = t.typeID and id > t.id)
)U
where iii between (@pageint*10 + 1) and (@pageint + 1)*10
from TableA
group by TypeId
order by TypeId
(
id INT IDENTITY PRIMARY KEY,
typeId VARCHAR(100),
price DECIMAL(10, 2)
)
INSERT #temp
SELECT 'A', 12.1 UNION ALL
SELECT 'A', 12.2 UNION ALL
SELECT 'B', 12.3 UNION ALL
SELECT 'C', 12.4 UNION ALL
SELECT 'D', 12.5 UNION ALL
SELECT 'B', 12.6DECLARE @PageStart INT
SET @PageStart = 1SELECT * FROM
(
SELECT rowno = ROW_NUMBER() OVER (ORDER BY a.typeId), c.*, b.avgPrice FROM
(SELECT DISTINCT typeId FROM #temp) a
CROSS APPLY
(SELECT avgPrice = AVG(price) FROM #temp WHERE typeId = a.typeId) b
CROSS APPLY
(SELECT TOP(1) * FROM #temp WHERE typeId = a.typeId ORDER BY id) c
) d
WHERE rowno BETWEEN (@PageStart-1)*10+1 AND @PageStart*10
实现分页使用存储过程即可
--分页
Select * From (
--汇总而且排好次序的结果集合
Select a.TypeId, Min(a.id) as Id, Avg(a.Price) as Price,
Row_Number() over(Order By a.TypeId) as rowNum From 你的表 a
group by a.TypeId
Order By a.TypeId
) t
Where
t.rowNum> (页号-1)*10 and t.rowNum<=页号*10
order by rowNum
如果说要求每一页顶上都显示上一页的最后1条记录的话,那么分页的代码会变成Select * from (...) t Where
(页号=1 and t.rowNum>0 and t.rowNum<= 10)
or (页号>1 and t.rowNum>9+(页号-2)*9 and t.rowNum<=9+(页号-2)*9+10)
order by rowNum
SELECT * FROM Tab1 AS a WHERE NOT EXISTS(SELECT 1 FROM Tab1 WHERE TypeID=a.TYPEID AND ID>a.ID)
--并且要查出该类型的所有价格的平均价格
SELECT TypeID,AVG(Price) AS Price FROM Tab1 GROUP BY TypeID
(
id INT IDENTITY PRIMARY KEY,
typeId VARCHAR(100),
price DECIMAL(10, 2)
)
select a.id,typeid,price from (select min(id) id,typeid from #temp where typeid in( select typeid from #temp group by typeid)) a inner join (select typeid,avg(price) price from #temp group by typeid) b on a.typeid=b.typeid