mysql - How to I get the minimum and maximum value of a table and create another table with it?

Question

I have the following tables.

STUDENTS
    +------+----------------+
    | stuID| StuStatus      |
    +------+----------------+
    | 1001 | 1              |
    | 1001 | 3              |
    | 1002 | 1              |
    | 1003 | 6              |
    | 1004 | 1              |
    | 1002 | 4              |
    | 1001 | 6              |
    | 1005 | 1              |
    | 1005 | 4              |
    +------+----------------+
DESCRIPTION

    +-------+--------------------------+
    | statID| StatusDesc              |
    +-------+-------------------------+
    |    1  | Application Submitted   |
    |    2  | Application Accepted    |
    |    3  | Application Pending     |
    |    4  | Application Resubmitted |
    |    5  | Application Denied      |
    =+------+-------------------------+

How do i make use of inner join to create a table that shows for each student, their start point and end point, in words?

This is the logic flow I have in my mind right now:

1. Create two tables, both with the columns stuID and stuStatus. The tables will demonstrate each student's min and max stuStatus respectively.

2. Create a new table using inner join, where I join DESCRIPTION to my table 2 and 3.

However, I am not clear of how I should go about doing it and would love some help.

Thank you.

标签： mysqlinner-join

Answer 1

您应该首先确定每个学生的最低和最高状态，然后将这些数字转换为相应的描述。
像这样的东西：

SELECT stu_stat.stuID,
       min_desc.statusDescr AS MinDescription,
       max_desc.statusDescr AS MaxDescription
FROM
(
SELECT StuID,
       MIN(StuStatus) AS MinStatus,
       MAX(StuStatus) AS MaxStatus
FROM   students
GROUP BY StuID
) AS stu_stat
JOIN   descr_table AS min_desc
  ON   stu_stat.MinStatus = min_desc.StatID
JOIN   descr_table AS max_desc
  ON   stu_stat.MaxStatus = max_desc.StatID;

虽然我没试过...