Mysql -在同一个查询中,动态地从结果中获取索引位置,并将其放在另一个查询的OFFSET中

wkyowqbh  于 2023-03-17  发布在  Mysql
关注(0)|答案(2)|浏览(108)

我在tbl_messages表中有一个包含id列(_id,integer)和post_id列(_post_id,string)的表。
我想做一个查询,在那里我可以知道行的位置在一个简单的查询没有限制,把它放在偏移。
我可以在PHP中使用search_array进行查询并获取位置,但我希望在同一个查询中进行。
例如:
SELECT * FROM tbl_messages WHERE _user = '2' AND _instance = '5' ORDER BY _date ASC LIMIT 10, OFFSET (Here I would like to return the position in the result, and start the result list from post_id = 'R5G96H5D85S9' by example).
我找了几个小时也没找到任何能满足我兴趣的东西,我很感激任何给予我灵感的尝试;
谢谢

a9wyjsp7

a9wyjsp71#

您不能在一个查询中执行所描述的操作,因为LIMITOFFSET的参数不能是表达式或子查询。
所以你必须在运行查询之前计算位置。你说过你已经知道怎么做了。
允许使用整型参数或变量作为LIMIT的参数。

SELECT * FROM tbl_messages 
WHERE _user = '2' AND _instance = '5' 
ORDER BY _date ASC LIMIT 10 OFFSET ?

(注意我去掉了OFFSET前面的逗号)

0g0grzrc

0g0grzrc2#

好吧,对不起那些说这不可能同时又放弃的人。对于那些来这个问题寻找答案的人,我是这样理解的:

SELECT @a = row_num - 1 FROM (SELECT row_number() over (ORDER BY `_date` DESC) row_num, `_message_id` AS "m_id" FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC) AS tab WHERE tab.`m_id` = 'BAE58BEA44DD7972';
PREPARE PREP FROM "SELECT * FROM `tbl_messages` WHERE `_contact` = '2' AND `_instance` = '5' ORDER BY `_date` DESC LIMIT 10 OFFSET ?";
EXECUTE PREP USING @a;

这可以用PHP写成这样:

$sql = $conn->prepare('SELECT row_num - 1 AS num FROM (SELECT row_number() over (ORDER BY _date DESC) row_num, _message_id AS "m_id" FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC) AS tab WHERE tab.m_id = "BAE58BEA44DD7972"');
$sql->execute(array(':contact' => 2, ':instance' => 5));
$result = $sql->fetch(PDO::FETCH_ASSOC);
$a = $result['num'];

$stmt = $conn->prepare("SELECT * FROM tbl_messages WHERE _contact = :contact AND _instance = :instance ORDER BY _date DESC LIMIT 10 OFFSET :offset");
$contact = 2;
$instance = 5;
$offset = intval($a);
$stmt->bindParam(':contact', $contact, PDO::PARAM_INT);
$stmt->bindParam(':instance', $instance, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();

echo '<pre>';
var_dump($result);

相关问题