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

How to get the maximum value in a PHP array When writing PHP code, you often need to perform various operations on the array, including getting the maximum value in the array. In this article, we will introduce how to use PHP's built-in and custom functions to get the maximum value in an array, and provide corresponding code examples. Using the PHP built-in function max() PHP provides a built-in function max() that can easily get the maximum value from an array. Here is a code example using this function: <?php$numbers

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
