Учитывая следующую таблицу в SQL Server 2005:
ID Col1 Col2 Col3
-- ---- ---- ----
1 3 34 76
2 32 976 24
3 7 235 3
4 245 1 792
Каков наилучший способ написать запрос, который дает следующий результат (то есть тот, который дает последний столбец - столбец, содержащий значения minium из Col1, Col2 и Col 3 для каждой строки)?
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
UPDATE:
Для пояснения (как я уже сказал в комментариях) в реальном сценарии база данных правильно нормирована. Эти столбцы "массива" не находятся в фактической таблице, а находятся в результирующем наборе, который требуется в отчете. И новое требование состоит в том, что отчет также нуждается в этом столбце MinValue. Я не могу изменить базовый набор результатов, и поэтому я искал T-SQL для удобного "выхода из тюремной карты".
Я попробовал подход CASE, упомянутый ниже, и он работает, хотя он немного громоздкий. Это также сложнее, чем указано в ответах, потому что вам нужно учитывать тот факт, что в одной строке есть два значения min.
В любом случае, я думал, что опубликую свое текущее решение, которое, учитывая мои ограничения, работает очень хорошо. Он использует оператора 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
Я скажу заранее, что я не ожидаю, что это обеспечит лучшую производительность, но с учетом обстоятельств (я не могу перепроектировать все запросы только для нового требования столбца MinValue), это довольно элегантный "get из тюремной карты".
Вероятно, есть много способов сделать это. Мое предложение - использовать Case/When, чтобы сделать это. С 3 столбцами это не так уж плохо.
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
Использование CROSS APPLY:
SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
Лучший способ сделать это, вероятно, не делать этого - странно, что люди настаивают на хранении своих данных способом, который требует "гимнастики" SQL для извлечения значимой информации, когда есть гораздо более простые способы достичь желаемого результата, если вы просто структурируйте свою схему немного лучше :-)
Правильный способ сделать это, на мой взгляд, это иметь следующую таблицу:
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
с ID/Col в качестве первичного ключа (и, возможно, Col в качестве дополнительного ключа, в зависимости от ваших потребностей). Тогда ваш запрос становится простым select min(val) from tbl и вы все равно можете обрабатывать отдельные "старые столбцы" отдельно, используя, where col = 2 в других ваших запросах. Это также позволяет легко расширяться при увеличении количества "старых столбцов".
Это делает ваши запросы намного проще. Общее правило, которое я обычно использую, заключается в том, что если у вас когда-либо есть что-то, похожее на массив в строке базы данных, вы, вероятно, делаете что-то не так и должны подумать о реструктуризации данных.
Однако, если по какой-то причине вы не можете изменить эти столбцы, я бы предложил использовать триггеры вставки и обновления и добавить еще один столбец, для которого эти триггеры установлены на минимум на Col1/2/3. Это переместит "стоимость" операции с выбора на обновление/вставку, к которому она относится - большинство таблиц базы данных в моем опыте читаются гораздо чаще, чем записываются, поэтому затраты на запись, как правило, со временем становятся более эффективными.
Другими словами, минимум для строки изменяется только при изменении одного из других столбцов, так что именно тогда вы должны его вычислять, а не каждый раз, когда выбираете (что теряется, если данные не изменяются). Затем вы получите таблицу, например:
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
Любой другой вариант, который должен принимать решения во время select обычно является плохой идеей с точки зрения производительности, поскольку данные изменяются только при вставке/обновлении - добавление другого столбца занимает больше места в БД и будет немного медленнее для вставок и обновления, но могут быть намного быстрее для выбора - предпочтительный подход должен зависеть от ваших приоритетов, но, как уже говорилось, большинство таблиц читаются гораздо чаще, чем пишутся.
Вы можете использовать подход "грубой силы" с помощью твиста:
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
WHEN Col2 <= Col3 THEN Col2
ELSE Col3
END AS [Min Value] FROM [Your Table]
Когда первое условие прекращается, он гарантирует, что Col1 не является наименьшим значением, поэтому вы можете исключить его из остальной части условий. Аналогично для последующих условий. Для пяти столбцов ваш запрос будет выглядеть следующим образом:
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]
Обратите внимание, что если существует связь между двумя или более столбцами, то <= гарантирует, что мы выйдем из инструкции CASE как можно раньше.
Если столбцы были целыми числами, как в вашем примере, я бы создал функцию:
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
тогда, когда мне нужно будет использовать его, я бы сделал:
select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)
если у вас есть 5 колов, то выше будет
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)
Вы также можете сделать это с помощью запроса объединения. По мере увеличения количества столбцов вам нужно будет изменить запрос, но, по крайней мере, это будет прямое изменение.
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
Используйте это:
select least(col1, col2, col3) FROM yourtable
Это грубая сила, но работает
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
... потому что min() работает только с одним столбцом, а не с столбцами.
Оба этот вопрос И этот вопрос попытайтесь ответить на этот вопрос.
Резюме состоит в том, что у Oracle есть встроенная функция для этого: с Sql Server вы застряли либо определяете пользовательскую функцию, либо используете аргументы case.
Если вы можете создать хранимую процедуру, она может принимать массив значений, и вы можете просто вызвать это.
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
Если вы используете SQL 2005, вы можете сделать что-то вроде этого:
;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
Таким образом, вы не теряетесь во множестве операторов:)
Однако это может быть медленнее, чем другой выбор.
Это ваш выбор...
Небольшой поворот в запросе объединения:
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
Ниже я использую временную таблицу, чтобы получить минимум несколько дат. Первая таблица temp запрашивает несколько связанных таблиц для получения разных дат (а также других значений для запроса), вторая таблица темпов получает различные столбцы и минимальную дату, используя столько проходов, сколько есть столбцов даты.
Это по существу похоже на запрос объединения, требуется такое же количество проходов, но может быть более эффективным (на основе опыта, но потребуется тестирование). Эффективность не была проблемой в этом случае (8000 записей). Можно индексировать и т.д.
--==================== 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
Для нескольких столбцов лучше всего использовать оператор CASE, однако для двух числовых столбцов я и j вы можете использовать простую математику:
min (i, j) = (i + j)/2 - abs (i-j)/2
Эта формула может быть использована для получения минимального значения нескольких столбцов, но его действительно грязное прошлое 2, min (i, j, k) будет min (i, min (j, k))
SELECT [ID],
(
SELECT MIN([value].[MinValue])
FROM
(
VALUES
([Col1]),
([Col1]),
([Col2]),
([Col3])
) AS [value] ([MinValue])
) AS [MinValue]
FROM Table;
Если вы знаете, какие значения вы ищете, как правило, код состояния, может быть полезно следующее:
select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS
Я знаю, что этот вопрос старый, но я все еще нуждался в ответе и не был доволен другими ответами, поэтому мне пришлось придумать свой собственный, который искажает ответ @paxdiablo.
Я приехал из страны SAP ASE 16.0, и мне нужно было только посмотреть статистику определенных данных, которые ИМХО достоверно хранятся в разных столбцах одной строки (они представляют разное время - когда планировалось прибытие чего-то, что ожидалось, когда действие началось и наконец то, что было фактическим временем). Таким образом, я переместил столбцы в строки временной таблицы и подготовил свой запрос, как обычно.
NB Не единственное решение для всех!
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
Это заняло около 30 секунд в исходном наборе из 630000 строк и использовало только индексные данные, поэтому не для запуска в критичном по времени процессе, а для таких вещей, как однократная проверка данных или отчет на конец дня, вы можете быть хорошо (но проверьте это со своими сверстниками или начальством, пожалуйста!). Основным преимуществом этого стиля для меня было то, что я мог легко использовать больше/меньше столбцов и изменять группировку, фильтрацию и т.д., Особенно после копирования данных.
Дополнительные данные (columnName, max es,...) должны были помочь мне в моем поиске, поэтому они могут вам не понадобиться; Я оставил их здесь, чтобы, возможно, зажечь некоторые идеи :-).