0

## 請問如何從多筆資料中獲取最小值?

id        val1           val2
1             777              184
2             5616              205
3              3                20

### 2 個回答

2

iT邦大師 1 級 ‧ 2018-03-08 15:36:53

``````--建立測試資料
create table #Tem_Table ([ID] int,val1 int,val2 int );
insert into #Tem_Table ([ID],val1,val2) values
(1,777,184)
,(2,5616,205)
,(3,3,20)
;
select * from #Tem_Table;
``````
``````--方法一:SQL版本
with Tem_Table as (
select id
,case when val2>val1 then val1  --藉由比值把兩個欄位合併成一個最小值欄位
else val2
end as min_value
from #Tem_Table
)
select * from Tem_Table T100
where T100.min_value = (select min(min_value) from Tem_Table ); --篩選出最小值
``````

``````--方法二:T-SQL版本
declare @最小值 int ,@val1欄最小值 int , @val2欄最小值 int
select @val1欄最小值 = (select min(val1) val from #Tem_Table)
select @val2欄最小值 = (select min(val2) val from #Tem_Table)
IF( @val1欄最小值 < @val2欄最小值 )
BEGIN
select id,val1 min_value from #Tem_Table T100
where T100.val1 = @val1欄最小值;
END	;
IF( @val2欄最小值 < @val1欄最小值 )
BEGIN
select id,val2 min_value from #Tem_Table T100
where T100.val2 = @val2欄最小值;
END	;
``````

0.0032945 * 4 = 0.013178

vbe02607 iT邦新手 5 級 ‧ 2018-03-08 15:52:52 檢舉

vbe02607 iT邦新手 5 級 ‧ 2018-03-08 15:58:56 檢舉

1

iT邦新手 1 級 ‧ 2018-03-08 15:17:40

``````SELECT TOP(1) *
FROM myTable
ORDER BY val1

--只想取id和val1的話就把 * 換成欄位名稱
SELECT TOP(1) id,val1
FROM myTable
ORDER BY val1
``````

``````SELECT *
FROM myTable
WHERE val1 IN (SELECT MIN(val1) FROM myTable)
``````

vbe02607 iT邦新手 5 級 ‧ 2018-03-08 15:52:21 檢舉