iT邦幫忙

0

mysql join 問題

關於 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
)

2 個回答

0
攻城屍
iT邦新手 5 級 ‧ 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
看更多先前的回應...收起先前的回應...

果然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邦新手 5 級 ‧ 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

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

攻城屍 iT邦新手 5 級 ‧ 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),那效能當然會比較差。

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

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

用 MAX 是因為我看到你寫

order by created_at desc
limit 1

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

0
浩瀚星空
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語法因該不會是正確的答案。但提供給你一個思維出來。

就兩張表,關係是一對多,想知道資料少的那張表,哪些是在join的那張表有資料的,哪些是沒有的,就這樣

我要發表回答

立即登入回答