MSSQL 多字段根据范围求最大值实现方法
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
*/

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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

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

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 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.

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

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

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

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
