SQL With Ties 用法

最新推荐文章于 2024-09-19 20:32:07 发布

louisit

最新推荐文章于 2024-09-19 20:32:07 发布

阅读量754

点赞数 1

分类专栏： MSSQL/数据库文章标签： sql sql server table 数据库 insert module

MSSQL/数据库专栏收录该内容

6 篇文章

订阅专栏

指定从基本结果集中返回附加的行，这些行包含与出现在 TOP n (PERCENT) 行最后的 ORDER BY 列中的值相同的值。如果指定了 ORDER BY 子句，则只能指定 TOP ...WITH

相当于下面实现的功能:
select top 0 * into #test from employee

declare @s_empid varchar(20)

declare c_getempid cursor for
select distinct emp_id
(
select top 50 * from employee order by emp_id
) as info
open c_getempid
fetch next from c_getempid into @s_empid
while @@fetch_status = 0
begin
insert #test
select top 50 * from employee where emp_id = @s_empid order by emp_id
fetch next from c_getempid into @s_empid
end
close c_getempid

select top 50 with ties * from employee order by emp_id
这句的意思是按emp_id从小到大的顺序取employee的前50条记录,如果第50条的emp_id重复,那么这个emp_id号的记录都取出来,这样取出来的记录就不止50条了

table1
   id   name
   1 100
   2 200
   3 300
   4 300
   5    400
select top 3 * from table1
结果： 
      1 100
      2 200
      3 300
select top 3 with ties * from table1
结果：
      1 100
      2 200
      3 300
      4 300

  
  
      
      
       
       declare @table table (field int)
insert into @table select 1
insert into @table select 2
insert into @table select 3
insert into @table select 3
insert into @table select 5
       
       -- 选取TOP   N   时,选出平手的所有记录  
       
       select   top   3   field   from   @table  
       
       --   得到的结果只有三个   1   2   3     
    
--而
select   top   3      WITH   TIES field from   @table   order by field
--TOP N WITH TIES 句は対応する ORDER BY 句が必要です。
--   得到的结果有四个 1 2   3   3

SQL Server 2005 has the functionality to select the top records but let’s say I want the same amount also will be loaded. For example, I’d like to select the top 5 currency rate records from a table, but if one of the records has the same value, don’t make it count as top 5. For illustrations you could see below example.

select top 5 * from batch where module = ‘CA’ order by curyrate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750

If you look at that one, you will see that 9900 was loaded 3 times, let’s see if I run by below scripts.

select top 5 with ties * from batch where module = ‘CA’ order by CuryRate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750
000451 9750
000475 9500
000456 9400

Could you see the difference?

下面的应用场景就可以考虑用with ties

StudentNo  Grade

1                 99

2                  100

3                  80

4                  99

5                  98

查出前三名的学生，结果集如下：

1    100

2    99

4    99

5    98

今天在园子里看了关于索引的一道经典面试题，讲了这样一个问题“从100万条记录中的得到成绩最高的记录”。看到这个题目，通常我们的做法是：

select top 1 * from student order by score desc

但是这样做你会发现，如果有几个人分数并列第一，这样就只能取到一个记录。用下面的代码的话，就可以正确地取出分数第一的所有记录：

select top 1 with ties * from student order by score desc

由于以前没有用过with ties ，看到这个比较新奇，故随后MSDN，Google，Baidu之。

WITH TIES

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。

注意：返回的记录关联顺序是任意的。ORDER BY 不影响此规则

来源：MSDN，http://msdn.microsoft.com/zh-cn/library/ms189463.aspx

MSDN中指出这些额外的返回行的参数值与TOP n（PERCENT）行中的最后一行的该参数值相同。这个地方该怎么理解呢？其实是如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n+1…n+k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n+1、…、n+k也返回。n+1、…、n+k就是额外的返回值。

举个例子，假设有如下记录：

studentID	courseName	score
09212744	数据库	90
09212745	数据库	90
09212746	数据库	90
09212750	数据库	85
09212719	数据库	84
09212720	数据库	80
09212742	数据库	80
09212751	数据库	75
09212755	数据库	74
09212740	数据库	70

select top 6 * from student order by score desc

将返回如下结果：

图1 不带with ties（注意最好一条记录）

select top 6 with ties * from student order by score desc

将返回如下结果：

图2 带with ties（多了第7条记录）

结果一目了然，不用多解释！

插曲：其实在这个过程中有个小插曲，我首先是Google “with ties” 搜到了这篇文章With Ties on SQL Server 2005。内容如下（翻译之后）：

SQL Server 2005有一个功能来选择top记录，但是我要说的是，我希望同样的数量将被加载。举例来说，我想从表中记录选择前5名的货币汇率，但是如果其中一个记录有相同的值，不要让算作前5名。通过下面的例子来证明。

select top 5 * from batch where module = ‘CA’ order by curyrate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750

如果你看结果，你会看到9900被加载了3次，让我们看看如果运行下面的脚步。

select top 5 with ties * from batch where module = ‘CA’ order by CuryRate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750
000451 9750
000475 9500
000456 9400

出处： http://www.cnblogs.com/skynet/

oracle中实现sql server的select top n with ties中的ties

设有表ta有2个字段,a和b，要取按b排序前五名的数据，但是如果第六名和第七名数值一样，那么这条数据也要，如果第一名的值并列5个，那么所有符合结果的b值只有一个，依次类推。

鉴于所有数值都有可能会重复，故写伪代码如下：

select * from ta where b < = (select max (b) mb from (select b from (select b from ta order by b) tb rownum < 6) tc)

1，按b排序取b
2，取6行
3，取最大值mb
4，把小于等于mb的数据都拿出来，
5，done 不知对否，空下来再测试:D

Popularity: 15%

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只有在指定 ORDER BY 子句之后，才能在 SELECT 语句中指定 TOP ...WITH TIES。

以上是SQL Server联机丛书上的说明，大家请注意这么几点：

1.返回额外的行，比如top 10,却有可能返回了大于10行记录

2.最后一行的该参数值相同

3.只有在指定 ORDER BY 子句之后，才能在 SELECT 语句中指定 TOP ...WITH TIES。否则会出错的。

请看示例代码：

USE AdventureWorks;
GO
-- Total Number of Rows in Table
SELECT COUNT(*) TotalRows
FROM Sales.SalesOrderDetail
GO
-- Total Count of Rows Grouped by ORDERQty
SELECT COUNT(*) Cnt, OrderQty
FROM Sales.SalesOrderDetail
GROUP BY OrderQty
ORDER BY OrderQty
GO
-- Example of Top 10 Records
SELECT TOP 10 *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty
GO
-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty
GO

多处转载就不注明出处。