SQL 有没有办法将选择查询的结果分成相等的两半?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2776006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to split the results of a select query into two equal halfs?
提问by Mats
I need a solution for a select query in Sql Server 2005.
我需要一个 Sql Server 2005 中选择查询的解决方案。
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:
我希望有一个查询返回两个 ResultSets,每个 ResultSets 都包含符合特定条件的所有记录的一半。我尝试将 TOP 50 PERCENT 与 Order By 结合使用,但如果表中的记录数是奇数,则两个结果集中都会显示一条记录。我不想在记录集上复制任何记录。例子:
I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now.
我有一个包含 TheID (PK) 和 TheValue 字段 (varchar(10)) 和 5 条记录的简单表。暂时跳过 where 子句。
SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc
results in the selected id's 1,2,3
结果是所选 ID 的 1,2,3
SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc
results in the selected id's 3,4,5
结果是选定的 id 的 3,4,5
3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.
3 是一个重复。在现实生活中,当然查询相当复杂,有大量的 where 子句和子查询。
回答by araqnid
SQL Server 2005 and similar:
SQL Server 2005 及类似版本:
select *, ntile(2) over(order by theid) as tile_nr from thetable
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:
ntile(n)
将输出分配到 n 个段中,每个段的大小都相同(当行数不能被 n 整除时,进行舍入或取整)。所以这会产生输出:
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
will return the top half, and similarly use x.tile_nr = 2
for the bottom half
将返回上半部分,同样x.tile_nr = 2
用于下半部分
回答by Mark Byers
You could use these two queries:
您可以使用这两个查询:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY TheID) AS rn FROM TheTable
) T1
WHERE rn % 2 = 0
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY TheID) AS rn FROM TheTable
) T1
WHERE rn % 2 = 1
回答by Thomas
If this is SQL Server 2000, then I'd be inclined to find the PK of the middle value like so:
如果这是 SQL Server 2000,那么我倾向于像这样找到中间值的 PK:
Declare @MiddleId int
Set @MiddleId = (
Select TOP 1 PK
From (
Select TOP 50 PERCENT PK
From Table
Order By TheId ASC
)
Order By TheId DESC
)
Select ...
From Table
Where TheId <= @MiddleId
Select ..
From Table
Where TheId > @MiddleId
With SQL Server 2005, I'd be inclined to do the same but you can use a CTE
使用 SQL Server 2005,我倾向于这样做,但您可以使用 CTE
;With NumProjects As
(
Select Id, ROW_NUMBER() OVER (ORDER BY TheId ASC ) As Num
From Table
)
Select @MiddleId = Id
From Table
Where Num = CEILING( (Select Count(*) From Table) / 2 )
回答by KM.
try this:
尝试这个:
DECLARE @CountOf int,@Top int,@Bottom int
SELECT @CountOf=COUNT(*) FROM YourTable
SET @Top=@CountOf/2
SET @Bottom=@CountOf-@Top
SELECT TOP (@Top) * FROM YourTable ORDER BY 1 asc --assumes column 1 is your PK
SELECT TOP (@Bottom) * FROM YourTable ORDER BY 1 desc --assumes column 1 is your PK
回答by Unbound
Here is another solution:
这是另一个解决方案:
You would need to use a temp table to hold the first 50% as below:
您需要使用临时表来保存前 50%,如下所示:
select top 50 percent *
into #YourTempTable
from TheTable
-- The below would give the first half
select * from #YourTempTable
-- The below woud give rest of the half
select * from TheTable where TheID not in (select TheID from #YourTempTable)
回答by Chagbert
This is the queryI found useful (after modifications of-course):
这是我发现有用的查询(当然修改后):
DECLARE @numberofitemsperpage INT DECLARE @numberofpages INT DECLARE @currentpage int
DECLARE @countRecords float SET @countRecords = (Select COUNT(*) From sz_hold_visitsData) -- the Excel can hold approximately ONE MILLION records at a time. if @countRecords >= 1000000 SET @numberofitemsperpage = 500000 ELSE IF @countRecords < 1000000 AND @countRecords >= 500000 SET @numberofitemsperpage = 250000 ELSE IF @countRecords < 500000 AND @countRecords >= 100000 SET @numberofitemsperpage = 50000 ELSE SET @numberofitemsperpage = 10000
DECLARE @numberofpages_deci float SET @numberofpages_deci = @countRecords / @numberofitemsperpage
SET @numberofpages = CEILING(@numberofpages_deci) Select @countRecords AS countRecords, @numberofitemsperpage AS numberofitemsperpage , @numberofpages_deci AS numberofpages_deci, @numberofpages AS numberofpagesFnl
SET @currentpage =0 WHILE @currentpage < @numberofpages BEGIN SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW >= @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage
IF @@ROWCOUNT = 0 BREAK SET @currentpage = @currentpage +1 END
DECLARE @numberofitemsperpage INT DECLARE @numberofpages INT DECLARE @currentpage int
DECLARE @countRecords float SET @countRecords = (Select COUNT(*) From sz_hold_visitsData) -- Excel 一次可以保存大约一百万条记录。if @countRecords >= 1000000 SET @numberofitemsperpage = 500000 ELSE IF @countRecords < 1000000 AND @countRecords >= 500000 SET @numberofitemsperpage = 250000 ELSE IF @countRecords00numbersperpages = 500000 ELSE @countRecords < 1000000 AND @countRecords >= 250000 ELSE IF @countRecords00numbers0of SET @countRecords < 1000000 项目0=0 SET @countRecords0ofitem0s0=0 5 SET @0Records 0 0 5个项目0 10000
声明@numberofpages_deci float SET @numberofpages_deci = @countRecords / @numberofitemsperpage
SET @numberofpages = CEILING(@numberofpages_deci) 选择@countRecords AS countRecords, @numberofitemsperpage AS numberofitemsperpage , @numberofpages_deci AS numberofpages_deci, @numberofpages AS numberofpagesFnl
SET @currentpage =0 WHILE @currentpage < @numberofpages BEGIN SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW >= @currentpage * @numberofitemsperpage +1 AND Row < = (@currentpage+1) * @numberofitemsperpage
IF @@ROWCOUNT = 0 BREAK SET @currentpage = @currentpage +1 END
In this extract, "sz_hold_visitsData" is a table in my database, whilst "person_ID" is a column therein. You can also further modify the script to output to file:
在此摘录中,“sz_hold_visitsData”是我数据库中的一个表,而“person_ID”是其中的一列。您还可以进一步修改脚本以输出到文件:
DECLARE @numberofitemsperpage INT DECLARE @numberofpages INT DECLARE @currentpage int
DECLARE @countRecords float SET @countRecords = (Select COUNT(*) From sz_hold_visitsData) -- the Excel can hold approximately ONE MILLION records at a time. if @countRecords >= 1000000 SET @numberofitemsperpage = 500000 ELSE IF @countRecords < 1000000 AND @countRecords >= 500000 SET @numberofitemsperpage = 250000 ELSE IF @countRecords < 500000 AND @countRecords >= 100000 SET @numberofitemsperpage = 50000 ELSE SET @numberofitemsperpage = 10000
DECLARE @numberofpages_deci float SET @numberofpages_deci = @countRecords / @numberofitemsperpage
SET @numberofpages = CEILING(@numberofpages_deci) Select @countRecords AS countRecords, @numberofitemsperpage AS numberofitemsperpage , @numberofpages_deci AS numberofpages_deci, @numberofpages AS numberofpagesFnl
DECLARE @sevrName nvarchar(50) SET @sevrName = '.\sql14' DECLARE @outputFile nvarchar(500)
SET @currentpage =0 WHILE @currentpage < @numberofpages BEGIN --SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW >= @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage SET @outputFile = 'C:\PSM\outVisits_' +convert(nvarchar(50), @currentpage) + '.csv' --Select @outputFile --TEST
DECLARE @cmd_ varchar(500) = 'sqlcmd -S ' + @sevrName + ' -E -Q "SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW >= '+ CONVERT(nvarchar(500),@currentpage * @numberofitemsperpage +1) +' AND Row <= ' + CONVERT(nvarchar(500),((@currentpage+1) * @numberofitemsperpage)) +'" -s "," -o ' +@outputFile +' ' -- "C:\PSM\outVisits.csv" ' EXEC xp_cmdshell @cmd_
IF @@ROWCOUNT = 0 BREAK SET @currentpage = @currentpage +1 END
DECLARE @numberofitemsperpage INT DECLARE @numberofpages INT DECLARE @currentpage int
DECLARE @countRecords float SET @countRecords = (Select COUNT(*) From sz_hold_visitsData) -- Excel 一次可以保存大约一百万条记录。if @countRecords >= 1000000 SET @numberofitemsperpage = 500000 ELSE IF @countRecords < 1000000 AND @countRecords >= 500000 SET @numberofitemsperpage = 250000 ELSE IF @countRecords00numbersperpages = 500000 ELSE @countRecords < 1000000 AND @countRecords >= 250000 ELSE IF @countRecords00numbers0of SET @countRecords < 1000000 项目0=0 SET @countRecords0ofitem0s0=0 5 SET @0Records 0 0 5个项目0 10000
声明@numberofpages_deci float SET @numberofpages_deci = @countRecords / @numberofitemsperpage
SET @numberofpages = CEILING(@numberofpages_deci) 选择@countRecords AS countRecords, @numberofitemsperpage AS numberofitemsperpage , @numberofpages_deci AS numberofpages_deci, @numberofpages AS numberofpagesFnl
声明@sevrName nvarchar(50) SET @sevrName = '.\sql14' 声明@outputFile nvarchar(500)
SET @currentpage =0 WHILE @currentpage < @numberofpages BEGIN --SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW >= @currentpage * @numberofitemsperpage +1 AND行 <= (@currentpage+1) * @numberofitemsperpage SET @outputFile = 'C:\PSM\outVisits_' +convert(nvarchar(50), @currentpage) + '.csv' --Select @outputFile --TEST
DECLARE @cmd_ varchar(500) = 'sqlcmd -S ' + @sevrName + ' -E -Q "SELECT a.* FROM (SELECT row_number() OVER (ORDER BY person_ID) AS ROW, * FROM sz_hold_visitsData) a WHERE ROW > = '+ CONVERT(nvarchar(500),@currentpage * @numberofitemsperpage +1) +' AND Row <= ' + CONVERT(nvarchar(500),((@currentpage+1) * @numberofitemsperpage)) +'" -s "," -o ' +@outputFile +' ' -- "C:\PSM\outVisits.csv" ' EXEC xp_cmdshell @cmd_
IF @@ROWCOUNT = 0 BREAK SET @currentpage = @currentpage +1 END
Hope helps.
希望有所帮助。