因為實在...試不出來，想請教站上大大...
我需要同時抓兩個資料表，並希望結果是能顯示最後更新的資料，
資料表如下：
資料表hos_main
資料欄hos_id, hos_name
B表 hos_image
資料欄 hos_img_id, hos_id, hos_img_url, hos_img_upload_date
從A表中要取出 hos_name
從B表中要取出 hos_img_url
這樣的語法，可work
這樣的語法，也可work
可是...紅色指向那一列的資料，卻不是跟著右邊欄位連動的...
早上九點試到現在，脖子都硬了...
不知那位大大能指點一下迷津...謝謝您。
已邀請的邦友 {{ invite_list.length }}/5
我連題目都看不懂....
海綿寶寶，交給你了....
都看不懂...
sunallen提到：
早上九點試到現在，脖子都硬了...
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;
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;
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;
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;
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 ) ;
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;
<pre class="c" name="code">CREATE TABLE hos_main(
hos_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
hos_name VARCHAR(30) NOT NULL);
INSERT INTO hos_main(hos_name) VALUES
('我是天字一號房'), ('我是地字一號房'),
('我是玄字一號房'), ('我是黃字一號房');
CREATE TABLE hos_image(
hos_img_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
hos_id INT UNSIGNED NOT NULL,
hos_img_url VARCHAR(120),
hos_img_upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(hos_id) REFERENCES hos_main(hos_id));
INSERT INTO hos_image(hos_id, hos_img_url, hos_img_upload_date) VALUES
(1, "D:\\img\\天字一號房\\sky001.jpg", '2013-4-27 18:37:54'),
(1, "D:\\img\\天字一號房\\sky002.jpg", '2013-4-27 19:58:27'),
(2, "D:\\img\\地字一號房\\ground001.jpg", '2013-4-27 18:39:04'),
(2, "D:\\img\\地字一號房\\ground002.jpg", '2013-4-27 20:04:35'),
(3, "D:\\img\\玄字一號房\\dark001.jpg", '2013-4-27 21:25:24'),
(3, "D:\\img\\玄字一號房\\dark002.jpg", '2013-4-28 16:57:23'),
(4, "D:\\img\\黃字一號房\\yellow001.jpg", '2013-4-27 22:17:25'),
(4, "D:\\img\\黃字一號房\\yellow002.jpg", '2013-4-28 13:14:42');
-- 找出每一個房間最新更新圖形的房間名稱,時間與圖形url
SELECT m.hos_name, j.hos_img_url, i.mdate AS '我是最新的圖片更新時間'
FROM hos_main m
JOIN (SELECT hos_id, MAX(hos_img_upload_date) AS mdate
FROM hos_image
GROUP BY hos_id) i
ON m.hos_id = i.hos_id
JOIN hos_image j
ON i.mdate = j.hos_img_upload_date
ORDER BY m.hos_id;
+-----------------------+--------------------------------------+-----------------------------------+
| hos_name | hos_img_url | 我是最新的圖片更新時間 |
+-----------------------+--------------------------------------+-----------------------------------+
| 我是天字一號房 | D:\img\天字一號房\sky002.jpg | 2013-04-27 19:58:27 |
| 我是地字一號房 | D:\img\地字一號房\ground002.jpg | 2013-04-27 20:04:35 |
| 我是玄字一號房 | D:\img\玄字一號房\dark002.jpg | 2013-04-28 16:57:23 |
| 我是黃字一號房 | D:\img\黃字一號房\yellow002.jpg | 2013-04-28 13:14:42 |
+-----------------------+--------------------------------------+-----------------------------------+
SQL裡頭的MAX、MIN之類的統計函數，用途是抓取特定欄位中的最大或最小值
並不會影響其他欄位的排序，所以，MAX不應該用在這裡
把查詢改一下...把MAX變成條件，應該就會是你要的查詢結果了...
<pre class="c" name="code">
SELECT `hos_image_url`,MAX(`hos_image_upload_date`) AS `last_upload_date`,`hos_name`
FROM `hos_image`,`hos_main`
WHERE `hos_image`.`hos_image_upload_date`=MAX(`hos_image`.`hos_image_upload_date`)
AND `hos_main`.`hos_id`=`hos_image`.`hos_id`
比對hos_image_upload_date值等於最大值....
我用的是最笨的方法
就是「再加一個Table」
看看合不合用
<pre class="c" name="code">
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
謝謝 antijava 大大...
剛剛想到，用sub query應該可以更簡單一點：
<pre class="c" name="code">
SELECT * FROM (SELECT * FROM hos_image ORDER BY hos_img_upload_date DESC) a group by a.hos_id;
既然GROUP BY預設取第一筆，那我先把資料用hos_img_upload_date降冪排序就可以。不過我這樣做sub query基本上是full table scan，效率應該很差就是了
再把hos_main加上去，就可以用inner join取得hos_name：
<pre class="c" name="code">
SELECT b.hos_name, a.hos_img_url, a.hos_img_upload_date FROM (SELECT * FROM hos_image ORDER BY hos_img_upload_date DESC) a, hos_main b WHERE b.hos_id=a.hos_id GROUP BY a.hos_id;
上網找了一下，看起來沒有其他比較好的方法...另外，排成一行有點難閱讀，我重排一下：
<pre class="c" name="code">
SELECT
b.hos_name, a.hos_img_url, a.hos_img_upload_date
FROM
(SELECT * FROM hos_image ORDER BY hos_img_upload_date DESC) a,
hos_main b
WHERE
b.hos_id=a.hos_id
GROUP BY
a.hos_id;
fillano大大的答案更漂亮
fillano提到：
GROUP BY預設取第一筆
這應該是 MySQL 才這樣吧
今天才知道
hos_main 超過一筆又如何，反正只是要顯示最後更新的資料，而不是每一條ID最後更新的資料
xnchan提到：
而不是每一條ID最後更新的資料
建議你去看看最佳解答的範例資料再下結論
antijava提到：
建議你去看看最佳解答的範例資料再下結論
那就只需可上GROUP BY就可以了啦，用SUB QUERY速度不會變慢嗎?
<pre class="c" name="code">SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
ORDER BY
b.hos_img_upload_date DESC,a.hos_id
GROUP BY
a.hos_id
LIMIT
1;
抱歉，手快沒看清楚，應該是
<pre class="c" name="code">
SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
GROUP BY
a.hos_id
ORDER BY
b.hos_img_upload_date DESC,a.hos_id
LIMIT
1;
<pre class="c" name="code">
SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
GROUP BY
a.hos_id
ORDER BY
b.hos_img_upload_date DESC,a.hos_id
LIMIT
1;
結果如:
+-----------------------+--------------------------------------+
| hos_name | hos_img_url |
+-----------------------+--------------------------------------+
| 我是黃字一號房 | D:\img\黃字一號房\yellow001.jpg |
+-----------------------+--------------------------------------+
不是按時間排列嗎?為什麼搞這麼複雜，你們看這樣行不行:
<pre class="c" name="code">
SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
ORDER BY
UNIX_TIMESTAMP(b.hos_img_upload_date) DESC,a.hos_id
LIMIT
1;
把時間換成Unix 時間戳就可以排序了
不用換成Unix 時間戳也可以排序.
以下是使用敝人掰出來的範例,執行你上面的SQL Command的結果.
<pre class="c" name="code">
SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
ORDER BY
UNIX_TIMESTAMP(b.hos_img_upload_date) DESC,a.hos_id
LIMIT
1;
+-----------------------+------------------------------------+
| hos_name | hos_img_url |
+-----------------------+------------------------------------+
| 我是玄字一號房 | D:\img\玄字一號房\dark002.jpg |
+-----------------------+------------------------------------+
<pre class="c" name="code">
以下是換成Unix 時間戳的.
SELECT
a.hos_name, b.hos_img_url
FROM
hos_main a, hos_image b
WHERE
a.hos_id=b.hos_id
ORDER BY
b.hos_img_upload_date DESC,a.hos_id
LIMIT 1;
+-----------------------+------------------------------------+
| hos_name | hos_img_url |
+-----------------------+------------------------------------+
| 我是玄字一號房 | D:\img\玄字一號房\dark002.jpg |
+-----------------------+------------------------------------+
上面說明弄錯了,是不換成Unix 時間戳的.
轉成字串，還是可以排序的....
wiselou提到：
排序
夜市的烤肉串好像也可以...
iThome鐵人賽
看影片追技術