T-SQL基础–TOP

T-SQL基础–TOP

 

理解TOP子句

 

众所周知,TOP子句可以通过控制返回行的数量来影响查询。

我们知道TOP子句能很容易的满足返回指定行数的子集,接下来有一些例子来展示什么情况下使用TOP子句来返回一个结果集;

  • 你打算返回的恰好是一个记录的子集来验证你代码;
  • 你仅仅需要确定至少一行数据满足特定的Where条件;
  • 你的业务需求指示你仅仅返回前面的几行数据,基于一个特定的Where条件;

为了去解释TOP子句的如何工作,我将列举几个实例,使你能够更容易理解并观察使用TOP子句的影响返回值得细微差别。

TOP 的语法

语法很简单,可以将TOP加在任何如 SELECT、DELETE, INSERT, or UPDATE 的语句中:

TOP (expression) [PERCENT]
[WITH TIES]

expression”的值是一个数字,,如果PERCENT的可选项被启用则数字将被转换成一个float 类型,否则姜维BIGINT类型。可以指定数字也可以使用局部变量。

可选项WITH TIES ,用来包含具有系统值得数据,需要注意的是该选项支队带有Order by的子句有效。

举例说明:

现有数据:

image

简单实例1

-- 找到两个SalesAgent 根据SalesAmount倒序。先排序在选出前两个,如果没有Order by 则此数据会随即返回(没有主键)
 
SELECT TOP(2) SalesAgent, Region, SalesAmount
FROM dbo.HectorSales
ORDER BY SalesAmount DESC;

运行结果:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
Nick Potts                     East       9834212.87
Mary Johnson                   West       8723412.61

 

使用百分比的查询

If you want to return a percentage of the top records in a set then you need to use the TOP clause with the PERCENT option. To demonstrate using the PERCENT option look at Listing 3.

-- 查询前百分之50的数据,按照SalesAmount
SELECT TOP(50) PERCENT SalesAgent, Region, SalesAmount
FROM dbo.HectorSales
ORDER BY SalesAmount DESC;

查询结果:

SalesAgent                     Region     SalesAmount
----------------------------- ---------- ---------------------------
Nick Potts                     East       9834212.87
Mary Johnson                   West       8723412.61
Sam Holder                     East       8723412.61
Stan Morris                    East       4562341.67
Lori Morin                     East       2000111.67

使用变量的TOP查询

–不带百分比的查询

DECLARE @Number INT = 2;
SELECT TOP(@Number) SalesAgent, Region, SalesAmount
FROM dbo.HectorSales
ORDER BY SalesAmount DESC;

-- 带百分比的
SET @Number = 50;
SELECT TOP(@Number) PERCENT SalesAgent, Region, SalesAmount
FROM dbo.HectorSales
ORDER BY SalesAmount DESC;

使用 WITH TIES 可选项

按照值进行排序,如果有相同的则一并显示出来

-- 找出SalesAmount 最大的前两个。实际是3个
SELECT TOP(2) WITH TIES SalesAgent, Region, SalesAmount
FROM dbo.HectorSales
ORDER BY SalesAmount DESC;

查询结果:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
Nick Potts                     East       9834212.87
Mary Johnson                   West       8723412.61
Sam Holder                     East       8723412.61

正如我们看到的,返回了3行而不是2行,因为第三行和第二行的值是相同的。

使用TOP子句实现更新

 

如何使用TOP子句限制更新的行数,如下:

UPDATE TOP (2) dbo.HectorSales
SET SalesAmount = 100000.00
FROM dbo.HectorSales
WHERE Region = ‘West’

更新后查询Region = 'West'的结果

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
John Smith                     West       100000.00
Mary Johnson                   West       100000.00
Doris Bean                     West       2000111.67
Martin Derrick                 West       120834.81
Don Olson                      West       508921.48

当然也可以使用其他不同的方式来更新,先查询出前2的数据,然后将符合前2的数据进行更新,如下:

UPDATE dbo.HectorSales 
SET SalesAmount = 6666666.00
FROM
(SELECT TOP(2) SalesAgent FROM dbo.HectorSales
WHERE Region = ‘West’
ORDER BY SalesAmount DESC) TS
WHERE dbo.HectorSales.SalesAgent = TS.SalesAgent

更新后的结果:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
John Smith                     West       100000.00
Mary Johnson                   West       100000.00
Doris Bean                     West       6666666.00
Martin Derrick                 West       120834.81
Don Olson                      West       6666666.00

使用TOP完成Insert 语句

例如我打算将SalesAmount 最多的两个SalesAgent 插入到表dbo.TopTwoSales 的agent 里面。

INSERT TOP(2) INTO dbo.TopTwoHectorSales
SELECT * FROM dbo.HectorSales
ORDER BY SalesAmount DESC;
SELECT * FROM dbo.TopTwoHectorSales;

结果集如下:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
John Smith                     West       100000.00
Mary Johnson                   West       100000.00

通过查询结果我们发现插入的两行,并不是SalesAmount 最大的两行,因为我将TOP放在了Insert 后面,SQLServer 认为从子结果集中的前两行,这样的话实际上子结果集是随即的。为了纠正之前的问题,我这样写:

INSERT INTO dbo.TopTwoHectorSales
SELECT TOP(2) * FROM dbo.HectorSales
ORDER BY SalesAmount DESC;
SELECT * FROM dbo.TopTwoHectorSales;

改正后的结果:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
Nick Potts                     East       9834212.87
Sam Holder                     East       8723412.61

使用TOP完成DELETE语句

BEGIN TRANSACTION;
DELETE FROM dbo.HectorSales
WHERE SalesAgent in (SELECT TOP(2) SalesAgent FROM dbo.HectorSales
                     ORDER by SalesAmount ASC);
SELECT * FROM dbo.HectorSales;
ROLLBACK;

结果如下:

SalesAgent                     Region     SalesAmount
------------------------------ ---------- --------------------------
John Smith                     West       100000.00
Doris Bean                     West       6666666.00
Martin Derrick                 West       120834.81
Don Olson                      West       6666666.00
Sam Holder                     East       8723412.61
Nick Potts                     East       9834212.87
Lori Morin                     East       2000111.67
Stan Morris                    East       4562341.67

Report 12: Rows inserted into dbo.TopTwoHectorSales table using ORDER BY

当然如果想包含相同的值,使用WITH TIES

DELETE FROM dbo.HectorSales
WHERE SalesAgent in (SELECT TOP(2) WITH TIES SalesAgent FROM dbo.HectorSales
                     ORDER by SalesAmount ASC);
SELECT * FROM dbo.HectorSales;

与上一个相比,这个代码将会多删除掉一个SalesAmount 1000000.00数据

谨慎使用TOP关键在UNION、EXCEPT和INTERSECT语句中

创建一个表,插入初始数据。

CREATE TABLE dbo.Sales (
AgentName varchar(30),
Region varchar(10),
SalesAmount decimal(10,2));
INSERT INTO dbo.Sales VALUES
    (‘John Smith’, ‘West’,1012302.01),
    (‘Mary Johnson’, ‘West’,2453202.89),
    (‘Doris Bean’, ‘West’,99001.43),
    (‘Sam Holder’, ‘East’,8723412.61),
    (‘Nick Potts’, ‘East’,9834212.87),
    (‘Jason Thomas’, ‘East’,13424.51);

将东西部最高的SalesAmount 筛选出来并联

SELECT AgentName, Region, SalesAmount FROM
(
SELECT TOP(1) AgentName, Region, SalesAmount 
	FROM dbo.Sales
	WHERE Region = 'East'
	ORDER BY SalesAmount DESC ) East
UNION  
SELECT AgentName, Region, SalesAmount FROM
(
SELECT TOP(1) AgentName, Region, SalesAmount
	FROM dbo.Sales
	WHERE Region = 'West'
	ORDER BY SalesAmount DESC ) West
ORDER BY SalesAmount DESC;

总结

 

TOP关键字,让我们可以返回指定行数的数据,也能指定行数或者百分比的数据。为了确保结果集的一致性,一定要保证使用ORDER BY,假如你没有使用则将返回随机的指定行数数据。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119732.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Idea激活码永久有效Idea2021.2.3激活码教程-持续更新,一步到位「建议收藏」

    Idea激活码永久有效Idea2021.2.3激活码教程-持续更新,一步到位「建议收藏」Idea激活码永久有效2021.2.3激活码教程-Windows版永久激活-持续更新,Idea激活码2021.2.3成功激活

  • Linux 查看环境变量_linux修改jdk环境变量

    Linux 查看环境变量_linux修改jdk环境变量Linux的变量种类按变量的生存周期来划分,Linux变量可分为两类:1永久的:需要修改配置文件,变量永久生效。2临时的:使用export命令声明即可,变量在关闭shell时失效。设置变量的三种方法1在/etc/profile文件中添加变量【对所有用户生效(永久的)】用VI在文件/etc/profile文件中增加变量,该变量将会对Linux下所有用户有效,并且是“永久的”。例如:编辑/etc…

  • java如何实现分页查询_java分页计算页数

    java如何实现分页查询_java分页计算页数思路,假设数据库里的某张表的数据的条数是185条,我要实现的是一页显示10条,也就是说,展示完所有的数据需要19页,每一页的号是,1-10;11-20,22-31;第一页是a,a+9,第二页就是(a+9)+1,((a+9)+1)+9….一直到(b+9)>185;packagecn.com.servlet;importjava.io.IOException;import…

  • 我是如何学习Android源码的

    我是如何学习Android源码的一连很长时间(从2017/06/09到2017/06/26)都在研究蓝牙,而且这只是文章发表的时间,再加上组织文章和研究的时间,不知不觉中就坚持这么久………,也写了蓝牙的三篇博客,而且篇幅很长如下:在这里就不放文章链接了,有需要的在我的文章中搜索即可,现在,是时候放空一下,给自己充充电了,来张美景,欣赏一下北京的晚霞。言归正传,先放上Android源码连接地址Android源码,至于研究源码的好处,

  • Swift教程之typealias代替OC的typedef[通俗易懂]

    Swift教程之typealias代替OC的typedef

  • idea安装mybatis插件_myeclipse安装插件

    idea安装mybatis插件_myeclipse安装插件选择Help->Installfromsite…在弹出的对话框中点击右上角的Add按钮Name:mybatisLocation:https://dl.bintray.com/mybatis/mybatis-generator

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号