计算没有限制的总行数

Currently its working but I want the xml to contain the total number of rows without the limit.

目前它的工作,但我希望xml包含没有限制的总行数。

SET @query_result = (SELECT ID,Title 
        FROM
        ( 
            SELECT items.id AS "ID",items.title AS "Title" ,
ROW_NUMBER() OVER(ORDER BY date_added DESC) AS RowNum
                FROM [cars] 
                JOIN [items] ON items.id=cars.item_id
                WHERE
                rejected = 0 


    )AS MyDerivedTable
        WHERE
        MyDerivedTable.RowNum BETWEEN (@page-1)*2+1 AND (@page*2) 
        FOR XML PATH('car'),ROOT('items')
)

This returns

<items>
  <car>
    <ID>37</ID>
    <Title>Used 2004 Chevrolet Corvette Convertible</Title>
  </car>
</items>

I want

<items>
      <car>
        <ID>37</ID>
        <Title>Used 2004 Chevrolet Corvette Convertible</Title>
        <Count>6</Count>
      </car>
    </items>

While Count is not the number of rows returned but the total number of rows that matched the query .Or if my problem is too hard for anybody to understand ,I am looking for MSSQL alternative for FOUND_ROWS(); This question @SQL Count total number of rows whilst using LIMIT is trying to answer the same thing but I want a solution is MSSQL.

虽然Count不是返回的行数,但是与查询匹配的行总数。或者如果我的问题太难以让任何人理解,我正在寻找FOUND_ROWS()的MSSQL替代方法。这个问题@SQL Count使用LIMIT的总行数试图回答同样的事情,但我想要一个解决方案是MSSQL。

1 个解决方案

#1

OK, I hope I understand now what you're trying to do. I believe you have to "turn around" your select and use a CTE (Common Table Expression) instead of a subselect to achieve this.

好的,我希望我现在明白你要做的事情。我相信你必须“转身”你的选择并使用CTE(通用表格表达式)而不是子选择来实现这一点。

Try this:

DECLARE @queryResult VARCHAR(MAX)

;WITH MyDerivedTable AS
(
    SELECT 
       items.id,
       items.title,
       ROW_NUMBER() OVER(ORDER BY date_added DESC) AS RowNum
    FROM dbo.cars
    INNER JOIN dbo.items ON items.id = cars.item_id
    WHERE rejected = 0 
) 
SELECT
   @queryResult = 
   (SELECT
       ID, Title,
       (SELECT MAX(RowNum) FROM MyDerivedTable) AS 'Count'
    FROM
       MyDerivedTable
    WHERE
        RowNum BETWEEN (@page-1)*2+1 AND (@page*2) 
    FOR XML PATH('car'),ROOT('items')
   )

SELECT @queryResult

That should output your ID, Title and the Count (which is the max of the RowNum) for each car entry.

这应该输出您的ID,标题和每个汽车入口的计数(这是RowNum的最大值)。

#1