mysql - 用于组合多个查询输出的存储过程

Question

我有 4 个数据库表，

session :
------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  |
------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 |
| 2       | 2612fedcf78d |  type-2     |  1479111234 |
------------------------------------------------------

user :
----------------------------------
| userid  | name   |  user_type  |
----------------------------------
| 1       | Name-1 |  teacher    |
| 2       | Name-2 |  student    |
----------------------------------

teacher :
---------------------------------------------
| userid  | details-col1   |  details-col2  |
---------------------------------------------
| 1       | Value-1        |  Value-1       |
| 2       | Value-2        |  Value-2       |
---------------------------------------------

student :
---------------------------------------------
| userid  | student-col1   |  student-col2  |
---------------------------------------------
| 1       | Value-1        |  Value-1       |
| 2       | Value-2        |  Value-2       |
---------------------------------------------

现在，我的要求是:

1. 我想要“session”表中的一行将“session_id”字段与存储过程参数中提供的session_id值相匹配。

2. 此外，我希望 "user" 表中有一行将其 "userid" 列值与 session 表查询输出中的 userid 值(指定上述观点:1)。

3. 现在，根据“user”表查询输出中的“user_type”列中存储的值，应在相应的数据库表上触发查询。

   例如如果"user_type"列中的值为"teacher"，则查询"teacher"表。对于值“学生”也是如此。

通过在第二个查询中提供一个查询的输出，我可以在 3 个不同的查询中完成上述要求。但是，我想仅通过一个查询/存储过程来完成它。

请考虑每个数据库表“用户”、“教师”、“学生” 可能包含超过 1,000,000 行它。并且表“教师” 和“学生” 都有超过 8 个唯一列，并且可能会随时间而变化。

实现此要求的最优化存储过程是什么？

Expected Output :

For Teacher :
---------------------------------------------------------------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  | name   |  user_type  | details-col1   |  details-col2  |
---------------------------------------------------------------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 | Name-1 |  teacher    | Value-1        |  Value-1       |
---------------------------------------------------------------------------------------------------------------

For Student : 
---------------------------------------------------------------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  | name   |  user_type  | student-col1   |  student-col2  |
---------------------------------------------------------------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 | Name-1 |  teacher    | Value-1        |  Value-1       |
---------------------------------------------------------------------------------------------------------------

Answer 1

如果您需要选择不同的列，可以在存储过程中执行此操作:

SELECT u.user_type, se.session_id, se.sess_type, se.sess_time, u.userid, u.name, u.user_type 
    INTO @user_type, @session_id, @sess_type, @sess_time, @userid, @name, @type
FROM user AS u
JOIN session AS se ON se.userid = u.userid
WHERE se.session_id = @session_id_param;

IF @user_type = 'student'
THEN
    SELECT @userid AS userid, @session_id AS session_id, @sess_type AS sess_type, @sess_time AS sess_time, @name AS name, @type AS user_type, s.student_col1, s.student_col2
    FROM student AS s
    WHERE s.userid = @userid
ELSE
    SELECT @userid AS userid, @session_id AS session_id, @sess_type AS sess_type, @sess_time AS sess_time, , @name AS name, @type AS user_type, t.details_col1, t.details_col2
    FROM teacher AS t
    WHERE t.userid = @userid
END IF;