我有一个 teacher 表，还有一个 phone 表，其中链接是通过人员的 ID 进行的。当我查找每位教授的电话号码时，显示如下：
1
576(SELECT
T.ID_TEACHER,
P.PHONE,
P.NUMBER
FROM TEACHER T LEFT JOIN PHONES P
ON P.IDPERSON = T.ID_TEACHER)
| ID_TEACHER | 电话 | NUMBER |
|---|---|---|
| 1 | 1 | xxxxxxx |
| 1 | 2 | xxxxxxxx |
| 1 | 3 | xxxxxxx |
| 2 | 1 | xxxxxxx |
但是，我希望它显示如下：
| ID_TEACHER | 电话 | NUMBER | 电话 | NUMBER | 电话 | NUMBER |
|---|---|---|---|---|---|---|
| 1 | 1 | xxxxx | 2 | xxxxx | 3 | xxxxx |
| 2 | 1 | xxxxxxxxx |
我该怎么办？我正在使用MYSQL。我没有找到解决方案。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
收藏
您需要使用条件聚合：
尝试以下操作：
SELECT T.ID_TEACHER, MAX(CASE WHEN P.PHONE = 1 THEN P.PHONE ELSE NULL END) AS PHONE1, MAX(CASE WHEN P.PHONE = 1 THEN P.NUMBER ELSE NULL END) AS NUMBER1, MAX(CASE WHEN P.PHONE = 2 THEN P.PHONE ELSE NULL END) AS PHONE2, MAX(CASE WHEN P.PHONE = 2 THEN P.NUMBER ELSE NULL END) AS NUMBER2, MAX(CASE WHEN P.PHONE = 3 THEN P.PHONE ELSE NULL END) AS PHONE3, MAX(CASE WHEN P.PHONE = 3 THEN P.NUMBER ELSE NULL END) AS NUMBER3 FROM TEACHER T LEFT JOIN PHONES P ON P.IDPERSON = T.ID_TEACHER GROUP BY T.ID_TEACHER