Home Database Mysql Tutorial 解析SQLServer2005的Top功能

解析SQLServer2005的Top功能

Jun 07, 2016 pm 04:18 PM
Function parse

所有人都知道select top 的用法,但很多人还不知道update top 和 delete top 怎么用。以往的做法是set rowcount来指定,其实SQL2005中对于Top语句的增强除了参数化之外还包括对update和delete的支持,但可惜的是还不支持自定义 的order by列。如果要自定义派

所有人都知道select top 的用法,但很多人还不知道update top 和 delete top 怎么用。以往的做法是set rowcount来指定,其实SQL2005中对于Top语句的增强除了参数化之外还包括对update和delete的支持,但可惜的是还不支持自定义 的order by列。如果要自定义派序列可以借助CTE.对于CTE的任何更改都会影响到原始表。
我们看下面的测试代码。

复制代码 代码如下:


set nocount on
use tempdb
go
if (object_id ('tb' ) is not null )
drop table tb
go
create table tb (id int identity (1 , 1 ), name varchar (10 ), tag int default 0 )
insert into tb (name ) select 'a'
insert into tb (name ) select 'b'
insert into tb (name ) select 'c'
insert into tb (name ) select 'd'
insert into tb (name ) select 'e'
/*--更新前两行
id name tag
----------- ---------- -----------
1 a 1
2 b 1
3 c 0
4 d 0
5 e 0
*/
update top (2 ) tb set tag = 1
select * from tb
/*--更新后两行
id name tag
----------- ---------- -----------
1 a 1
2 b 1
3 c 0
4 d 1
5 e 1
*/
;with t as
(
select top (2 ) * from tb order by id desc
)
update t set tag = 1
select * from tb
/*--删除前两行
id name tag
----------- ---------- -----------
3 c 0
4 d 1
5 e 1
*/
delete top (2 ) from tb
select * from tb
/*--删除后两行
id name tag
----------- ---------- -----------
3 c 0
*/
;with t as
(
select top (2 ) * from tb order by id desc
)
delete from t
select * from tb
set nocount off


我会在下一篇文章中介绍一个应用,就是很多人关心的如何独占查询(就是一条数据只被一个终端select到)。
如果你感兴趣的话可以自己先思考一下,我给一个提示:
SQLServer2005有一个关键字Output,,它可以将更改和插入的数据输出,我们配合update top就可以模拟出来一个相对高效的独占查询的事物。此功能适合用在并行的任务处理或者消费中。

 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

The difference between vivox100s and x100: performance comparison and function analysis The difference between vivox100s and x100: performance comparison and function analysis Mar 23, 2024 pm 10:27 PM

Both vivox100s and x100 mobile phones are representative models in vivo's mobile phone product line. They respectively represent vivo's high-end technology level in different time periods. Therefore, the two mobile phones have certain differences in design, performance and functions. This article will conduct a detailed comparison between these two mobile phones in terms of performance comparison and function analysis to help consumers better choose the mobile phone that suits them. First, let’s look at the performance comparison between vivox100s and x100. vivox100s is equipped with the latest

Detailed explanation of Oracle error 3114: How to solve it quickly Detailed explanation of Oracle error 3114: How to solve it quickly Mar 08, 2024 pm 02:42 PM

Detailed explanation of Oracle error 3114: How to solve it quickly, specific code examples are needed. During the development and management of Oracle database, we often encounter various errors, among which error 3114 is a relatively common problem. Error 3114 usually indicates a problem with the database connection, which may be caused by network failure, database service stop, or incorrect connection string settings. This article will explain in detail the cause of error 3114 and how to quickly solve this problem, and attach the specific code

What exactly is self-media? What are its main features and functions? What exactly is self-media? What are its main features and functions? Mar 21, 2024 pm 08:21 PM

With the rapid development of the Internet, the concept of self-media has become deeply rooted in people's hearts. So, what exactly is self-media? What are its main features and functions? Next, we will explore these issues one by one. 1. What exactly is self-media? We-media, as the name suggests, means you are the media. It refers to an information carrier through which individuals or teams can independently create, edit, publish and disseminate content through the Internet platform. Different from traditional media, such as newspapers, television, radio, etc., self-media is more interactive and personalized, allowing everyone to become a producer and disseminator of information. 2. What are the main features and functions of self-media? 1. Low threshold: The rise of self-media has lowered the threshold for entering the media industry. Cumbersome equipment and professional teams are no longer needed.

PHP Tips: Quickly Implement Return to Previous Page Function PHP Tips: Quickly Implement Return to Previous Page Function Mar 09, 2024 am 08:21 AM

PHP Tips: Quickly implement the function of returning to the previous page. In web development, we often encounter the need to implement the function of returning to the previous page. Such operations can improve the user experience and make it easier for users to navigate between web pages. In PHP, we can achieve this function through some simple code. This article will introduce how to quickly implement the function of returning to the previous page and provide specific PHP code examples. In PHP, we can use $_SERVER['HTTP_REFERER'] to get the URL of the previous page

What are the functions of Xiaohongshu account management software? How to operate a Xiaohongshu account? What are the functions of Xiaohongshu account management software? How to operate a Xiaohongshu account? Mar 21, 2024 pm 04:16 PM

As Xiaohongshu becomes popular among young people, more and more people are beginning to use this platform to share various aspects of their experiences and life insights. How to effectively manage multiple Xiaohongshu accounts has become a key issue. In this article, we will discuss some of the features of Xiaohongshu account management software and explore how to better manage your Xiaohongshu account. As social media grows, many people find themselves needing to manage multiple social accounts. This is also a challenge for Xiaohongshu users. Some Xiaohongshu account management software can help users manage multiple accounts more easily, including automatic content publishing, scheduled publishing, data analysis and other functions. Through these tools, users can manage their accounts more efficiently and increase their account exposure and attention. In addition, Xiaohongshu account management software has

Analysis of new features of Win11: How to skip logging in to Microsoft account Analysis of new features of Win11: How to skip logging in to Microsoft account Mar 27, 2024 pm 05:24 PM

Analysis of new features of Win11: How to skip logging in to a Microsoft account. With the release of Windows 11, many users have found that it brings more convenience and new features. However, some users may not like having their system tied to a Microsoft account and wish to skip this step. This article will introduce some methods to help users skip logging in to a Microsoft account in Windows 11 and achieve a more private and autonomous experience. First, let’s understand why some users are reluctant to log in to their Microsoft account. On the one hand, some users worry that they

What is PHP used for? Explore the role and functions of PHP What is PHP used for? Explore the role and functions of PHP Mar 24, 2024 am 11:39 AM

PHP is a server-side scripting language widely used in web development. Its main function is to generate dynamic web content. When combined with HTML, it can create rich and colorful web pages. PHP is powerful. It can perform various database operations, file operations, form processing and other tasks, providing powerful interactivity and functionality for websites. In the following articles, we will further explore the role and functions of PHP, with detailed code examples. First, let’s take a look at a common use of PHP: dynamic web page generation: P

Understanding VSCode: What is this tool used for? Understanding VSCode: What is this tool used for? Mar 25, 2024 pm 03:06 PM

"Understanding VSCode: What is this tool used for?" 》As a programmer, whether you are a beginner or an experienced developer, you cannot do without the use of code editing tools. Among many editing tools, Visual Studio Code (VSCode for short) is very popular among developers as an open source, lightweight, and powerful code editor. So, what exactly is VSCode used for? This article will delve into the functions and uses of VSCode and provide specific code examples to help readers

See all articles