Home Database Mysql Tutorial MSSQL 多字段根据范围求最大值实现方法

MSSQL 多字段根据范围求最大值实现方法

Jun 07, 2016 pm 05:59 PM
mssql maximum value

MSSQL 多字段根据范围求最大值实现语句,大家可以参考下

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-21 15:08:41

declare @T table([Col1] int,[Col2] int,[Col3] int,[Col4] int,[Col5] int,[Col6] int,[Col7] int)
Insert @T
select 1,10,20,30,40,50,60 union all
select 2,60,30,45,20,52,85 union all
select 3,87,56,65,41,14,21
--方法1
select [col1],
max([col2])maxcol
from
(select [col1],[col2] from @t
union all
select [col1],[col3] from @t
union all
select [col1],[col4] from @t
union all
select [col1],[col5] from @t
union all
select [col1],[col6] from @t
union all
select [col1],[col7] from @t
)T
where [col2] between 20 and 60 --條件限制
group by [col1]
/*
col1 maxcol
----------- -----------
1 60
2 60
3 56

(3 個資料列受到影響)

*/
--方法2
select [col1],
(select max([col2])from
(
select [col2]
union all select [col3]
union all select [col4]
union all select [col5]
union all select [col6]
union all select [col7]
)T
where [col2] between 20 and 60) as maxcol --指定查詢範圍
from @t
/*
(3 個資料列受到影響)
col1 maxcol
----------- -----------
1 60
2 60
3 56
*/

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)

Use math.Max ​​function to get the maximum value in a set of numbers Use math.Max ​​function to get the maximum value in a set of numbers Jul 24, 2023 pm 01:24 PM

Use the math.Max ​​function to obtain the maximum value in a set of numbers. In mathematics and programming, it is often necessary to find the maximum value in a set of numbers. In Go language, we can use the Max function in the math package to achieve this function. This article will introduce how to use the math.Max ​​function to obtain the maximum value in a set of numbers, and provide corresponding code examples. First, we need to import the math package. In the Go language, you can use the import keyword to import a package, as shown below: import"mat

How to connect php to mssql database How to connect php to mssql database Oct 23, 2023 pm 12:02 PM

Methods for php to connect to mssql database include using PHP's MSSQL extension, using PDO, etc. Detailed introduction: 1. Use PHP's MSSQL extension method to ensure that PHP has the MSSQL extension installed. You can check whether the mssql extension is enabled in the PHP configuration file (php.ini); 2. Use the PDO method to ensure that PHP has the PDO extension installed. You can check whether the pdo_sqlsrv extension is enabled in the PHP configuration file (php.ini).

Detailed guide to install PHP and configure MSSQL connection on Ubuntu Detailed guide to install PHP and configure MSSQL connection on Ubuntu Feb 29, 2024 am 11:15 AM

Ubuntu is a popular open source operating system commonly used to run servers. Installing PHP and configuring MSSQL connections on Ubuntu is one of the operations that many developers and system administrators often need to do. This article will provide readers with a detailed guide, including the steps to install PHP, set up Apache, install MSSQLServer, etc., and attach specific code examples. Step 1: Install PHP and related extensions First, we need to install PHP and related extensions to support PHP connections

Detailed steps to install PHP to support MSSQL database in Ubuntu environment Detailed steps to install PHP to support MSSQL database in Ubuntu environment Feb 29, 2024 am 10:39 AM

Detailed steps for installing PHP to support MSSQL database in Ubuntu environment. When developing web applications, you often encounter situations where you need to connect to the Microsoft SQL Server (MSSQL) database. In the Ubuntu environment, to connect PHP to the MSSQL database, you need to install relevant software and configure appropriate settings. Next, we will introduce in detail the steps to install PHP to support MSSQL database in Ubuntu environment and provide specific code.

Complete tutorial on installing PHP and connecting to MSSQL database under Ubuntu Complete tutorial on installing PHP and connecting to MSSQL database under Ubuntu Feb 29, 2024 am 11:18 AM

Installing PHP and connecting to MSSQL database under the Ubuntu operating system is one of the skills that many developers and system administrators need to master. This article will provide a detailed tutorial, including installing PHP, installing the MSSQL server driver, configuring PHP to connect to the MSSQL database, and providing corresponding code examples. Part One: Install PHP First, we need to install PHP and related extensions to be able to connect to the MSSQL database. Enter the following command in the terminal to install PHP and necessary extensions

Get the maximum value in a sequence or set using Python's max() function Get the maximum value in a sequence or set using Python's max() function Aug 22, 2023 pm 02:10 PM

Use Python's max() function to get the maximum value in a sequence or set. In Python programming, we often need to find the largest element from a sequence or set. Python provides a built-in function max(), which can implement this function very conveniently. The max() function can accept any iterable object as a parameter, including lists, tuples, sets, etc. It returns the largest element in the passed object. The following is the basic syntax of the max() function: max(iterable[,def

In C++, remove one bit of a binary number to obtain the maximum value In C++, remove one bit of a binary number to obtain the maximum value Sep 17, 2023 pm 03:53 PM

Discuss a problem given a binary number. We have to remove a little bit from it so that the remaining number should be the maximum among all other options like Input:N=1011Output:111Explanation:Weneedtoremoveonebitsoremoving0bitwillgiveamaximumnumberthanremovingany1’sbit.111>101,011.Input:111Output:11Explanation:Sinceallthebitsare1sowecanremovean

Use the sorting logic of TreeSet in Java to get the maximum and minimum elements in the set Use the sorting logic of TreeSet in Java to get the maximum and minimum elements in the set Sep 02, 2023 pm 12:33 PM

TreeSet is a class in JavaCollectionFramework that implements the SortedSet interface. It stores elements in ascending order and does not allow duplicate values, so access and retrieval times become faster. Because of this excellent feature, TreeSets are often used to store large amounts of information that need to be searched quickly. We will use the Comparable interface to sort a given TreeSet and then, using the built-in methods, try to get the highest and lowest value elements from that TreeSet. Java Program to Get the Highest and Lowest Value Elements from a TreeSet Before entering the program, let us first familiarize ourselves with some conceptually similar interfaces when we want to press the natural order of custom objects

See all articles