MySQL - 每个WHERE_IN问题的棘手限制

我希望做以下(见伪代码); 我想为每个gd.id(7,11或9)选择4行.我错误地使用限制因为总共只占用了4行.任何人都知道如何更改此查询以实现我的目标？

SELECT gd.gid, gd.aid, li.ads, li.til
FROM gd
JOIN li ON li.a_id = gd.aid
WHERE gd.gid
IN (
'7', '11', '9'
)
ORDER BY li.timestamp DESC
LIMIT 4 #FOR EACH ;-)

谢谢!

冰

ps也许某种类型的group_by？

好的,我现在发布了第二个答案,因为我理解了你的表之间的关系.

CREATE TABLE gd (
  aid INT AUTO_INCREMENT PRIMARY KEY,
  gid INT
);

INSERT INTO gd (gid) VALUES
  (7), (7), (7),                -- fewer than four rows
  (9), (9), (9), (9),           -- exactly four rows
  (11), (11), (11), (11), (11); -- greater than four rows

CREATE TABLE li (
  a_id INT AUTO_INCREMENT PRIMARY KEY,
  ads VARCHAR(10),
  til VARCHAR(10),
  `timestamp` TIMESTAMP
);

INSERT INTO li (ads, til, `timestamp`) VALUES
  ('foo1', 'bar1', '2008-01-01'),
  ('foo2', 'bar2', '2008-02-01'),
  ('foo3', 'bar3', '2008-03-01'),
  ('foo4', 'bar4', '2008-04-01'),
  ('foo5', 'bar5', '2008-05-01'),
  ('foo6', 'bar6', '2008-06-01'),
  ('foo7', 'bar7', '2008-07-01'),
  ('foo8', 'bar8', '2008-08-01'),
  ('foo9', 'bar9', '2008-09-01'),
  ('foo10', 'bar10', '2008-10-01'),
  ('foo11', 'bar11', '2008-11-01'),
  ('foo12', 'bar12', '2008-12-01');

因此,您希望每个值的前四行gd.gid,具体取决于timestamp关联表中的值li.

SELECT g1.gid, g1.aid, l1.ads, l1.til, l1.`timestamp`
FROM gd AS g1
  INNER JOIN li AS l1 ON (g1.aid = l1.a_id)
  LEFT OUTER JOIN (
    gd AS g2 INNER JOIN li AS l2 ON (g2.aid = l2.a_id)
  ) ON (g1.gid = g2.gid AND l1.`timestamp` <= l2.`timestamp`)
WHERE g1.gid IN ('7', '11', '9')
GROUP BY g1.aid
HAVING COUNT(*) <= 4
ORDER BY g1.gid ASC, l1.`timestamp` DESC;

输出如下:

+------+-----+-------+-------+---------------------+
| gid  | aid | ads   | til   | timestamp           |
+------+-----+-------+-------+---------------------+
|    7 |   3 | foo3  | bar3  | 2008-03-01 00:00:00 | 
|    7 |   2 | foo2  | bar2  | 2008-02-01 00:00:00 | 
|    7 |   1 | foo1  | bar1  | 2008-01-01 00:00:00 | 
|    9 |   7 | foo7  | bar7  | 2008-07-01 00:00:00 | 
|    9 |   6 | foo6  | bar6  | 2008-06-01 00:00:00 | 
|    9 |   5 | foo5  | bar5  | 2008-05-01 00:00:00 | 
|    9 |   4 | foo4  | bar4  | 2008-04-01 00:00:00 | 
|   11 |  12 | foo12 | bar12 | 2008-12-01 00:00:00 | 
|   11 |  11 | foo11 | bar11 | 2008-11-01 00:00:00 | 
|   11 |  10 | foo10 | bar10 | 2008-10-01 00:00:00 | 
|   11 |   9 | foo9  | bar9  | 2008-09-01 00:00:00 | 
+------+-----+-------+-------+---------------------+