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)
blank

相关推荐

  • FPGA和CPLD对比与入门

    FPGA和CPLD对比与入门入门介绍:1、EMP240使用很广泛了,8元一片。EMP240顾名思义具有240个宏单元,或者说240个触发器,或者理解成240个bit的存储单元。2、仿真分2步,写逻辑时用QUARTUS自带的仿真;逻辑写完后,最好用modelsim专门仿真。3、如果你需要100个逻辑单元,实际用的可能是120个,因此要留出20%的余量。4、一个小技巧,针对EPM240和570来说,常用的封装T

  • Try out the latest C++ compiler toolset without waiting for the next update of Visual Studio

    Updated22/Apr/2016:TheNuGet packageis nowbeinguploadeddaily.Thepayloaddoesn’tch

    2021年12月23日
  • 群辉 Docker-x64-17.05.0-0400.spk,用来群晖洗白,安装ddsm使用

    群辉 Docker-x64-17.05.0-0400.spk,用来群晖洗白,安装ddsm使用链接:https://pan.baidu.com/s/1_J5uQTBmEolNT8T_9P6gLQ提取码:jyq8复制这段内容后打开百度网盘手机App,操作更方便哦

  • 神经网络的反向传播算法推导

    神经网络的反向传播算法推导有了上一篇神经网络的反向传播算法推导—前期知识准备做铺垫,下一步来看看反向传播算法具体的推导过程。一、定义机器学习中常说的两个函数:损失函数(lossfunction):是定义在单个样本上的,算的是一个样本的值和预测值的误差,记为C(Θ);代价函数(costfunction):是定义在整个训练集上的,是所有样本误差的平均,也就是损失函数的平均,记为J(Θ);假设函数:二、神经网络结构图以三层神经网络为例:…

  • 分辨率,像素,像素密度易懂

    分辨率,像素,像素密度易懂分辨率是什么?一般会说这个屏幕的分辨率是1920*1080,这就说明纵向和横向上有1920个和1080个像素点;像素点是什么?一个像素点就是一个色彩块,没有实际的物理尺寸;什么是屏幕像素密度?一英寸长的一条线上理论上会有多少个像素点;例如:一个手机长边有1920个像素点,短边有1080个像素点,屏幕大小(对角线的物理大小)是5.2英寸的,那么屏幕密度是怎么计…

  • 【云原生 • Docker】镜像的迁移与备份、Dockerflie 使用方法

    【云原生 • Docker】镜像的迁移与备份、Dockerflie 使用方法Docker中镜像的迁移与备份,Dockerfile的使用方法(附案例)

发表回复

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

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