选择所有列的最小值

Table Result :

表结果：

        ID      CAt     date            BS  BD
        19048   4       12/10/2013    132   68
        19048   4       12/10/2013 2:43 138 78
        19048   4       12/10/2013 2:44 128 74
        19048   4       12/10/2013 2:45 140 80
        19048   4       6/30/2014 22:07 146 88
        19048   4       6/30/2014 22:07 148 88
        19048   4       6/30/2014 22:08 138 86
        19048   4       6/30/2014 22:08 132 80

I want min of bs and BD for each ID and date.

我想要每个ID和日期的最小bs和BD。

Expected output

预期产出

        ID      CAt date                NAME    Value
        19048   4   12/10/2013          BS      128
        19048   4   12/10/2013          BD      68
        19048   4   6/30/2014           BS      132
        19048   4   6/30/2014           BD      80

3 个解决方案

#1

You need Group By and Min aggregate.

您需要Group By和Min聚合。

Try this way

试试这种方式

select ID,CAt, min([date]),Min(BS),Min(BD)
From yourtable 
Group by ID,CAt,cast([date] as Date)

cast([date] as Date) in group by is used to find the min BS and BD for each date

group by中的cast（[date] as Date）用于查找每个日期的最小BS和BD

For second output you need to unpivot the data

对于第二个输出，您需要取消数据

SELECT ID,CAt,[date] = Min([date]),name,value = Min(value)
FROM   yourtable
       CROSS apply (VALUES ('bs',bs),
                           ('bd',bd)) cs (name, value)
GROUP  BY ID,CAt,Cast([date] AS DATE),name

#2

First Group by ID, CAt, date and get min and max value from grouped rows:

第一组按ID，CAt，日期并从分组行中获取最小值和最大值：

    select ID, CAt, date, 
    (case when BS>=0 then 'BS' else 'BD' end ) as NAME ,
    (case when BS>=0 then BS else BD end ) as Value from
   ( select ID, CAt, date , min(BS) as BS, -1 as BD 
    from table_name
    group by ID, CAt, date
    union all 
    select ID, CAt, date , -1 as BS, min(BD) as BD 
    from table_name
    group by ID, CAt, date
    ) as A

You also group by different group of column as per requirement.

您还可以根据要求按不同的列组进行分组。

#3

MIN() selects minimum value according to group by condition. Date() selects only date portion of DATETIME column

MIN（）根据条件选择最小值。 Date（）仅选择DATETIME列的日期部分

SELECT ID , CAT, n.DATE, min(BS) AS BS, min(BD) AS BD FROM test.new_table AS n group by ID, Date(n.DATE);

#1