主页 / dba / 问题 / 239391
Accepted
klor
Asked: 2019-05-30 12:36:53 +0800 CST

SQL查询列出所有学生用户，添加列以显示谁至少登录过一次

  • 772

我有一个查询，其中列出了这些用户，即学生：

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
mysql join

2 个回答

  1. 看起来您试图查看 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
 )
    • 1
  2. Best Answer

    这可以以一种简单且不言自明的方式进行，无需子选择和/或联合：

    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
    • 1

相关问题