作者: tn00386 時間: 2015-10-20 10:00 標題: SQL能判斷重複資料只顯示最新日期的data嗎?
本帖最後由 tn00386 於 2015-10-20 10:28 編輯
小弟在學習使用Openoffice Base做資料庫在查詢功能於上問題:
例:
- Code id Price Date
- Q048 $498 10/08/2015
- Q102 $1588 10/08/2015
- T341 $55 09/08/2015
- Q048 $488 20/10/2015
如果"Code id"相同，能自動顯示最新日期的價錢嗎?
希望最後輸出:
- Code id Price Date
- Q102 $1588 10/08/2015
- T341 $55 09/08/2015
- Q048 $488 20/10/2015
作者: chriso 時間: 2015-10-20 11:03
select a.* from table a, (select codeid, MAX(date) as date from table group by codeid) b where a.codeid = b.codeid and a.date = b.date
作者: twaiho2003 時間: 2015-10-20 11:27
SELECT max(Date),Price,Code_ID from THISTABLE group by Code_ID
作者: gakko 時間: 2015-10-20 12:39
本帖最後由 gakko 於 2015-10-20 12:40 編輯
Where code=id Order by 日期 limit 1
作者: max918 時間: 2015-10-21 12:23
SELECT max(Date),Price,Code_ID from THISTABLE group by Code_ID
twaiho2003 發表於 2015-10-20 11:27
效率呢個比較好
作者: masseffect 時間: 2015-10-21 13:12
效率呢個比較好
max918 發表於 2015-10-21 12:23
但個price可能係錯嘅
作者: 7h1r733n 時間: 2015-10-21 17:07
但個price可能係錯嘅
masseffect 發表於 2015-10-21 13:12
所以話寫SQL真係一門藝術.. :D
作者: 7h1r733n 時間: 2015-10-21 17:21
唔知openoffice base可唔可以咁寫.. 不過mysql就可以
- SELECT b.* FROM (SELECT code_id, max(date) date from tablename group by code_id ) a INNER JOIN tablename b USING (code_id, date);
作者: twaiho2003 時間: 2015-10-21 18:36
回覆 6# masseffect
點解有可能係錯既?
作者: java2 時間: 2015-10-21 18:55
咁寫應該唔得
效率呢個比較好
max918 發表於 2015-10-21 12:23
作者: java2 時間: 2015-10-21 18:58
唔得咪用貼2 寫法, 其實都係一樣的做法, 先找出 max(date) 的 codeid, 再join 返其他結果, 只係寫法不同.
唔知openoffice base可唔可以咁寫.. 不過mysql就可以
7h1r733n 發表於 2015-10-21 17:21
作者: masseffect 時間: 2015-10-21 19:01
回覆 masseffect
點解有可能係錯既?
twaiho2003 發表於 2015-10-21 18:36
你嗰句project 左啲唔喺group by 入面嘅嘢field，standard sql唔support
就算係support 嘅，好似MYSQL，佢都係喺嗰group入面咁多條record是但揀一條嚟用
以上面個例子，group by code_id，但有兩條同code_id但唔同price 嘅record，最後select出嚟可以係$488，又可以係$498
作者: twaiho2003 時間: 2015-10-21 19:09
回覆 12# masseffect
我打左max(date), 我用mysql試左係出最新果個record
作者: masseffect 時間: 2015-10-21 19:16
本帖最後由 masseffect 於 2015-10-21 19:18 編輯
回覆 masseffect
我打左max(date), 我用mysql試左係出最新果個record
twaiho2003 發表於 2015-10-21 19:09
唔出奇，佢是但揀一條揀岩左
但個DB 唔會保證以後都係岩
因為你根本無話俾佢聽要揀邊條，max(date)只係projection，唔會指定佢揀邊條
你條sql只係project左group 入面date 最大嘅value 加埋group入面任意一條record嘅其他field出嚟
作者: 7h1r733n 時間: 2015-10-21 19:20
唔得咪用貼2 寫法, 其實都係一樣的做法, 先找出 max(date) 的 codeid, 再join 返其他結果, 只係寫法不同.
...
java2 發表於 2015-10-21 18:58
我只係想講.. 同一個結果可以有唔同做法.. :D
作者: Kin_2012 時間: 2015-10-21 21:18
一見呢種問題就諗起 ....不過 Openoffice base 應該唔支援
max(date) over (partition by code_id)
作者: LoneGumMan 時間: 2015-10-21 21:40
本帖最後由 LoneGumMan 於 2015-10-21 21:42 編輯
--- delete ---
作者: tn00386 時間: 2015-10-22 11:12
喔!完來唔支援，謝謝各位師兄教路
作者: henrywho 時間: 2015-10-22 18:45
MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次
作者: xeon0541 時間: 2015-10-22 23:09
本帖最後由 xeon0541 於 2015-10-22 23:14 編輯
MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次
henrywho 發表於 2015-10-22 18:45
you are wrong, the performance is significantly improved once the index properly applied
作者: henrywho 時間: 2015-10-23 08:12
you are wrong, the performance is significantly improved once the index properly applied
xeon0541 發表於 2015-10-22 23:09
Don't be afraid of full table scan. Look at and look into the actual SQL execution plans. You will find that there are cases that you refer the database doing full table scans instead of repeated index scans.
作者: xeon0541 時間: 2015-10-23 10:49
本帖最後由 xeon0541 於 2015-10-23 12:17 編輯
select max(patient_id) from patient_history
where record_date <= to_date(:p_cur_date, 'ddmmyyyy')
and patient_id = :pid);
the execution plan for above sql
Sort(AGGREGATE)
FIRST ROW
INDEX (RANGE SCAN(MIN/MAX)
access predicates
AND
PATEINT_ID=TO_NUMBER(:PID)
PRECORD_DATE<=TO_DATE(:P_CUR_DATE,'DDMMYYYY')
The index of PRECORD_DATE column is used.
Because the optimizer of database don't know your business logic, it always used wrong index or performing full scan table. That's why SQL performance tuning is needed.
作者: henrywho 時間: 2015-10-23 19:15
select max(patient_id) from patient_history
where record_date
xeon0541 發表於 2015-10-23 10:49
Try not to focus on the sub-query only .....
作者: snoopy11hk 時間: 2015-10-23 20:13
Don't be afraid of full table scan. Look at and look into the actual SQL execution plans. You wi ...
henrywho 發表於 2015-10-23 08:12
see if u need to scan through small portion or large portion of data (0.1% or 20%+)
in this case should be full table scan faster than nested loop
and
analytics will be faster (1 full table scan)
vs
1 full table scan + (1+) nested loop join
作者: xeon0541 時間: 2015-10-23 20:51
本帖最後由 xeon0541 於 2015-10-23 20:56 編輯
Try not to focus on the sub-query only .....
henrywho 發表於 2015-10-23 19:15
select max(patient_id) from patient_history
where record_date <= to_date(:p_cur_date, 'ddmmyyyy')
and patient_id = :pid);
this is not sub query ,just simple sql only.
作者: henrywho 時間: 2015-10-23 20:59
this is not sub query ,just simple sql only.
xeon0541 發表於 2015-10-23 20:51
We are talking about queries like:
select a.* from table a, (select codeid, MAX(date) as date from table group by codeid) b where a.codeid = b.codeid and a.date = b.date
作者: xeon0541 時間: 2015-10-23 21:30
本帖最後由 xeon0541 於 2015-10-23 21:36 編輯
you mentioned "MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次" I just follow the reply. moreover your sql don't need perform full table scan.
作者: henrywho 時間: 2015-10-24 00:44
moreover your sql don't need perform full table scan
xeon0541 發表於 2015-10-23 21:30
what is the execution plan?
作者: CT333 時間: 2015-10-25 20:04
提示: 作者被禁止或刪除 內容自動屏蔽
作者: snoopy11hk 時間: 2015-10-25 22:24
you mentioned "MySQL 同 Oracle 可以寫 analytic function, 只需 full-table scan 一次" I just follow t ...
xeon0541 發表於 2015-10-23 21:30
ur index seems not good enough
should using id instead if it is id selection
作者: xeon0541 時間: 2015-10-26 23:52
本帖最後由 xeon0541 於 2015-10-26 23:55 編輯
ur index seems not good enough
should using id instead if it is id selection
snoopy11hk 發表於 2015-10-25 22:24
try to review again before given some comments.
tips: if use id must be slower than search by date.
作者: henrywho 時間: 2015-10-28 20:45
try to review again before given some comments.
xeon0541 發表於 2015-10-26 23:52
I too, we too
作者: java2 時間: 2015-10-29 22:27
run到就算啦...慢唔慢比上頭嘈先算
CT333 發表於 2015-10-25 20:04