给出SQL Server 2005中的下表:
ID Col1 Col2 Col3 -- ---- ---- ---- 1 3 34 76 2 32 976 24 3 7 235 3 4 245 1 792
编写产生以下结果的查询的最佳方法是什么(即产生最终列的查询 - 包含每行的Col1,Col2和Col3中的minium值的列)？
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
更新:
为了澄清(正如我在演讲中所说),在真实场景中,数据库已正确规范化.这些"数组"列不在实际表中,而是在报表中所需的结果集中.新要求是报告还需要此MinValue列.我无法更改基础结果集,因此我期待T-SQL获得一个方便的"走出监狱卡".
我尝试了下面提到的CASE方法并且它有效,尽管它有点麻烦.它也比答案中说明的更复杂,因为你需要满足同一行中有两个最小值的事实.
无论如何,我想我会发布我目前的解决方案,鉴于我的限制,它的效果非常好.它使用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列要求),它是一个非常优雅的"走出监狱卡".
可能有很多方法可以实现这一目标.我的建议是使用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
SQL小提琴
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
在MySQL上，使用以下命令：
select least(col1, col2, col3) FROM yourtable
最好的方法实际上不是这样做 - 很奇怪人们坚持以一种需要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作为一个额外的键,根据您的需要.然后您的查询变得简单
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
您仍然可以使用分别处理单个"旧列"
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
在您的其他查询中.如果"旧柱"的数量增加,这也允许容易扩展.
这使你的查询,所以容易得多.一般准则我倾向于使用,如果你曾经有东西,看起来像在一个数据库行的一个数组,你可能做错事,应该考虑重组数据.
但是,如果由于某种原因您无法更改这些列,我建议使用插入和更新触发器,并添加另一个列,这些触发器设置为最小值select min(val) from tbl.这会将操作的"成本"从选择转移到它所属的更新/插入 - 我的经验中的大多数数据库表读取的频率远远超过写入,因此随着时间的推移,写入成本往往会更高效.
换句话说,最低为一排,当其他列的一个变化只改变,所以这就是当你在每一次选择时间(如果数据不改变被浪费)来计算它,而不是.然后你会得到一个像这样的表:
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
任何其他必须在where col = 2时间上做出决策的选项通常都是性能上的坏主意,因为数据只会在插入/更新时发生变化 - 添加另一列会占用更多的空间,并且插入和插入的速度会稍慢一些更新,但是可以多为选择快-首选的方法应该取决于你的重点有,但,如上所述,大多数表是只读远远往往比他们写的.
您可以使用"强力"方法进行扭曲:
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个colums,那么上面就变成了
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
京公网安备 11010802040832号 | 京ICP备19059560号-6 