php - 在mysql中对连接表应用限制

This question already has answers here:

Using LIMIT within GROUP BY to get N results per group?
（14个答案）
四年前关闭。
我有两个表用户和查询。用户可以发布多个查询。
MySQL表架构：-

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `queries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address` text,
  `city` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `zipcode` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我想对它应用限制，即查询从查询表中为每个用户获取5个查询。
我应该如何对此编写查询？

最佳答案

试试看-

SELECT x.*
FROM (SELECT t.user_id,t.id,
               CASE
                 WHEN @category != t.user_id THEN @rownum := 1
                 ELSE @rownum := @rownum + 1
               END AS rank,
               @category := t.user_id AS var_category
          FROM queries t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.user_id,t.id) X
      WHERE x.rank<=5

关于php - 在mysql中对连接表应用限制，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32391658/