时间戳

首页 > 解决方案 > 连续获得最小值

sql - 连续获得最小值

问题描述

我有如下 12 个月的条目，我想获得它的最小值。我有一个用表达式实现的想法，CASE但在可读性上看起来太复杂了。

桌子

   +----------------------------------------------------------------------------+
   | Id | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
   +----------------------------------------------------------------------------+
   | 1  | 0.1 | 1   | -2  | -0.5|  3  |  5  |  9  | 1.5 | 2.5 | 3.6 | 3.6 | 2.5 |
   | 2  | 3   | 2   | 5   | 2.5 |  3  |  6  |  10 | 1.1 | 2.8 | 2.6 | 5.6 | 1.8 |
   | 3  | -1  | 1   | 3   | -0.5|  3  |  5  |  9  | 1.5 | 2.5 | 3.6 | 3.6 | 2.5 |
   +----------------------------------------------------------------------------+

预期产出

   +------------------------------------------------------------------------------------+
   | Id | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Least |
   +------------------------------------------------------------------------------------+
   | 1  | 0.1 | 1   | -2  | -0.5|  3  |  5  |  9  | 1.5 | 2.5 | 3.6 | 3.6 | 2.5 | -2    |
   | 2  | 3   | 2   | 5   | 2.5 |  3  |  6  |  10 | 1.1 | 2.8 | 2.6 | 5.6 | 1.8 | 1.1   |
   | 3  | -1  | 1   | 3   | -0.5|  3  |  5  |  9  | 1.5 | 2.5 | 3.6 | 3.6 | 2.5 | -1    |
   +------------------------------------------------------------------------------------+

我尝试过如下查询，但可读性太复杂 - 有什么方法可以更有效地做到这一点？

SELECT Id, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,
       CASE WHEN Jan< Feb && Jan < Mar && Jan < Apr && Jan < MAY && Jan < Jun && Jan < Jul && Jan < Aug && Jan < Sep && Jan < Oct && Jan < Nov && Jan < Dec THEN Jan
            WHEN Feb < Jan && Feb < Mar && Feb < Apr && Feb < MAY && Feb < Jun && Feb < Jul && Feb < Aug && Feb < Sep && Feb < Oct && Feb < Nov && Feb < Dec THEN Feb
            WHEN...
       END AS Least     
FROM table

标签： sqlsql-servertsqlmin

解决方案

你可以试试cross apply。见下面的脚本：

SELECT Id, Jan, Feb, Mar, Apr, May, Jun, Jul ,Aug, Sep, Oct, Nov, Dec, Least
FROM Test
CROSS APPLY (SELECT MIN(MinColVal) AS Least FROM (VALUES (Jan),(Feb),(Mar),(Apr),(May),(Jun),(Jul),(Aug),(Sep),(Oct),(Nov),(Dec)) AS a(MinColVal))tab

推荐阅读