在sql中选择限制

enxuqcxy 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(428)

我写了这个查询，但是在第15行（limit子句）给了我一个错误。我们有3张table，州，电子邮件和收件人。

CREATE PROCEDURE `inbox`(IN `page_size` INT,IN `page_number` INT)
BEGIN
    set @curr = ( SELECT entered_accounts.username
                FROM entered_accounts
                WHERE entered_accounts.time = (SELECT Max(entered_accounts.time) FROM entered_accounts));
    SET @tmp = page_number-1;
    SET @offset = page_size * @tmp;
    SELECT email.subject, email.body, email.time, state.readed, state.deleted
    FROM email INNER JOIN reciever USING(email_id) 
                INNER JOIN state USING (email_id) 
    WHERE state.username=reciever.username AND LOWER(reciever.username)=LOWER(@curr) AND NOT reciever.username=email.username
    ORDER BY email.time DESC
    LIMIT @offset,page_size;
END

sql stored-procedures sql-order-by sql-limit

来源：https://stackoverflow.com/questions/61983443/select-limit-in-sql

1条答案

按热度按时间

x9ybnkn61#

mysql不支持中的用户变量 LIMIT 条款。相反，可以使用正则变量：

DELIMITER //
CREATE PROCEDURE `inbox`(IN `page_size` INT,IN `page_number` INT)
BEGIN
    DECLARE v_offset INT;
    SET @curr = ( SELECT entered_accounts.username
                FROM entered_accounts
                WHERE entered_accounts.time = (SELECT Max(entered_accounts.time) FROM entered_accounts));
    SET @tmp = page_number-1;
    SET v_offset = page_size * @tmp;
    SELECT email.subject, email.body, email.time, state.readed, state.deleted
    FROM email INNER JOIN reciever USING(email_id) 
                INNER JOIN state USING (email_id) 
    WHERE state.username=reciever.username AND LOWER(reciever.username)=LOWER(@curr) AND NOT reciever.username=email.username
    ORDER BY email.time DESC
    LIMIT v_offset, page_size;
END//

展开查看全部

赞(0）回复(0）举报 2021-07-26

我来回答

在sql中选择限制

1条答案

相关问题

热门标签

最新问答