Y a-t-il une fonction Max dans SQL Server qui prend deux valeurs comme Math.Max in.NET?
je veux écrire une requête comme ceci:
SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
mais ce n'est pas comme ça que fonctionne la fonction MAX
, n'est-ce pas? Il s'agit d'une fonction agrégée de sorte qu'elle s'attend à un seul paramètre et retourne ensuite le MAX de toutes les lignes.
est-ce que quelqu'un sait comment faire à ma façon?
25 réponses
vous auriez besoin de faire un User-Defined Function
si vous vouliez avoir une syntaxe similaire à votre exemple, mais pourriez-vous faire ce que vous voulez, en ligne, assez facilement avec une déclaration CASE
, comme les autres l'ont dit.
le UDF
pourrait être quelque chose comme ceci:
create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
if @val1 > @val2
return @val1
return isnull(@val2,@val1)
end
... et vous appelez ça comme si ...
SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice)
FROM Order o
si vous utilisez SQL Server 2008 (ou une version plus récente), alors c'est la meilleure solution:
SELECT o.OrderId,
(SELECT MAX(Price)
FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price))
FROM Order o
Tout le crédit et les votes doivent aller à Sven, en réponse à une question relative à la "SQL MAX de plusieurs colonnes?"
je dis que c'est la" meilleure réponse "parce que:
- Il n'a pas besoin de compliquer votre code à l'aide de l'UNION, PIVOT, UNPIVOT's, UDF, and crazy-long CASE relevés.
- il n'est pas tourmenté par le problème de manipulation des nulls, il les gère très bien.
- il est facile de remplacer le" MAX "par" MIN"," AVG", ou"SUM". Vous pouvez utiliser n'importe quelle fonction d'agrégation pour trouver la somme sur plusieurs colonnes différentes.
- Vous n'êtes pas limité à, les noms que j'ai utilisé (c'est à dire "AllPrices" et "Prix"). Vous pouvez choisir vos propres noms pour le rendre plus facile à lire et à comprendre pour le prochain gars.
- vous pouvez trouver plusieurs agrégats en utilisant SQL Server 2008's derived_tables like so:
SELECT MAX(a), MAX (b) FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable (a, b)
peut être fait en une ligne:
-- the following expression calculates ==> max(@val1, @val2)
SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2))
Edit: si vous avez affaire à de très grands nombres, vous devrez convertir les variables de valeur en bigint afin d'éviter un débordement d'entier.
Je ne pense pas. J'ai voulu l'autre jour. Le plus proche que j'ai eu était:
SELECT
o.OrderId,
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END
FROM Order o
Pourquoi ne pas essayer IIF fonction (nécessite SQL Server 2012 et ultérieures)
IIF(a>b, a, b)
C'est ça.
DECLARE @MAX INT
@MAX = (SELECT MAX(VALUE)
FROM (SELECT 1 AS VALUE UNION
SELECT 2 AS VALUE) AS T1)
les autres réponses sont bonnes, mais si vous devez vous soucier d'avoir des valeurs nulles, vous pouvez vouloir cette variante:
SELECT o.OrderId,
CASE WHEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice) > ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
THEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice)
ELSE ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
END
FROM Order o
les requêtes secondaires peuvent accéder aux colonnes de la requête externe de sorte que vous pouvez utiliser cette approche pour utiliser des agrégats tels que MAX
à travers les colonnes. (Probablement plus utile quand il y a un plus grand nombre de colonnes)
;WITH [Order] AS
(
SELECT 1 AS OrderId, 100 AS NegotiatedPrice, 110 AS SuggestedPrice UNION ALL
SELECT 2 AS OrderId, 1000 AS NegotiatedPrice, 50 AS SuggestedPrice
)
SELECT
o.OrderId,
(SELECT MAX(price)FROM
(SELECT o.NegotiatedPrice AS price
UNION ALL SELECT o.SuggestedPrice) d)
AS MaxPrice
FROM [Order] o
SQL Server 2012 introduit IIF
:
SELECT
o.OrderId,
IIF( ISNULL( o.NegotiatedPrice, 0 ) > ISNULL( o.SuggestedPrice, 0 ),
o.NegotiatedPrice,
o.SuggestedPrice
)
FROM
Order o
manipulation NULLs est recommandé lors de l'utilisation de IIF
, parce qu'un NULL
de chaque côté de votre boolean_expression
entraînera IIF
de retourner le false_value
(par opposition à NULL
).
je voudrais aller avec la solution fournie par kcrumley Il suffit de le modifier légèrement pour gérer NULLs
create function dbo.HigherArgumentOrNull(@val1 int, @val2 int)
returns int
as
begin
if @val1 >= @val2
return @val1
if @val1 < @val2
return @val2
return NULL
end
EDIT Modifié après commentaire de marque . Comme il l'a correctement souligné dans 3 valued logic x > NULL ou x < NULL devrait toujours retourner NULL. En d'autres termes résultat inconnu.
C'est aussi simple que cela:
CREATE FUNCTION InlineMax
(
@p1 sql_variant,
@p2 sql_variant
) RETURNS sql_variant
AS
BEGIN
RETURN CASE
WHEN @p1 IS NULL AND @p2 IS NOT NULL THEN @p2
WHEN @p2 IS NULL AND @p1 IS NOT NULL THEN @p1
WHEN @p1 > @p2 THEN @p1
ELSE @p2 END
END;
Oups, je viens de poster un dupe de cette question ...
la réponse est, il n'y a pas de fonction intégrée comme le plus grand D'Oracle , mais vous pouvez obtenir un résultat similaire pour 2 colonnes avec un UDF, notez, l'utilisation de sql_variant est très important ici.
create table #t (a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
-- option 1 - A case statement
select case when a > b then a else b end
from #t
-- option 2 - A union statement
select a from #t where a >= b
union all
select b from #t where b > a
-- option 3 - A udf
create function dbo.GREATEST
(
@a as sql_variant,
@b as sql_variant
)
returns sql_variant
begin
declare @max sql_variant
if @a is null or @b is null return null
if @b > @a return @b
return @a
end
select dbo.GREATEST(a,b)
from #t
posté cette réponse:
create table #t (id int IDENTITY(1,1), a int, b int)
insert #t
select 1,2 union all
select 3,4 union all
select 5,2
select id, max(val)
from #t
unpivot (val for col in (a, b)) as unpvt
group by id
voici un exemple de CAs qui devrait traiter les nulls et qui fonctionnera avec les anciennes versions de MSSQL. Ceci est basé sur la fonction inline dans un des exemples courants:
case
when a >= b then a
else isnull(b,a)
end
Je ne le ferais probablement pas de cette façon, car c'est moins efficace que les constructions de cas déjà mentionnées - à moins, peut-être, que vous ayez couvert les index pour les deux requêtes. De toute façon, c'est une technique utile pour des problèmes similaires:
SELECT OrderId, MAX(Price) as Price FROM (
SELECT o.OrderId, o.NegotiatedPrice as Price FROM Order o
UNION ALL
SELECT o.OrderId, o.SuggestedPrice as Price FROM Order o
) as A
GROUP BY OrderId
Voici une version IIF avec aucune manipulation (basée sur la réponse de Xin):
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a > b, a, b))
la logique est la suivante, si l'une ou l'autre des valeurs est nulle, renvoie celle qui n'est pas nulle (si les deux sont nulles, un NULL est renvoyé). Sinon de retour de la grande one.
même chose peut être fait pour MIN.
IIF(a IS NULL OR b IS NULL, ISNULL(a,b), IIF(a < b, a, b))
vous pouvez faire quelque chose comme ça:
select case when o.NegotiatedPrice > o.SuggestedPrice
then o.NegotiatedPrice
else o.SuggestedPrice
end
SELECT o.OrderID
CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN
o.NegotiatedPrice
ELSE
o.SuggestedPrice
END AS Price
CREATE FUNCTION [dbo].[fnMax] (@p1 INT, @p2 INT)
RETURNS INT
AS BEGIN
DECLARE @Result INT
SET @p2 = COALESCE(@p2, @p1)
SELECT
@Result = (
SELECT
CASE WHEN @p1 > @p2 THEN @p1
ELSE @p2
END
)
RETURN @Result
END
pour la réponse ci-dessus concernant les grands nombres, vous pouvez faire la multiplication avant l'addition/soustraction. C'est un peu plus volumineux mais ne nécessite pas de plâtre. (Je ne peux pas parler pour la vitesse, mais je suppose que c'est encore assez rapide)
sélectionnez 0.5 * (@val1 + @val2) + ABS (@val1 - @val2))
devient
SELECT @val1*0.5+@val2*0.5 + ABS (@val1*0.5 - @val2*0.5)
au moins une alternative si vous voulez éviter de jeter.
dans sa forme la plus simple...
CREATE FUNCTION fnGreatestInt (@Int1 int, @Int2 int )
RETURNS int
AS
BEGIN
IF @Int1 >= ISNULL(@Int2,@Int1)
RETURN @Int1
ELSE
RETURN @Int2
RETURN NULL --Never Hit
END
pour SQL Server 2012:
SELECT
o.OrderId,
IIF( o.NegotiatedPrice >= o.SuggestedPrice,
o.NegotiatedPrice,
ISNULL(o.SuggestedPrice, o.NegiatedPrice)
)
FROM
Order o
SELECT o.OrderId,
--MAX(o.NegotiatedPrice, o.SuggestedPrice)
(SELECT MAX(v) FROM (VALUES (o.NegotiatedPrice), (o.SuggestedPrice)) AS value(v)) as ChoosenPrice
FROM Order o
Voici la réponse de @Scott Langham avec une manipulation nulle simple:
SELECT
o.OrderId,
CASE WHEN (o.NegotiatedPrice > o.SuggestedPrice OR o.SuggestedPrice IS NULL)
THEN o.NegotiatedPrice
ELSE o.SuggestedPrice
END As MaxPrice
FROM Order o
select OrderId, (
select max([Price]) from (
select NegotiatedPrice [Price]
union all
select SuggestedPrice
) p
) from [Order]
Dans la Presto, vous pourriez utiliser
SELECT array_max(ARRAY[o.NegotiatedPrice, o.SuggestedPrice])