星期二, 1月 17, 2023

[SQL] Greatest、Least

SQL Server 2022 新函式 - Greatest、Least，官方文件說明
This function returns the maximum (minimum) value from a list of one or more expressions.
看文字說明是無感，但在該範例中發現亮點 - 跨欄位彙總
SELECT
  P.Name
  ,P.SellStartDate
  ,P.DiscontinuedDate
  ,PM.ModifiedDate AS ModelModifiedDate
  ,GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) AS LatestDate
FROM SalesLT.Product AS P
  INNER JOIN SalesLT.ProductModel AS PM ON P.ProductModelID = PM.ProductModelID
WHERE GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) >= '2007-01-01'
  AND P.SellStartDate >= '2007-01-01'
  AND P.Name LIKE 'Touring %'
ORDER BY P.Name
在該篇 [SQL] 資料表值建構函式 - 跨欄位彙總 也是想達到相同目的，但使用 Greatest、Leaset 可讀性更佳

文件上不支援資料型別說明
The following types are not supported for comparison in GREATEST: varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes, cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.

The varchar(max), varbinary(max), and nvarchar(max) data types are supported for arguments that are 8,000 bytes or below, and will be implicitly converted to varchar(n), varbinary(n), and nvarchar(n), respectively, prior to comparison.

For example, varchar(max) can support up to 8,000 characters if using a single-byte encoding character set, and nvarchar(max) can support up to 4,000 byte-pairs (assuming UTF-16 character encoding).

1 則留言: