SQL:在给定ID的where子句中查找下一行 [英] SQL: Find next row in a where clause given an ID

问题描述

我在MySQL数据库中有一个名为products的表. products看起来像这样:

I have a table called products in a MySQL database. products looks some what like this:

id   name     din        strength active deleted
1    APA TEST 00246374   25       1      0
4    APA BOB  00246375   50       1      0
5    APA TIRE 00246888   50       1      0
7    APA ROT  00521414   100      1      0
9    APA APA  01142124   100      1      0
6    APA CODE 00121212   150      1      0
8    APA SERV 00011145   600      1      0

很明显，我省略了对我的问题不重要的几列.当查询该表时，我将按几个不同的列之一进行排序(用户界面允许单个用户更改排序列和顺序)，并且我可能会有一个搜索子句，在这种情况下，我将对名称和DIN.

Obviously I've left out several columns not important to my question. When I query this table, I will be sorting by one of several different columns (The user interface allows individual users to change the sorting column and order), and I may have a search clause in which case I'll do a LIKE clause on NAME and DIN.

我想知道的是，给定排序信息和搜索信息以及特定产品的ID(例如，我搜索004，该结果返回了3个结果，并且我正在查看其中一个结果)，如何获得下一个和上一个产品?

What I want to know is, given the sorting info and search info, and the ID of a specific product (Say I searched for 004, which returned 3 results, and I am viewing one of them), how could I get the next and previous products?

我需要这样做，因为如果用户在搜索和排序结果后单击以编辑/查看其中一种产品，那么他们希望能够在不转到上一页的情况下循环浏览结果.

I need to do this, because if a user clicks to edit/view one of the products after searching and sorting results, they want to be able to cycle through results without going to the previous page.

在SQL中是否有一种很好且高效的方法来执行此操作，还是我最好使用PHP?任何想法也欢迎.

Is there a good and efficient way to do this in SQL, or am I best off using PHP? Any ideas are also welcome.

当前正在使用此SQL查询，如果我按strength列进行排序，因为存在重复的值，则会遇到问题

Currently using this SQL query, which is experiencing issues if I sort by the strength column as there are duplicate values

SELECT T.*
FROM `wp_products` T
INNER JOIN `wp_products` curr on curr.id = 38
   AND ((T.strength = curr.strength and T.id < curr.id)
    OR (T.strength > curr.strength))
WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE '%%' OR T.din LIKE '%%')
ORDER BY T.strength ASC, T.id ASC
LIMIT 1

我的PHP代码(使用WordPress)(旨在获取下一项)

My PHP code (using WordPress) (Designed to get the next item)

    $sql = 'SELECT T.*
FROM `' . $wpdb->prefix . 'apsi_products` T
INNER JOIN `' . $wpdb->prefix . 'apsi_products` curr on curr.id = ' . $item->id . '
   AND ((T.' . $wpdb->escape( $query['orderby'] ) . ' = curr.' . $wpdb->escape( $query['orderby'] ) . ' and T.id > curr.id)
    OR (T.' . $wpdb->escape( $query['orderby'] ) . ' > curr.' . $wpdb->escape( $query['orderby'] ) . '))
WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE \'%' . $query['where'] . '%\' OR T.din LIKE \'%' . $query['where'] . '%\')
ORDER BY T.' . $wpdb->escape( $query['orderby'] ) . ' ' . $query['order'] . ', T.id ASC
LIMIT 1;';

推荐答案

您需要引用当前记录，然后根据已排序的列逐步查找下一条记录.下面的示例假定它在

You need to have a reference to the current record, and then progressively look for the next record based on the sorted columns. The example below assumes it is sorted on

ORDER BY Active, DIN, NAME

第一:

SELECT *
FROM TABLE
WHERE NAME LIKE '%X%' AND DIN LIKE '%%'
ORDER BY Active, DIN, Name
LIMIT 1;

下一步:( 请确保使用适当的括号分隔CURR.ID = 6和AND-OR！ )

Next: (make sure you separate the CURR.ID = 6 and the AND-ORs with proper brackets!)

SELECT *
FROM TABLE T
INNER JOIN TABLE CURR ON CURR.ID = 6 # the current ID being viewed
   AND ((T.Active = Curr.Active AND T.DIN = Curr.DIN AND T.NAME > Curr.Name)
     OR (T.Active = Curr.Active AND T.DIN > Curr.DIN)
     OR T.Active > Curr.Active)
WHERE T.NAME LIKE '%X%' AND T.DIN LIKE '%%'
ORDER BY T.Active, T.DIN, T.Name
LIMIT 1;

下面提供了一个工作样本

A working sample presented below

create table products
(ID int, SEED int, NAME varchar(20), DIN varchar(10), ACTIVE int, DELETED int);
insert products values
(1,  0,    'Product #1', '004812', 1,    0),
(2,  0,    'Product #2', '004942', 0,    0),
(3,  0,    'Product #3', '004966', 1,    0),
(4,  0,    'Product #4', '007437', 1,    1),
(5,  2,    'Product #2', '004944', 0,    0),
(6,  2,    'Product #2', '004944', 1,    0);

SELECT *
FROM products
WHERE active = 1 AND deleted = 0
ORDER BY din DESC, ID desc;

Output:
"ID";"SEED";"NAME";"DIN";"ACTIVE";"DELETED"
"3";"0";"Product #3";"004966";"1";"0"
"6";"2";"Product #2";"004944";"1";"0"
"1";"0";"Product #1";"004812";"1";"0"

如果当前是ID = 6的行，则可以使用

If current is the row with ID=6, the next record can be retrieved using

SELECT T.*
FROM products T
INNER JOIN products curr on curr.ID = 6
   AND ((T.din = curr.din and T.ID > curr.ID)
    OR (T.din < curr.din))
WHERE T.active = 1 AND T.deleted = 0
ORDER BY T.din DESC, T.ID ASC
LIMIT 1;

这篇关于SQL:在给定ID的where子句中查找下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！