這樣子:我有一表如下:欄位(訂單號,修改日期,修改時間,訂單數量)
其中,訂單號,修改日期,修改時間加起來為關鍵字我想求所有訂單號修改日期最小的訂單數量(有這種情況:個改日期一樣,修改時間不一樣情況,這時我想求修改時間最小的數據)
select出來 訂單號,修改日期,修改時間,訂單數量
其中,訂單號,修改日期,修改時間加起來為關鍵字我想求所有訂單號修改日期最小的訂單數量(有這種情況:個改日期一樣,修改時間不一樣情況,這時我想求修改時間最小的數據)
select出來 訂單號,修改日期,修改時間,訂單數量
( Select * from 欄位 Where 訂單號=t.訂單號 and
修改日期=t.修改日期 and 修改時間<t.修改時間 )
(select 修改日期,min(修改时间) 修改时间 from table t1,
(select min(修改日期) 修改日期
from table) t where t1.修改日期=t.修改日期) tt
where t0.修改日期=tt.修改日期 and t0.修改时间=tt.修改时间。
字段分别是:
訂單號 ID
修改日期 modifydate
修改時間 modifytime
訂單數量 amount
SQL :
select ID,modifydate,modifytime,amount from (
select ID, min(modifydate) over (partition by ID order by modifydate, modifytime ) as modifydate
,min(modifytime) over (partition by ID order by modifydate, modifytime ) as modifytime
, min(amount) over (partition by ID order by modifydate, modifytime ) as amount
, row_number() over (partition by ID order by modifydate, modifytime ) as rn
from lanwei ) where rn =1;
MIN(訂單數量) KEEP (DENSE_RANK FIRST ORDER BY 修改日期,修改時間) 訂單數量,
FROM employees
GROUP BY 訂單號
Select * from 欄位 t where not exists
( Select * from 欄位 Where 訂單號=t.訂單號 and
修改日期<t.修改日期 and 修改時間<t.修改時間 )
Select * from 欄位 t where not exists
( Select * from 欄位 Where 訂單號=t.訂單號 and
修改日期<t.修改日期 and 修改時間<t.修改時間 )
------
不行啦,也是重複了
from 欄位
order by 修改日期,修改時間再取第一个就行了
select ID, min(modifydate) over (partition by ID order by modifydate, modifytime ) as modifydate
,min(modifytime) over (partition by ID order by modifydate, modifytime ) as modifytime
, min(amount) over (partition by ID order by modifydate, modifytime ) as amount
, row_number() over (partition by ID order by modifydate, modifytime ) as rn
from lanwei ) where rn =1;
------
好像可以,不過不知道有沒有更簡便的
ORDERID RIQI SHIJIAN SHULIANG
---------- ----------- ----------- ----------
1 9/23/2007 02:11:10 50
2 10/12/2007 02:12:20 50
3 10/12/2007 13:10:30 50
4 3/6/2007 18:06:23 50
5 3/6/2007 22:10:50 50
6 8/8/2007 22:10:50 506 rows selectedSQL> select *
2 from (
3 select tt.*,
4 row_number() over(order by riqi,shijian) as rn
5 from tt
6 )zz
7 where rn = 1; ORDERID RIQI SHIJIAN SHULIANG RN
---------- ----------- ----------- ---------- ----------
4 3/6/2007 18:06:23 50 1
----------------------
謝謝,ORDERID是可以重複的
字段分别是:
訂單號 ID
修改日期 modifydate
修改時間 modifytime
訂單數量 amount
SQL :
select ID,modifydate,modifytime,amount from (
select ID, min(modifydate) over (partition by ID order by modifydate, modifytime ) as modifydate
,min(modifytime) over (partition by ID order by modifydate, modifytime ) as modifytime
, min(amount) over (partition by ID order by modifydate, modifytime ) as amount
, row_number() over (partition by ID order by modifydate, modifytime ) as rn
from lanwei ) where rn =1;
----------------OK