現有表A
ID QTY MARK
1 2 kl
1 3 klk
1 5 kl
2 2 klk
2 6 kl
3 8 kl
3 0 fgf 現在想按ID分組,取QTY最小的那條記錄,如何寫這樣SQL呢
得到結果應是1 2 kl
2 2 klk
3 0 fgf
ID QTY MARK
1 2 kl
1 3 klk
1 5 kl
2 2 klk
2 6 kl
3 8 kl
3 0 fgf 現在想按ID分組,取QTY最小的那條記錄,如何寫這樣SQL呢
得到結果應是1 2 kl
2 2 klk
3 0 fgf
from a join
(select id,min(qty) as qty from A group by id)b on a.id=b.id and a.qty=b.qty
where not exists (select 1 from a where id =t.id and qty> t.qty)
insert into @tb select 1,2,'kl'
insert into @tb select 1,3,'klk'
insert into @tb select 1,5,'kl'
insert into @tb select 2,2,'klk'
insert into @tb select 2,6,'kl'
insert into @tb select 3,8,'kl'
insert into @tb select 3,0,'fgf'select * from @tb a where
not exists(select 1 from @tb b where a.id=b.id and a.qty>b.qty)
如果表中的記錄有很多,比如上百萬條,而且字段很多(目前有54個),
那類似 on a.id=b.id and a.qty=b.qty
不是要寫很多?
有沒有更好的辦法呢
select *
from table a
where not exists(select 1 from table where a.id=id and a.qty>qty)