[SQL] SELECT IN SELECT 以及 Pagination 的使用 @ MySQL 5.6

使用 SQL 語法時，有時會需要從另一張 Table 取出清單，接著對清單內的資料做為基準再進行一次資料的擷取，直觀的想法大概是 SELECT something FROM Table1 WHERE id IN (SELECT id FROM Table2 WHERE ... )。

可惜上述語法是不行的 XD 要改成 JOIN 的做法：

SELECT something FROM Table1, (SELECT id FROM Table2) AS list WHERE Table1.id = list.id;

接著，偶爾會需要 pagination 的需求，加個 LIMIT 的用法，這時候又會想要回報全部有幾筆資料（對於一些搜尋引擎的設計，有些是採用預估的方式），以便前端可以估算有幾筆資料。

最簡單的解法是再用一個 SQL Query 去問 Table2 的 id 資料，但想要更快一點，就來試試 MySQL User-Defined Variables 吧!

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id;

如此一來，結果都會有個 total 筆數跟著，雖然仍不夠好，但也不錯啦 XD  而搭配 LIMIT OFFSET,COUNT 時，total 的資訊是來自掃 Table2 的資料，所以也能正常顯示：

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id LIMIT 0,10;