聚合列表问题
如类型表 t:
id name
-------------------
1 aa
2 bb
数据表 D:
id name type
---------------------
1 ab 1
2 ac 1
3 ae 1
4 af 1
5 bc 2
6 ba 2
7 bd 2
如何获得以下结果(每个类型内容只显示两个):
t.name name
---------------------
aa af
aa ae
bb bd
bb ba
如何获得以下结果(显示类型名称到内容中,并每个类型内容只显示两个):
aa 1
aa af
aa ae
bb 2
bb bd
bb ba
[解决办法]
select D.name,t.name FROM D,t,
(select D.type,min(id) s_id FROM D,(select type,min(id) as min_id from D group by type) tmpD WHERE D.type=tmpD.TYPE AND D.id> tmpD.min_id GROUP BY D.type) S
where D.type=t.id and D.type=S.type and D.id <=S.s_id
