php - 初始化然后增加记录集的行号，如果

Question

仅当 orders_status 不等于 pulled 时，我才尝试使用一个 mysql select 查询 来计数并递增变量/不存在的列> 或拉动，否则它是空。我打算在执行标志 if row_number=400

的 php 脚本中使用计数

$orders_query_raw = "SELECT
                    o.orders_id,
                    o.customers_name,
                    o.payment_method,
                    o.date_purchased,
                    o.delivery_date,
                    o.delivery_time_slotid,
                    o.last_modified,
                    o.currency,
                    o.currency_value,
                    s.orders_status_name,
                    ot.text as order_total
                FROM " . TABLE_ORDERS . " o
                LEFT JOIN " . TABLE_ORDERS_TOTAL . " ot ON (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s
                WHERE o.orders_status = s.orders_status_id
                  AND s.language_id = '" . (int)$languages_id . "'
                  AND s.orders_status_id = '" . (int)$status . "'
                  AND ot.class = 'ot_total'
                ORDER BY o.orders_id DESC";

初始结果:

orders_id | orders_status 
     34a  |  pulling          
     45a  |  pending      
     45u  |  pulled       
     36c  |  payment_due  

返回期望的结果

orders_id | orders_status | rownumber
     34a  |  pulling      |   
     45a  |  pending      |   1
     45u  |  pulled       |   
     36c  |  payment_due  |   2

Answer 1

您可以使用变量根据需要增加值。您的查询有点复杂，但想法如下:

select . . .,
       (case when order_status in ('pulled', 'pulling') then NULL
             else @rn := @rn + 1
        end) as rownumber
from . . . cross join
     (select @rn := 0) vars;