sql max из нескольких столбцов?

<p>как вы возвращаете 1 значение в строке максимум нескольких столбцов:</p><p><strong> tablename </strong></p><pre><code>[number, date1, date2, date3, cost] </code></pre><p>мне нужно вернуть что-то вроде этого:</p><pre><code>[number, most\_recent\_date, cost] </code></pre><p>запрос?</p>

<p>ну, вы можете использовать оператор case:</p><pre><code>select case when date1 >= date2 and date1 >= date3 then date1 when date2 >= date1 and date2 >= date3 then date2 when date3 >= date1 and date3 >= date2 then date3 else date1 end as mostrecentdate </code></pre><p>[для microsoft sql server<strong> 2008 </strong>и выше, вы можете рассмотреть более простой ответ свена ниже.]</p>

<p>вот еще одно приятное решение для функциональности <code>max</code> с использованием t-sql и sql server.</p><pre><code>select [other fields], (select max(v) from (values (date1), (date2), (date3),...) as value(v)) as [maxdate] from [yourtablename] </code></pre>

<p>если вы используете mysql, вы можете использовать</p><pre><code>select greatest(col1, col2 ...) from table </code></pre>

<p>есть еще 3 метода, где <code>unpivot</code> (1) является самым быстрым на сегодняшний день, за которым следует simulated unpivot (3), который намного медленнее, чем (1), но все же быстрее, чем (2)</p><pre><code>create table dates ( number int primary key , date1 datetime , date2 datetime , date3 datetime , cost int ) insert into dates values ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 ) insert into dates values ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 ) insert into dates values ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 ) insert into dates values ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 ) go </code></pre><h2>решение 1 (<code>unpivot</code>)</h2><pre><code>select number , max(ddate) maxdate , cost from dates unpivot ( ddate for ndate in ( date1, date2, date3 ) ) as u group by number , cost go </code></pre><h2>решение 2 (подзапрос на строку)</h2><pre><code>select number , ( select max(ddate) maxdate from ( select d.date1 as ddate union select d.date2 union select d.date3 ) a ) maxdate , cost from dates d go </code></pre><h2>решение 3 (имитация <code>unpivot</code>)</h2><pre><code>;with maxd as ( select number , max(case rn when 1 then date1 when 2 then date2 else date3 end) as maxdate from dates a cross join ( select 1 as rn union select 2 union select 3 ) b group by number ) select dates.number , maxd.maxdate , dates.cost from dates inner join maxd on dates.number = maxd.number go drop table dates go </code></pre>

<p>любой из двух примеров ниже будет работать:</p><pre><code>select max(date\_columns) as max\_date from ( (select date1 as date\_columns from data\_table ) union ( select date2 as date\_columns from data\_table ) union ( select date3 as date\_columns from data\_table ) ) as date\_query </code></pre><p>второе - это дополнение к <a href="https://stackoverflow.com/users/267/lassevk">lassevk's</a> answer.</p><pre><code>select max(mostrecentdate) from ( select case when date1 >= date2 and date1 >= date3 then date1 when date2 >= date1 and date2 >= date3 then date2 when date3 >= date1 and date3 >= date2 then date3 else date1 end as mostrecentdate from data\_table ) as date\_query </code></pre>

<p>скалярная функция вызывает всевозможные проблемы с производительностью, поэтому лучше, если возможно, обернуть логику во встроенную табличную функцию. это функция, которую я использовал для замены некоторых пользовательских функций, которые выбирали минимальные/максимальные даты из списка до десяти дат. при тестировании на моем наборе данных из 1 миллиона строк скалярная функция заняла более 15 минут, прежде чем я завершил выполнение запроса. встроенный tvf занял 1 минуту, что соответствует количеству результатов, выбранных во временной таблице. чтобы использовать это, вызовите функцию из подзапроса в select или cross apply.</p><pre><code>create function dbo.get\_min\_max\_date ( @date1 datetime, @date2 datetime, @date3 datetime, @date4 datetime, @date5 datetime, @date6 datetime, @date7 datetime, @date8 datetime, @date9 datetime, @date10 datetime ) returns table as return ( select max(datevalue) max\_date, min(datevalue) min\_date from ( values (@date1), (@date2), (@date3), (@date4), (@date5), (@date6), (@date7), (@date8), (@date9), (@date10) ) as dates(datevalue) ) </code></pre>

<pre><code>declare @tablename table (number int, date1 datetime, date2 datetime, date3 datetime, cost money) insert into @tablename select 1, '20000101', '20010101','20020101',100 union all select 2, '20000101', '19900101','19980101',99 select number, cost , (select max([date]) from (select date1 as [date] union all select date2 union all select date3 ) d ) [most recent date] from @tablename </code></pre>

<p>для t-sql (mssql 2008+)</p><pre><code>select (select max(mymaxname) from ( values (max(isortcode)), (max(field2)) ) myalias(mymaxname) ) from mytable1 </code></pre>

<pre><code>select case when date1 >= date2 and date1 >= date3 then date1 when date2 >= date3 then date2 else date3 end as mostrecentdate </code></pre><p>это немного легче записать и пропускает этапы оценки, так как оператор case оценивается по порядку.</p>

<p>к сожалению <a href="https://stackoverflow.com/a/71045/224704">ответ лассе</a> , хотя, казалось бы, очевиден, имеет решающий недостаток. он не может обрабатывать значения null. любое единственное значение null приводит к возвращению date1. к сожалению, любая попытка решить эту проблему имеет тенденцию быть чрезвычайно грязной и не очень хорошо масштабируется до 4 или более значений.</p><p> <a href="https://stackoverflow.com/a/71147/224704">первый ответ databyss</a> выглядел (и есть) хорошо. однако неясно, будет ли ответ легко экстраполироваться на 3 значения из объединения нескольких таблиц вместо более простых 3 значений из одной таблицы. я хотел избежать превращения такого запроса в подзапрос, чтобы получить максимум 3 столбца, а также был уверен, что отличная идея databyss может быть немного исправлена.</p><p>итак, без дальнейших церемоний, вот мое решение (полученное из идеи databyss).<br> он использует перекрестные объединения, выбирая константы для имитации эффекта объединения нескольких таблиц. важно отметить, что все необходимые псевдонимы проходят правильно (что не всегда так), и это делает шаблон довольно простым и достаточно масштабируемым с помощью дополнительных столбцов.</p><pre><code>declare @v1 int , @v2 int , @v3 int --set @v1 = 1 --comment out set statements to experiment with --various combinations of null values set @v2 = 2 set @v3 = 3 select ( select max(vals) from ( select v1 as vals union select v2 union select v3 ) tmp where vals is not null -- this eliminates null warning ) as maxval from ( select @v1 as v1 ) t1 cross join ( select @v2 as v2 ) t2 cross join ( select @v3 as v3 ) t3 </code></pre>

<p>проблема: выберите минимальное значение ставки, данное субъекту требования: агентские ставки могут быть нулевыми</p><pre><code>[minratevalue] = case when isnull(fitchrating.ratingvalue, 100) < = isnull(moodyrating.ratingvalue, 99) and isnull(fitchrating.ratingvalue, 100) < = isnull(standardpoorsrating.ratingvalue, 99) then fitchgagency.ratingagencyname when isnull(moodyrating.ratingvalue, 100) < = isnull(standardpoorsrating.ratingvalue , 99) then moodyagency.ratingagencyname else isnull(standardpoorsrating.ratingvalue, 'n/a') end </code></pre><p>вдохновленный <a href="https://stackoverflow.com/a/4308539">этот ответ</a> от <a href="https://stackoverflow.com/users/13813/nat">nat</a> </p>

<p>использование cross apply (для 2005+) ....</p><pre><code>select mostrecentdate from sourcetable cross apply (select max(d) mostrecentdate from (values (date1), (date2), (date3)) as a(d)) md </code></pre>

<p>с sql server 2012 мы можем использовать <a href="http://msdn.microsoft.com/en-us/library/hh213574%28v=sql.110%29.aspx">iif</a> .</p><pre><code> declare @date1 date='2014-07-03'; declare @date2 date='2014-07-04'; declare @date3 date='2014-07-05'; select iif(@date1>@date2, iif(@date1>@date3,@date1,@date3), iif(@date2>@date3,@date2,@date3)) as mostrecentdate </code></pre>

<p>если вы используете sql server 2005, вы можете использовать функцию unpivot. вот полный пример:</p><pre><code>create table dates ( number int, date1 datetime, date2 datetime, date3 datetime ) insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008') insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008') insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008') insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008') select max(datemaxes) from ( select (select max(date1) from dates) date1max, (select max(date2) from dates) date2max, (select max(date3) from dates) date3max ) mytable unpivot (datemaxes for fieldname in (date1max, date2max, date3max)) as tblpivot drop table dates </code></pre>

<p>пожалуйста, попробуйте использовать <code>unpivot</code>:</p><pre><code>select max(maxdt) maxdt from tbl unpivot (maxdt for e in (date1, date2, date3) )as unpvt; </code></pre>

<p>я предпочитаю решения, основанные на случае, когда, я предполагаю, что это должно оказать наименьшее влияние на возможное падение производительности по сравнению с другими возможными решениями, такими как с перекрестным применением, values (), пользовательскими функциями и т.д.</p><p>вот вариант case-when, который обрабатывает нулевые значения с большинством возможных тестовых случаев:</p><pre><code>select case when date1 > coalesce(date2,'0001-01-01') and date1 > coalesce(date3,'0001-01-01') then date1 when date2 > coalesce(date3,'0001-01-01') then date2 else date3 end as mostrecentdate , * from (values ( 1, cast('2001-01-01' as date), cast('2002-01-01' as date), cast('2003-01-01' as date)) ,( 2, cast('2001-01-01' as date), cast('2003-01-01' as date), cast('2002-01-01' as date)) ,( 3, cast('2002-01-01' as date), cast('2001-01-01' as date), cast('2003-01-01' as date)) ,( 4, cast('2002-01-01' as date), cast('2003-01-01' as date), cast('2001-01-01' as date)) ,( 5, cast('2003-01-01' as date), cast('2001-01-01' as date), cast('2002-01-01' as date)) ,( 6, cast('2003-01-01' as date), cast('2002-01-01' as date), cast('2001-01-01' as date)) ,( 11, cast(null as date), cast('2002-01-01' as date), cast('2003-01-01' as date)) ,( 12, cast(null as date), cast('2003-01-01' as date), cast('2002-01-01' as date)) ,( 13, cast('2003-01-01' as date), cast(null as date), cast('2002-01-01' as date)) ,( 14, cast('2002-01-01' as date), cast(null as date), cast('2003-01-01' as date)) ,( 15, cast('2003-01-01' as date), cast('2002-01-01' as date), cast(null as date)) ,( 16, cast('2002-01-01' as date), cast('2003-01-01' as date), cast(null as date)) ,( 21, cast('2003-01-01' as date), cast(null as date), cast(null as date)) ,( 22, cast(null as date), cast('2003-01-01' as date), cast(null as date)) ,( 23, cast(null as date), cast(null as date), cast('2003-01-01' as date)) ,( 31, cast(null as date), cast(null as date), cast(null as date)) ) as demovalues(id, date1,date2,date3) order by id ; </code></pre><p>и результат:</p><pre><code>mostrecent id date1 date2 date3 2003-01-01 1 2001-01-01 2002-01-01 2003-01-01 2003-01-01 2 2001-01-01 2003-01-01 2002-01-01 2003-01-01 3 2002-01-01 2001-01-01 2002-01-01 2003-01-01 4 2002-01-01 2003-01-01 2001-01-01 2003-01-01 5 2003-01-01 2001-01-01 2002-01-01 2003-01-01 6 2003-01-01 2002-01-01 2001-01-01 2003-01-01 11 null 2002-01-01 2003-01-01 2003-01-01 12 null 2003-01-01 2002-01-01 2003-01-01 13 2003-01-01 null 2002-01-01 2003-01-01 14 2002-01-01 null 2003-01-01 2003-01-01 15 2003-01-01 2002-01-01 null 2003-01-01 16 2002-01-01 2003-01-01 null 2003-01-01 21 2003-01-01 null null 2003-01-01 22 null 2003-01-01 null 2003-01-01 23 null null 2003-01-01 null 31 null null null </code></pre>

<p>еще один способ использования<strong> случай когда </strong> </p><pre><code>select case true when max(row1) >= max(row2) then case true when max(row1) >= max(row3) then max(row1) else max(row3) end else case true when max(row2) >= max(row3) then max(row2) else max(row3) end end from yourtable </code></pre>

<p>основываясь на решении <a href="http://www.experts-exchange.com/m\_664986.html">scottpletcher</a> от <a href="http://www.experts-exchange.com/microsoft/development/ms-sql-server/q\_24204894.html">http://www.experts-exchange.com/microsoft/development/ms-sql-server/q\_24204894.html</a> я создал набор функций (например, getmaxofdates3, getmaxofdates13), чтобы найти максимум до 13 значений даты с помощью union all . см. функцию <a href="http://geekswithblogs.net/mnf/archive/2011/07/11/t-sql-function-to-get-maximum-of-values-from-the-same.aspx">t-sql, чтобы получить максимум значений из одной строки</a> однако я не считается решением unpivot на момент написания этих функций </p>

<p>вы можете создать функцию, в которой вы передаете даты, а затем добавляете эту функцию в оператор выбора, как показано ниже .. select number, dbo.fxmost\_recent\_date (date1, date2, date3), cost</p><pre><code>create function fxmost\_recent\_date </code></pre><p>( @ date1 smalldatetime, @ date2 smalldatetime, @ date3 smalldatetime ) возвращает smalldatetime as begin объявить @result smalldatetime</p><pre><code>declare @mostrecent smalldatetime set @mostrecent='1/1/1900' if @date1>@mostrecent begin set @mostrecent=@date1 end if @date2>@mostrecent begin set @mostrecent=@date2 end if @date3>@mostrecent begin set @mostrecent=@date3 end return @mostrecent </code></pre><p>конец</p>