如何获得总是n的倍数的SQL查询？ [英] How can I get a sql query that always would be multiple of n?

问题描述

我需要获得一个查询结果，该查询结果将显示为确定数字的倍数（在我的情况下为10），与实际行数无关（实际上是为了解决jasper问题）。

I need to obtain a query result that would be showing in multiples of a determined number (10 in my case), independent of the real quantity of rows (actually to solve a jasper problem).

例如，在此链接中，我构建了一个示例模式： http://sqlfiddle.com/#!3/c3dba/1/0

For example, in this link, I build a example schema: http://sqlfiddle.com/#!3/c3dba/1/0

我希望结果如下：

1    Item 1     1    10
2    Item 2     2    30
3    Item 3     5    15
4    Item 4     2    10
null null null  null null
null null null  null null
null null null  null null
null null null  null null
null null null  null null
null null null  null null

我找到了这个解释，但是在SQLServer和我无法转换： http：//community.jaspersoft .COM / questions / 514706 / need-table-fixed-size-detail-block

I have found this explanation, but doesn't work in SQLServer and I can't convert: http://community.jaspersoft.com/questions/514706/need-table-fixed-size-detail-block

推荐答案

另一种选择是使用a 递归CTE 以获取预定数量的行，然后使用嵌套CTE 构造来联合来自使用原始表进行递归CTE，最后使用 TOP 子句获得所需的行数。

Another option is to use a recursive CTE to get the pre-determined number of rows, then use a nested CTE construct to union rows from the recursive CTE with the original table and finally use a TOP clause to get the desired number of rows.

DECLARE @n INT = 10

;WITH Nulls AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 AS i
    FROM Nulls
    WHERE i < @n
),
itemsWithNulls AS
(
   SELECT * FROM itens 
   UNION ALL 
   SELECT NULL, NULL, NULL, NULL FROM Nulls    
)
SELECT TOP (@n) *
FROM itemsWithNulls

编辑：

通过更仔细地阅读要求，OP实际上希望返回的总行数是10的倍数。例如。如果表 itens 有4行，那么应返回10行，如果 itens 有12行，则应返回20行等等。

By reading the requirements more carefully, the OP actually wants the total number of rows returned to be a multiple of 10. E.g. if table itens has 4 rows then 10 rows should be returned, if itens has 12 rows then 20 rows should be return, etc.

在这种情况下， @n 应该设置为：

In this case @n should be set to:

DECLARE @n INT = ((SELECT COUNT(*) FROM itens) / 10 + 1) * 10

我们实际上可以使用嵌套的CTE在单个sql语句中包含所有内容：

We can actually fit everything inside a single sql statement with the use of nested CTEs:

;WITH NumberOfRows AS (
   SELECT n = ((SELECT COUNT(*) FROM itens) / 10 + 1) * 10
), Nulls AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 AS i
    FROM Nulls
    WHERE i < (SELECT n FROM NumberOfRows)
),
itemsWithNulls AS
(
   SELECT * FROM itens 
   UNION ALL 
   SELECT NULL, NULL, NULL, NULL FROM Nulls    
)
SELECT TOP (SELECT n FROM NumberOfRows) *
FROM itemsWithNulls

SQL小提琴这里

这篇关于如何获得总是n的倍数的SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！