query to select a specified number of rows
Imagine a simple query:
SELECT name, lastname FROM people
Let's imagine it returns 6 rows. What i need to do is to return a number of rows that ROWS Mod 4 would be equal to 0. So if the query would normally return 6 rows, it should return 2 more rows with NULL for name and lastname. It's easy to 开发者_运维知识库count the rows it would return and generate the necessary union selects, but i'm wondering if it's possible to do in sql. I know, sounds kinda stupid and you're probably thinking about telling me to find a different solution, but that's the easiest workaround for my problem ;]
for oracle:
SELECT name, lastname FROM people
UNION ALL
SELECT NULL, NULL FROM people
WHERE RowNum <= (Select mod(Count(*),4) from people)
for mssql: (>=2005)
SELECT name, lastname FROM people
UNION ALL
select null, null from
(
SELECT row_number() over(order by name) r FROM people
)
WHERE r <= (Select Count(*) % 4 from people)
精彩评论