JUF學習紀錄本: 【程式生活分享】解IThome的問題求助

這位同業的大大問了一個關於Sql的問題，剛用餐時，邊看邊吃，就一時興起。

正解頂置：

select m.hos_name, last(i.hos_img_url),last(i.hos_img_upload_date)
from hos_main m,hos_image i
where m.hos_id like i.hos_id
group by m.hos_name
order by last(i.hos_img_upload_date) desc;

沒有使用last功能的另解(給MySQL用)

select m.hos_name,i.hos_img_url,i.hos_img_upload_date
from hos_main m,
(
select hos_id,hos_img_url,hos_img_upload_date
from hos_image i
where i.hos_img_upload_date=
(
select max(hos_img_upload_date)
from hos_image
where hos_id=i.hos_id
group by hos_id
)
) i
where m.hos_id = i.hos_id
order by i.hos_img_upload_date desc;

ACCESS直接拉出來的語法，請參閱。

SELECT hos_main.hos_name, Last(hos_image.hos_img_url) AS hos_img_url之最後一筆, Last(hos_image.hos_img_upload_date) AS hos_img_upload_date之最後一筆
FROM hos_image INNER JOIN hos_main ON hos_image.hos_id = hos_main.hos_id
GROUP BY hos_main.hos_name
ORDER BY Last(hos_image.hos_img_upload_date) DESC;

下面是過程

我回答如下：

雖然各位大大已經有了正確的詳解

但是基於小弟的鑽牛角尖(無聊的一股想解題的衝勁Orz)

在這分享一下我想出來的SQL語法

[code]
select m.hos_name, max(i.hos_img_url),max(i.hos_img_upload_date)
from hos_main m,hos_image i
where m.hos_id like i.hos_id
group by m.hos_name
order by max(i.hos_img_upload_date) desc;
[/code]
這是小弟用手邊的accessDB測試用的模型(抱歉>< 懶得架Mysql測..其實很久沒用mysql...Orz)
表1

表2

我先做了兩表連結用法如inner join得到查詢1，查詢1是用來驗證我最後的答案是否是我預期想要的答案
查詢1

最後我用下列查詢語法得到我想要的答案查詢2
[code]
select m.hos_name, max(i.hos_img_url),max(i.hos_img_upload_date)
from hos_main m,hos_image i
where m.hos_id like i.hos_id
group by m.hos_name
order by max(i.hos_img_upload_date) desc;
[/code]
查詢2(Done)

最後歸納一下我的語法的優點，請各位先進指導XD
1.執行查詢時兩張表各scan一次，複雜度O(1)，所以效能比較好(I guess!?)
2.圖片欄位可以接受不同檔名的檔案，不會讓查詢因為上傳圖片檔名不同而出現重複資料。

隱約中看見Bug.....哀..

將max改成用last，最新的資料，bug就從心中移除了...@@

最後語法

select m.hos_name, last(i.hos_img_url),last(i.hos_img_upload_date)
from hos_main m,hos_image i
where m.hos_id like i.hos_id
group by m.hos_name
order by last(i.hos_img_upload_date) desc;

附上小畫家所提供的最後解(希望真的是最後解...><)

最後我發現mysql沒有last指令
所以只能用子查詢><
下面的查詢，我做了一些新增，與上面的表內容資料有不一致的地方，稍做一下說明：
我將表hos_main新增兩筆資料作驗證用
因為"C:\Intel\Logs\11.jpg"才是最後一筆資料
如果查詢出現欄位"C:\Intel\Logs\12.jpg"是最後一筆資料，就是語法錯誤
出現"C:\Intel\Logs\11.jpg"則成功

不使用last的方式查詢
step1:先將最後更新的欄位從hos_image表取出

select hos_id,hos_img_url,hos_img_upload_date
from hos_image i
where i.hos_img_upload_date=
(
select max(hos_img_upload_date)
from hos_image
where hos_id=i.hos_id
group by hos_id
) ;

step2:合併hos_image和hos_main兩張表

select m.hos_name,i.hos_img_url,i.hos_img_upload_date
from hos_main m,
前面那個查詢子句 i
where m.hos_id = i.hos_id
order by i.hos_img_upload_date desc;

檢視全部程式碼

select m.hos_name,i.hos_img_url,i.hos_img_upload_date
from hos_main m,
(
select hos_id,hos_img_url,hos_img_upload_date
from hos_image i
where i.hos_img_upload_date=
(
select max(hos_img_upload_date)
from hos_image
where hos_id=i.hos_id
group by hos_id
)
) i
where m.hos_id = i.hos_id
order by i.hos_img_upload_date desc;

最後心得：
程式語法還是很長，不容易讀，不知道使用last功能的效能，是不是跟這個語法寫出來的狀況一樣差
用了2個子查詢，時間複雜度O(N*N)，如果只是用在一般資料維護，效能影響不大，如果是用在給大眾查詢，那人一多系統的負擔會非常大。

ITHome裡的antijava先進使用了製作一個虛擬表，再將原本的兩張表比對，效能一樣O(N*N)
這邊借用其語法做為未來寫語法的思考方向，改變思考角度。

SELECT *
FROM hos_main m, hos_image i,
(SELECT hos_id, MAX( hos_img_upload_date ) AS MaxDateTime
FROM hos_image
GROUP BY hos_id) x
WHERE m.hos_id=i.hos_id
AND i.hos_id=x.hos_id
AND i.hos_img_upload_date=x.MaxDateTime

恩~針對這種類型的查詢
如果真的考慮到效能問題，可以使用建View的方式，不要讓使用者直接查詢資料庫，效能一級棒XD

好吧~剛剛又發現View裡面不能有子查詢~所以View不能用，那退而求其次，固定一小時做一張表格出來供多人查詢用。

CREATE TABLE mytable (select ....上述查詢語法); 

JUF學習紀錄本

2013年5月1日星期三

【程式生活分享】解IThome的問題求助

沒有留言:

張貼留言

2013年5月1日 星期三

【程式生活分享】解IThome的問題求助

沒有留言:

張貼留言

2013年5月1日星期三