Jaki jest najlepszy sposób, aby wybrać minimalną wartość z kilku kolumn?
Podano następującą tabelę w SQL Server 2005:
ID Col1 Col2 Col3
-- ---- ---- ----
1 3 34 76
2 32 976 24
3 7 235 3
4 245 1 792
Jaki jest najlepszy sposób na napisanie zapytania, które daje następujący wynik (tj. taki, który daje końcową kolumnę-kolumnę zawierającą minium wartości z Col1, Col2 i Col 3 dla każdego wiersza )?
ID Col1 Col2 Col3 TheMin
-- ---- ---- ---- ------
1 3 34 76 3
2 32 976 24 24
3 7 235 3 3
4 245 1 792 1
aktualizacja:
Dla wyjaśnienia (jak powiedziałem w komentarzach) w rzeczywistym scenariuszu baza danych jest odpowiednio znormalizowana . Te kolumny" array " to Nie w rzeczywistej tabeli, ale są w zestawie wyników, który jest wymagany w raporcie. Nowym wymogiem jest to, że raport potrzebuje również tej kolumny wartości minimalnej. Nie mogę zmienić podstawowego zestawu wyników i dlatego Szukałem T-SQL dla poręcznej "wyjść z więzienia karty".
Wypróbowałem podejście CASE wymienione poniżej i działa, choć jest nieco uciążliwe. Jest to również bardziej skomplikowane niż podane w odpowiedziach, ponieważ należy uwzględnić fakt, że istnieją dwie wartości min w ten sam rząd.
W każdym razie, pomyślałem, że opublikuję moje obecne rozwiązanie, które, biorąc pod uwagę moje ograniczenia, działa całkiem dobrze. Używa operatora UNPIVOT:
with cte (ID, Col1, Col2, Col3)
as
(
select ID, Col1, Col2, Col3
from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
select
ID, min(Amount) as TheMin
from
cte
UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
group by ID
) as minValues
on cte.ID = minValues.ID
Powiem z góry, że nie oczekuję, że będzie to najlepsza wydajność, ale biorąc pod uwagę okoliczności (nie mogę przeprojektować wszystkich zapytań tylko dla nowego wymogu kolumny MinValue), jest to dość elegancka karta "WYJDŹ z więzienia".
19 answers
Istnieje wiele sposobów, aby to osiągnąć. Moja sugestia to użycie Case / kiedy to zrobić. Z 3 kolumnami nie jest tak źle.
Select Id,
Case When Col1 < Col2 And Col1 < Col3 Then Col1
When Col2 < Col1 And Col2 < Col3 Then Col2
Else Col3
End As TheMin
From YourTableNameHere
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-15 13:39:23
Używając CROSS APPLY
:
SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) AS MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-06-26 08:33:29
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-04 11:03:22
W MySQL użyj tego:
select least(col1, col2, col3) FROM yourtable
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-10-30 16:20:24
Możesz użyć podejścia "brute force"z przekrętem:
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
WHEN Col2 <= Col3 THEN Col2
ELSE Col3
END AS [Min Value] FROM [Your Table]
Gdy pierwszy warunek zawiedzie to gwarantuje, że Col1 nie jest najmniejszą wartością, dlatego można go wyeliminować z pozostałych warunków. Podobnie dla kolejnych warunków. Dla pięciu kolumn Twoje zapytanie staje się:
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1
WHEN Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2
WHEN Col3 <= Col4 AND Col3 <= Col5 THEN Col3
WHEN Col4 <= Col5 THEN Col4
ELSE Col5
END AS [Min Value] FROM [Your Table]
Zauważ, że jeśli istnieje remis między dwiema lub więcej kolumnami, to <=
zapewnia, że opuścimy instrukcję CASE
tak wcześnie, jak to możliwe.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-05 15:47:20
Gdyby kolumny były liczbami całkowitymi jak w twoim przykładzie stworzyłbym funkcję:
create function f_min_int(@a as int, @b as int)
returns int
as
begin
return case when @a < @b then @a else coalesce(@b,@a) end
end
Wtedy, gdy muszę go użyć, zrobię:
select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)
Jeśli masz 5 kolumów to powyższe staje się
select col1, col2, col3, col4, col5,
dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(col1,col2),col3),col4),col5)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-28 13:58:22
Najlepszym sposobem na to jest prawdopodobnie nie aby to zrobić-to dziwne, że ludzie nalegają na przechowywanie swoich danych w sposób, który wymaga SQL "gimnastyka", aby wyodrębnić sensowne informacje, gdy istnieją o wiele łatwiejsze sposoby osiągnięcia pożądanego rezultatu, jeśli po prostu struktura schematu trochę lepiej: -)
The right sposób, aby to zrobić, moim zdaniem, jest następująca tabela:
ID Col Val
-- --- ---
1 1 3
1 2 34
1 3 76
2 1 32
2 2 976
2 3 24
3 1 7
3 2 235
3 3 3
4 1 245
4 2 1
4 3 792
Z ID/Col
jako kluczem głównym (i ewentualnie Col
jako kluczem dodatkowym, w zależności od potrzeb). Następnie Twoje zapytanie staje się proste select min(val) from tbl
i nadal możesz traktować poszczególne 'stare kolumny' oddzielnie za pomocą where col = 2
w innych zapytaniach. Pozwala to również na łatwą rozbudowę w przypadku wzrostu liczby "starych kolumn".
To sprawia, że Twoje zapytania tak są znacznie łatwiejsze. Ogólnie rzecz biorąc, jeśli kiedykolwiek masz coś, co wygląda jak tablica w wierszu bazy danych, prawdopodobnie robisz coś źle i powinieneś pomyśleć o restrukturyzacja danych.
Jeśli jednak z jakiegoś powodu nie możesz zmienić tych kolumn, sugerowałbym użycie wyzwalaczy insert i update i dodanie innej kolumny, którą wyzwalacze te ustawiają na minimum na Col1/2/3
. Spowoduje to przesunięcie "koszt" operacji z wyboru do aktualizacji / wstawiania, gdzie należy - Większość tabel baz danych z mojego doświadczenia jest czytana znacznie częściej niż pisana, więc ponoszenie kosztów zapisu wydaje się być bardziej wydajne w czasie.
Innymi słowy, minimum dla wiersza zmienia się tylko wtedy, gdy zmienia się jedna z pozostałych kolumn, więc to jest Kiedy powinieneś go obliczać ,a nie za każdym razem, gdy wybierzesz (co jest marnowane, jeśli DANE się nie zmieniają). Można by wtedy skończyć z tabelą w stylu:
ID Col1 Col2 Col3 MinVal
-- ---- ---- ---- ------
1 3 34 76 3
2 32 976 24 24
3 7 235 3 3
4 245 1 792 1
Każda inna opcja, która musi podejmować decyzje w czasie select
, jest zazwyczaj złym pomysłem pod względem wydajności, ponieważ dane zmieniają się tylko przy insert/update - dodanie innej kolumny zajmuje więcej miejsca w DB i będzie nieco wolniejszy dla wstawek i aktualizacji, ale może być znacznie szybszy dla selekcji - preferowane podejście powinno zależeć od twoich priorytetów, ale, jak wspomniano, większość tabel jest czytana daleko częściej niż są napisane.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-04-19 02:00:50
Można to również zrobić za pomocą zapytania związkowego. Wraz ze wzrostem liczby kolumn, będziesz musiał zmodyfikować zapytanie, ale przynajmniej będzie to prosta modyfikacja.
Select T.Id, T.Col1, T.Col2, T.Col3, A.TheMin
From YourTable T
Inner Join (
Select A.Id, Min(A.Col1) As TheMin
From (
Select Id, Col1
From YourTable
Union All
Select Id, Col2
From YourTable
Union All
Select Id, Col3
From YourTable
) As A
Group By A.Id
) As A
On T.Id = A.Id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-15 13:51:25
To jest brute force ale działa
select case when col1 <= col2 and col1 <= col3 then col1
case when col2 <= col1 and col2 <= col3 then col2
case when col3 <= col1 and col3 <= col2 then col3
as 'TheMin'
end
from Table T
... ponieważ min () działa tylko na jednej kolumnie, a nie między kolumnami.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-15 13:40:36
Oba to pytanie Na to pytanie Postaraj się odpowiedzieć.
Podsumowanie jest takie, że Oracle ma wbudowaną funkcję do tego, z Sql Server utknąłeś albo definiując funkcję zdefiniowaną przez Użytkownika, albo używając instrukcji case.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:02:39
Dla wielu kolumn najlepiej jest użyć instrukcji CASE, jednak dla dwóch liczbowych kolumn i I j można użyć prostej matematyki:
Min (I, j) = (I+j) / 2-abs (I-j) / 2
Ta formuła może być używana do uzyskania minimalnej wartości wielu kolumn, ale jej naprawdę niechlujna przeszłość 2,min(I,J,k) będzie min(i,min(j, k))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-11 15:50:54
Jeśli jesteś w stanie wykonać procedurę składowaną, może ona przyjąć tablicę wartości i możesz ją po prostu wywołać.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-15 13:44:56
select *,
case when column1 < columnl2 And column1 < column3 then column1
when columnl2 < column1 And columnl2 < column3 then columnl2
else column3
end As minValue
from tbl_example
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-12-15 13:46:11
Mały zwrot w sprawie zapytania związkowego:
DECLARE @Foo TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT)
INSERT @Foo (ID, Col1, Col2, Col3)
VALUES
(1, 3, 34, 76),
(2, 32, 976, 24),
(3, 7, 235, 3),
(4, 245, 1, 792)
SELECT
ID,
Col1,
Col2,
Col3,
(
SELECT MIN(T.Col)
FROM
(
SELECT Foo.Col1 AS Col UNION ALL
SELECT Foo.Col2 AS Col UNION ALL
SELECT Foo.Col3 AS Col
) AS T
) AS TheMin
FROM
@Foo AS Foo
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-08-30 20:53:15
Jeśli używasz SQL 2005 możesz zrobić coś schludnego w ten sposób:
;WITH res
AS ( SELECT t.YourID ,
CAST(( SELECT Col1 AS c01 ,
Col2 AS c02 ,
Col3 AS c03 ,
Col4 AS c04 ,
Col5 AS c05
FROM YourTable AS cols
WHERE YourID = t.YourID
FOR
XML AUTO ,
ELEMENTS
) AS XML) AS colslist
FROM YourTable AS t
)
SELECT YourID ,
colslist.query('for $c in //cols return min(data($c/*))').value('.',
'real') AS YourMin ,
colslist.query('for $c in //cols return avg(data($c/*))').value('.',
'real') AS YourAvg ,
colslist.query('for $c in //cols return max(data($c/*))').value('.',
'real') AS YourMax
FROM res
W ten sposób nie zgubisz się w tylu operatorach:)
Jednak może to być wolniejsze niż inne rozwiązanie.
To twój wybór...Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-06-25 09:06:55
Poniżej używam tabeli temp, aby uzyskać minimum kilka dat. Pierwsza tabela tymczasowa zapytuje kilka połączonych tabel, aby uzyskać różne daty( jak również inne wartości dla zapytania), druga tabela tymczasowa otrzymuje różne kolumny i minimalną datę, używając tylu przejść, ile kolumn daty.
Jest to zasadniczo jak zapytanie union, wymagana jest ta sama liczba przejść, ale może być bardziej wydajna (w oparciu o doświadczenie, ale wymagałaby testów). Wydajność nie była problemem w tym przypadku (8000 rekordów). Można indeksować itp.
--==================== this gets minimums and global min
if object_id('tempdb..#temp1') is not null
drop table #temp1
if object_id('tempdb..#temp2') is not null
drop table #temp2
select r.recordid , r.ReferenceNumber, i.InventionTitle, RecordDate, i.ReceivedDate
, min(fi.uploaddate) [Min File Upload], min(fi.CorrespondenceDate) [Min File Correspondence]
into #temp1
from record r
join Invention i on i.inventionid = r.recordid
left join LnkRecordFile lrf on lrf.recordid = r.recordid
left join fileinformation fi on fi.fileid = lrf.fileid
where r.recorddate > '2015-05-26'
group by r.recordid, recorddate, i.ReceivedDate,
r.ReferenceNumber, i.InventionTitle
select recordid, recorddate [min date]
into #temp2
from #temp1
update #temp2
set [min date] = ReceivedDate
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.ReceivedDate < [min date] and t1.ReceivedDate > '2001-01-01'
update #temp2
set [min date] = t1.[Min File Upload]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Upload] < [min date] and t1.[Min File Upload] > '2001-01-01'
update #temp2
set [min date] = t1.[Min File Correspondence]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Correspondence] < [min date] and t1.[Min File Correspondence] > '2001-01-01'
select t1.*, t2.[min date] [LOWEST DATE]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
order by t1.recordid
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-02 00:37:59
SELECT [ID],
(
SELECT MIN([value].[MinValue])
FROM
(
VALUES
([Col1]),
([Col1]),
([Col2]),
([Col3])
) AS [value] ([MinValue])
) AS [MinValue]
FROM Table;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-10 10:11:16
Jeśli wiesz, jakich wartości szukasz, zazwyczaj kod statusu, pomocne mogą być następujące wartości:
select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-28 19:47:52
Wiem, że to pytanie jest stare, ale wciąż potrzebowałem odpowiedzi i nie byłem zadowolony z innych odpowiedzi, więc musiałem opracować własną, która jest zwrotem na @ paxdiablos odpowiedz .
PochodzÄ ™ z krainy SAP ASE 16.0 i potrzebowaĺ' em tylko spojrzenia na statystyki pewnych danych, ktĂłre sÄ ... IMHO prawomocnie przechowywane w róşnych kolumnach w jednym wierszu (reprezentujÄ ... one róşne czasy - kiedy coĹ " byĹ 'o planowane, czego byĹ' o oczekiwane, kiedy rozpoczÄ ™ Ĺ 'a siÄ ™ akcja i w koĹ" cu jaki był rzeczywisty czas). W ten sposób przetransponowałem kolumny do wierszy tymczasowej tabeli i wstępnie uformowałem moje zapytanie nad tym, jak zwykle.
Uwaga nie jedno uniwersalne rozwiązanie przed nami!
CREATE TABLE #tempTable (ID int, columnName varchar(20), dataValue int)
INSERT INTO #tempTable
SELECT ID, 'Col1', Col1
FROM sourceTable
WHERE Col1 IS NOT NULL
INSERT INTO #tempTable
SELECT ID, 'Col2', Col2
FROM sourceTable
WHERE Col2 IS NOT NULL
INSERT INTO #tempTable
SELECT ID, 'Col3', Col3
FROM sourceTable
WHERE Col3 IS NOT NULL
SELECT ID
, min(dataValue) AS 'Min'
, max(dataValue) AS 'Max'
, max(dataValue) - min(dataValue) AS 'Diff'
FROM #tempTable
GROUP BY ID
Zajęło to około 30 sekund na zestawie źródłowym 630000 wierszy i używało tylko danych indeksowych, więc nie jest to rzecz do uruchomienia w krytycznym czasie, ale dla rzeczy takich jak Jednorazowa kontrola danych lub raport końca dnia może być dobrze (ale zweryfikuj to z rówieśnikami lub przełożonymi, proszę!). Główną zaletą tego stylu dla mnie było to, że mogłem łatwo użyć więcej / mniej kolumn i zmienić grupowanie, filtrowanie itp., zwłaszcza po skopiowaniu danych.
Dodatkowe dane (columnName
, max
es, ...) miały mi pomóc w poszukiwaniach, więc może ich nie potrzebujesz; zostawiłam je tutaj, aby być może iskrzyć kilka pomysłów :-).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-04-18 13:34:56