sqlserver- abs & min

/* 直线上的最近距离 */
-- create database and table 'point'
if (exists (select * from master.dbo.sysdatabases where name='LeetCode'))
	begin
		use master
		drop database LeetCode
		select 1
	end
go
create database LeetCode
go
use LeetCode
go

if (exists (select * from sys.objects where name='point'))
	drop table point
go
create table point
(
	id int identity primary key,
	x int
)
insert into point values(-1), (0), (2)
go

• min()

-- Aggregation Function Syntax  
MIN ( [ ALL | DISTINCT ] expression )  

-- Analytic Function Syntax   
MIN ( [ ALL ] expression ) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )  
* ALL
将聚合函数应用于所有值。ALL是默认值。

* DISTINCT 
指定考虑每个唯一值。DISTINCT对MIN没有意义，仅适用于ISO兼容性。

* expression
是常量，列名或函数，以及算术运算符，按位运算符和字符串运算符的任意组合。MIN可以与numeric，char，varchar，uniqueidentifier或datetime列一起使用，但不能与位列一起使用。不允许使用聚合函数和子查询。

* OVER （ [ partition_by_clause ] order_by_clause ）
partition_by_clause将FROM子句生成的结果集划分为应用该函数的分区。如果未指定，则该函数将查询结果集的所有行视为单个组。order_by_clause确定执行操作的逻辑顺序。order_by_clause是必需的。有关更多信息，请参阅OVER子句（Transact-SQL）。


eg.1

select * from point  -- select min(x) from point
go
select min(x) from point  -- * min()
go

eg.2

select * from point a, point b  -- *
go
select *, abs(a.x-b.x) from point a, point b
	where a.x!=b.x
go
select min(abs(a.x-b.x)) from point a, point b
	where a.x!=b.x
go
/*
select abs(a.x-b.x) as shortest from point a, point b
	where a.x!=b.x
	order by shortest
	offset 0 row fetch next 1 row only
go */

/* Error
An aggregate may not appear in the WHERE clause 
unless it is in a subquery contained in a HAVING clause 
or a select list, 
and the column being aggregated is an outer reference.

select *, abs(a.x-b.x) as r from point a, point b
	where a.x!=b.x and abs(a.x-b.x)=min(abs(a.x-b.x))   -- min() is aggregate
*/

declare @min int  -- *
set @min=(select min(abs(a.x-b.x)) from point a, point b where a.x!=b.x)
-- print @min
select *, abs(a.x-b.x) from point a, point b
	where a.x!=b.x and abs(a.x-b.x)=@min
go