c# - MySQL返回设置字段

Question

嗨，我有一个 MySQL 查询，如下所示，它是从 C# 调用的，用于 jQuery DataTables 的服务器端分页

SELECT * FROM (SELECT (@rn := @rn + 1) AS RowNumber,item_id,description
FROM
    (SELECT (SELECT count(item.item_id)
            FROM
                item) AS TotalRows
        , ( SELECT  count(item_id) FROM item  ) AS TotalDisplayRows ,item_id,description
    FROM
        item  ) RawResults
    ) Results 
    WHERE RowNumber BETWEEN 1 AND 10

这将返回 RowNumber、item_id、说明

我还需要返回 TotalRows 和 TotalDisplayRows。不知道该怎么做。请提供任何帮助。谢谢

这是在 SQL 中运行的查询

SELECT * FROM
(SELECT row_number() OVER (ORDER BY  NO  asc) AS RowNumber , *
    FROM
        (SELECT (SELECT count(item.item_id)
                FROM
                    item) AS TotalRows 
            , ( SELECT  count(item_id) FROM iq_item  ) AS TotalDisplayRows ,*
        FROM
            item ) RawResults) Results WHERE RowNumber BETWEEN 1 AND 10

Answer 1

解密您的查询非常复杂......

这是你想要的吗？

// returns first 10 results
SELECT SQL_CALC_FOUND_ROWS item_id, description FROM item LIMIT 0, 10;
// get total number of elements
SELECT FOUND_ROWS() AS TotalRows;

您的行号必须使用分页索引和返回的行位置来计算，而 TotalDisplayRows 是返回的行数(如果我了解您打算做什么的话)