mysql – 限制sql中的计数

我有一个如下所示的查询

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个事件.

有关完整的表详细信息,请参阅下面的SQL Fiddle链接

http://sqlfiddle.com/#!2/21ad85/32 最佳答案 此查询为您提供了您尝试分组和计数的五行：

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

http://sqlfiddle.com/#!2/21ad85/37

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!