我有一个如下所示的查询
SELECT
venueid as VENUES,venue2.venue AS LOCATION,(SELECT COUNT(*) FROM events WHERE (VENUES = venueid) AND eventdate < CURDATE()) AS number
FROM events
INNER JOIN venues as venue2 ON events.venueid=venue2.id
GROUP BY VENUES
ORDER BY number DESC
我想限制计数来计算表中的最后5行(按id排序)但是当我添加一个limt 0,5时,结果似乎没有改变.在计算时,您在哪里添加限制以限制计数的行数？
CREATE TABLE venues (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,venue VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,category VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
CREATE TABLE events (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,eventdate DATE NOT NULL,title VARCHAR(255),venueid INT,categoryid INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
INSERT INTO venues (id,venue) VALUES
(1,'USA'),(2,'UK'),(3,'Japan');
INSERT INTO categories (id,category) VALUES
(1,'Jazz'),'Rock'),'Pop');
INSERT INTO events (id,eventdate,title,venueid,categoryid) VALUES
(1,20121003,'Title number 1',1,3),20121010,'Title number 2',2,1),20121015,'Title number 3',3,2),(4,20121020,'Title number 4',(5,20121022,'Title number 5',(6,20121025,'Title number 6',(7,20121030,'Title number 7',(8,20121130,'Title number 8',(9,20121230,'Title number 9',(10,20130130,'Title number 10',3);
预期结果应如下所示
|VENUES |LOCATION |NUMBER |
|1 | USA | 3 |
|2 | UK | 1 |
|3 | Japan | 1 |
截至发布时间为9,8,7,6,5是当前日期之前的最后5个事件.
最佳答案
此查询为您提供了您尝试分组和计数的五行：
SELECT *
FROM events
WHERE eventdate < CURDATE()
ORDER BY eventdate DESC
LIMIT 5
现在,您可以将此查询用作子查询.您可以使用子查询的结果加入,就像它是普通的表一样：
SELECT
venueid as VENUES,COUNT(*) AS number
FROM
(
SELECT *
FROM events
WHERE eventdate < CURDATE()
ORDER BY eventdate DESC
LIMIT 5
) AS events
INNER JOIN venues as venue2 ON events.venueid=venue2.id
GROUP BY VENUES
ORDER BY number DESC