mysql join 問題

mysql db

跟著鄉民看熱鬧 2019-05-31 10:24:48 ‧ 2655 瀏覽

關於 join with only one row from second table
嘗試下文第一個作法，但效率很差，請問有效率較好的方式嗎？
其他方式無法正常執行
另外想知道如果資料少的那個table，有哪幾筆資料，是join表中沒有資料的，
又應該怎麼查詢？
https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql

select * from users join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1
)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

攻城屍

iT邦新手 4 級 ‧ 2019-05-31 10:57:12

最佳解答

建議不要在 JOIN ON 中有運算的邏輯，這樣會讓效能非常低落
可以考慮使用以下方法

SELECT
    *
FROM
    users AS t1
INNER JOIN
(
    SELECT
        user_id,
        MAX(created_at) AS max_created_at
    FROM
        widgets
    GROUP BY
        user_id
) AS t2
ON
    t1.id = t2.user_id

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

跟著鄉民看熱鬧 iT邦高手 1 級 ‧ 2019-05-31 11:02:26 檢舉

果然sql高手很多，請問如果想列出沒資料的那幾筆該怎麼做？
另外請問有運算的邏輯是指什麼？
有嘗試以下做法，但效率很差

select a.id, a.field2 FROM test1 as a LEFT JOIN
(select id as i from test2) as t1
ON a.id=t1.i where t1.i IS NULL

攻城屍 iT邦新手 4 級 ‧ 2019-05-31 11:24:31 檢舉

照你原本的寫法會在 Join 的時後每筆資料都去跑一次運算邏輯比對，若資料有一千筆就要比對一千次，這樣效能當然很差

如果想列出沒資料的那幾筆可以改成

SELECT
    *
FROM
    users AS t1
LEFT JOIN
(
    SELECT DISTINCT
        user_id
    FROM
        widgets
) AS t2
ON
    t1.id = t2.user_id
WHERE
	t2.user_id IS NULL

跟著鄉民看熱鬧 iT邦高手 1 級 ‧ 2019-05-31 11:27:52 檢舉

你的作法不也是每次去MAX和GROUP BY？意思是join的查詢要跟外部的查詢做條件關聯，提高效率？雖然知道作法，但不是很了解你指的有運算的邏輯，是什麼定義？

攻城屍 iT邦新手 4 級 ‧ 2019-05-31 11:57:25 檢舉

如果 AB 表各有 100 筆資料，JOIN 的時候會依據你寫在 ON 的條件去比對 100*100 次

我的作法是先將大表變小表在去做 JOIN，先將 B 表根據 GROUP BY 條件篩選出我要的資料，B 表可能就剩下 10 筆，那這個時候我再去做 JOIN 就只要比對 100*10 次

如果是照原寫法會去比對 100*100 次，甚至將比對的條件設為 SELECT B 表，造成比對一次就要額外去消耗額外資源去篩選，而不是當下就可以知道結果(EX: t1.id = t2.user_id)，那效能當然會比較差。

跟著鄉民看熱鬧 iT邦高手 1 級 ‧ 2019-05-31 13:39:52 檢舉

簡單說就是先透過 GROUP BY 讓多筆的資料表筆數資料減少至和外部表相同，再與外部表關聯，原來如此，感謝說明
因為用了MAX，還以為和MAX的函數有關，測試了一下，拿掉似乎也是同樣的結果
果然是高手高手高高手

攻城屍 iT邦新手 4 級 ‧ 2019-05-31 13:46:30 檢舉

用 MAX 是因為我看到你寫

order by created_at desc
limit 1

所以我就照你的邏輯取最晚的 created_at

登入發表回應

㊣浩瀚星空㊣

iT邦大神 1 級 ‧ 2019-05-31 11:07:54

感覺你的子查尋有點搞錯了。
這樣子等於每一筆都要做一次子查尋。當然效能會很差。

我現在不太明白你做order by created_at desc的用意在哪。
如果你的widgets表是複合式資料。要取created_at最大值的對應id值
你可以改用如下的方式試試看。

SELECT * FROM users LEFT JOIN (
   SELECT userid,id FROM widgets
   order by created_at desc
   GROUP BY userid
) AS widgets on widgets.id = user.id

讓子查尋一次性處理。不要多次性。
不過說真的，我雖然是照著你的處理方式。但我目前是不太懂你的要求。
上面的sql語法有很多不合邏輯的地方。如id因該是唯一性的。但我是用userid來取得id的情況。
這樣會有矛盾的地方。只是在不了解你的需求情況下。我也不知道怎麼去幫你改。
主要是不太明白你widgets表的架構。

所以上面的sql語法因該不會是正確的答案。但提供給你一個思維出來。