在利用php+mysql开发中,对表products进行select检索,products表结构如下
Proid 产品ID auto_increment
Product_name 产品名称
Model 产品规格
Price 产品金额
Description 说明及正文
Photo 图片
Fuserid 发布产品的对应的会员的ID每个用户可发布20-50条产品信息 对应关键字的select检索结果集是已经OK了,问题是只需要找出匹配关键字的每个用户的最后5条信息,也就是在不需要临时表的帮助下,如何对sql检索select语句中按Proid字段desc筛选匹配关键字的每一个用户的最后5条信息?
Proid 产品ID auto_increment
Product_name 产品名称
Model 产品规格
Price 产品金额
Description 说明及正文
Photo 图片
Fuserid 发布产品的对应的会员的ID每个用户可发布20-50条产品信息 对应关键字的select检索结果集是已经OK了,问题是只需要找出匹配关键字的每个用户的最后5条信息,也就是在不需要临时表的帮助下,如何对sql检索select语句中按Proid字段desc筛选匹配关键字的每一个用户的最后5条信息?
sql语句执行如下:
SELECT `Proid`, `Product_name`, `Fuserid` FROM `products`
WHERE LOWER(`Product_name`) REGEXP BINARY '[[:<:]]valve[[:>:]]'
ORDER BY `Proid` DESC
LIMIT 0 , 80;结果集如下:
Proid Product_name Fuserid
2 API Gate Valve volvo
3 Cast Steel Globe Valve volvo
4 Swing Type Check Valve volvo
6 Wafer Type Lift Check Valve volvo
201 Floating Ball Valve land_rover
202 Ball Valve land_rover
203 V Type Ball Valve land_rover
204 Three Way Ball Valve land_rover
208 Turnnion Ball Valve land_rover
209 Bellow Globe Valve land_rover
210 Bellows Globe Valve land_rover
211 Gate valve land_rover
212 Safety Valve land_rover
213 Gate valve land_rover
214 Safety Valve land_rover
215 Ball Valve Body land_rover
218 Lug Butterfly Valve land_rover
220 Adjust Butterfly Valve land_rover
221 Clamp Butterfly Valve land_rover
224 Check Valve land_rover
226 Swing Check Valve land_rover
227 Forged Steel Check Valve land_rover
230 Forged Steel Gate Valve land_rover
232 Forged Steel Globe Valve land_rover
233 Y Pattern Globe Valve land_rover
234 Carbon Steel RF End Gate Valve land_rover
235 Forged Steel Gate Valve land_rover
236 Flat Gate Valve land_rover
237 Gate valve land_rover
238 Gate valve land_rover
239 Forged Steel Globe Valve land_rover
240 Bellows Gate Valve land_rover
313 Sanitary Clamped Butterfly Valve puma
315 Sanitary Threaded Butterfly Valve puma
316 Sanitary three piece butterfly valve puma
317 Sanitary non-residue ball valve puma
319 Sanitary Welded Check Valve puma
320 Sanitary Threaded Check Valve puma
321 Sanitary non-residue ball valve puma
322 Sanitary All through ball valve puma
323 Clamped three piece sanitary ball valve puma
324 Sanitary Welded Check Valve puma
325 Sanitary Threaded Check Valve puma
326 Sanitary Diaphragm Valve puma
328 Pneumatic valve puma 结果集如上,这些信息分别归属三个用户,三个用户ID分别为volvo、land_rover、puma,我只想取得三个用户各自最后的5条信息,望高人指点…
select * from products where Fuserid=‘XX’ order by proid desc limit 5
通过实例程序已经搞定,现给分结帖。
不过,用户少的话,速度应该还可以。