SQL查詢列出所有學生使用者，添加列以顯示誰至少登錄過一次

我有一個查詢，其中列出了這些使用者，即學生：

SELECT  *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5  /* is student */

我有一個帶有使用者 ID 的表，他們至少登錄過一次：

SELECT * FROM mdl_user_lastaccess   

我需要列出所有學生使用者，並有一個新列，顯示使用者是否登錄。已登錄：值 1，未登錄：值 0。

我想出了這個查詢，但登錄列始終為 1。缺少 0 值：

SELECT  *, (SELECT IF ((SELECT count(*) as c FROM mdl_user as u, mdl_user_lastaccess as ula WHERE u.id = ula.userid), 1, 0)) as loggedin
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5   

知道如何解決嗎？

錯誤的解決方案：

select u.*,
      case
      when ul.userid is null then 0
      else 1
      end loggedin
 from mdl_user u
 join mdl_role_assignments ra
   on u.id = ra.userid
 left join (
    select ula.userid
      from mdl_user_lastaccess ula
     group by ula.userid
) ul
on u.id = ul.userid
WHERE 
ra.roleid = 5

ORDER BY loggedin DESC

結果 455 行，其中包含重複項。

好的解決方案：

SELECT DISTINCT u.*, IF(ul.userid IS NULL, 0, 1) AS loggedin, ul.courseid
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
LEFT JOIN (
   SELECT ula.userid, ula.courseid
   FROM mdl_user_lastaccess ula
   GROUP BY ula.userid
) ul ON u.id = ul.userid
WHERE ra.roleid = 5
ORDER BY loggedin DESC

我不得不使用 DISTINCT 來忽略重複項。

最終的好解決方案：

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS loggedin, la.courseid
 FROM mdl_user                 AS u
 JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
 LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
WHERE ra.roleid = 5           -- is student
GROUP BY u.id                 -- to collapse all sessions for certain user
ORDER BY loggedin DESC

這可以以一種簡單且不言自明的方式進行，無需子選擇和/或聯合：

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS logged_in
 FROM mdl_user                 AS u
 JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
 LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
WHERE ra.roleid = 5            -- is student
GROUP BY u.id                  -- to collapse all sessions for certain user
ORDER BY u.id

看起來您試圖查看 mdl_user_lastaccess 表中是否存在使用者 ID 的記錄，您可以通過左連接來實現，試試這個

select u.*,
      case
      when ul.userid is null then 0
      else 1
      end loggedin
 from mdl_user u
 join mdl_role_assignments ra
   on u.id = ra.userid
 left join (
    select ula.userid
      from mdl_user_lastaccess ula
     group by ula.userid
) ul
on u.id = ul.userid

另一種使用存在語句的方法

select u.*,
      1 as loggedin
 from mdl_user u
 join mdl_role_assignments ra
   on u.id = ra.userid
where exists (
   select 1
     from mdl_user_lastaccess ul
    where u.id = ul.userid
)

union

select u.*,
      0 as loggedin
 from mdl_user u
 join mdl_role_assignments ra
   on u.id = ra.userid
where not exists (
   select 1
     from mdl_user_lastaccess ul
    where u.id = ul.userid
)

引用自：https://dba.stackexchange.com/questions/239391