有没有一种方法可以将选择查询的结果分为两个相等的一半? [英] Is there a way to split the results of a select query into two equal halfs?

问题描述

我需要在Sql Server 2005中进行选择查询的解决方案.

I need a solution for a select query in Sql Server 2005.

我想查询一个返回两个ResultSet的查询，每个ResultSet恰好保存符合特定条件的所有记录的一半.我尝试将TOP 50 PERCENT与Order By一起使用，但是如果表中的记录数为奇数，则两个结果集中都会显示一条记录.我不想在记录集上重复任何记录.示例:

I'd like to have a query returning two ResultSets each of which holding exactly half of all records matching a certain criteria. I tried using TOP 50 PERCENT in conjunction with an Order By but if the number of records in the table is odd, one record will show up in both resultsets. I don't want to have any record duplicated over the recordsets. Example:

我有一个简单的表，其中包含TheID(PK)和TheValue字段(varchar(10))和5条记录.暂时跳过where子句.

I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now.

SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc

得出所选ID的1,2,3

results in the selected id's 1,2,3

SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc

得出所选ID的3、4、5

results in the selected id's 3,4,5

3是dup.当然，在现实生活中，查询非常复杂，其中包含大量的where子句和子查询.

3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.

推荐答案

SQL Server 2005和类似版本:

SQL Server 2005 and similar:

select *, ntile(2) over(order by theid) as tile_nr from thetable

ntile(n)将输出分配为n个段，每个段的大小相同(当行数不能被n整除时，取整或取整).这样就产生了输出:

ntile(n) allocates the output into n segments, each of the same size (give or take rounding when the number of rows isn't divisible by n). So this produces the output:

1 | value1 | 1
2 | value2 | 1
3 | value3 | 1
4 | value4 | 2
5 | value5 | 2

如果只需要上半部分或下半部分，则需要将其放入子查询中，例如:

If you just want the top or bottom half, you need to put this into a subquery, e.g.:

select theid, thevalue from (
  select theid, thevalue, ntile(2) over(order by theid) as tile_nr from thetable
) x
where x.tile_nr = 1

将返回上半部，类似地，将x.tile_nr = 2用于下半部

will return the top half, and similarly use x.tile_nr = 2 for the bottom half

这篇关于有没有一种方法可以将选择查询的结果分为两个相等的一半?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！