mysql - 是否可以将表与其自身连接

此查询的结果是否能够在不相乘的情况下连接。

Select StudID, StudName, StudYear from tbl_students where StudYear='Third Year';
Select StudID, StudName, StudYear from tbl_students where StudYear='Second Year';
Select StudID, StudName, StudYear from tbl_students where StudYear='First Year';

或者，这些查询的“结果”是否可能与9列联接，9列分别表示第三年的前3列、第二年的第2 3列和第一年的最后3列。
谢谢你的回答。
结果我想到的是
StudentID | StudentName | StudentYear
-----------------------------------------
1 |食物1 | 1
-----------------------------------------
2 |食物2 | 1
-----------------------------------------
3 |食物3 | 2
-----------------------------------------
4 |食物4 | 2
-----------------------------------------
5 |食物5 | 2
-----------------------------------------
6 |食物6 | 3
-----------------------------------------
StudentID-1 | StudentName-1 | StudentYear-1 | StudentID-2 | StudentName-2 | StudentYear-2 | StudentID-3 | StudentName-3 | StudentYear-3
----------------------------------------------------------------------------------------------------------------------------------------------------
1 | foo1 | 1 | 3 | foo3 | 2 | 6 | foo6 | 3
----------------------------------------------------------------------------------------------------------------------------------------------------
2 | foo2 | 1 | 4 | foo4 | 2 | ||
----------------------------------------------------------------------------------------------------------------------------------------------------
|| | 5 |食物| 2 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------

最佳答案

为了达到您的要求，您必须有某种id类型的东西加入，所以使用行号否则您将得到一对多对多的结果
在SQL Server中工作。所以在MYSQL中试试吧。

CREATE TABLE #STUDENT(ID INT, NAME VARCHAR(10), STUDENT_YEAR INT);
INSERT INTO #STUDENT VALUES(1,'FOO1',1);
INSERT INTO #STUDENT VALUES(2,'FOO2',1);
INSERT INTO #STUDENT VALUES(3,'FOO3',2);
INSERT INTO #STUDENT VALUES(4,'FOO4',2);
INSERT INTO #STUDENT VALUES(5,'FOO5',2);
INSERT INTO #STUDENT VALUES(6,'FOO6',3);


SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY S1.ID ASC) RN,
        S1.ID AS [ID_1], S1.NAME AS [NAME_1],S1.STUDENT_YEAR AS [YEAR_1]
    FROM #STUDENT S1
    where s1.STUDENT_YEAR=1
) T1
FULL JOIN (
    SELECT
        ROW_NUMBER() OVER (ORDER BY S2.ID ASC) RN,
        S2.ID AS [ID_2], S2.NAME AS [NAME_2],S2.STUDENT_YEAR AS [YEAR_2]
    FROM #STUDENT S2
    where s2.STUDENT_YEAR=2
) T2 ON T1.RN = T2.RN
FULL JOIN (
    SELECT
        ROW_NUMBER() OVER (ORDER BY S3.ID ASC) RN,
        S3.ID AS [ID_3], S3.NAME AS [NAME_3],S3.STUDENT_YEAR AS [YEAR_3]
    FROM #STUDENT S3
    where s3.STUDENT_YEAR=3
) T3 ON T1.RN = T3.RN

注意：#Student表示它是一个临时表。

关于mysql - 是否可以将表与其自身连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32798785/