mysql - Mysql行到列选择特定数据

Question

我有专栏用户和评级。

SELECT rating.idUser, user.nmUser, rating.idBengkel, rating.nilai FROM `rating`
JOIN user on rating.idUser = user.idUser
WHERE rating.idBengkel=1 or rating.idBengkel=2

结果 ：

+--------+---------------------------+-----------+-------+
| idUser |          nmUser           | idBengkel | nilai |
+--------+---------------------------+-----------+-------+
|     10 | Hudson mas77              |         1 |     5 |  
|     11 | Vina Nurfadzilah          |         1 |     5 |  
|     12 | Angelica Amartya          |         1 |     5 |  
|     15 | Syahrul K                 |         1 |     4 |  
|     27 | Ashar Murdihastomo        |         1 |     5 |  
|     28 | Eril Obeit Choiri         |         1 |     2 |  
|     29 | Ariyadi                   |         1 |     3 |  
|     30 | Robertus Dwian Augusta    |         1 |     4 |  
|     31 | Irfan Setiaji             |         1 |     4 |  
|     33 | Baby Ayuna                |         1 |     5 |  
|      9 | Nur k hamid               |         2 |     5 |  
|     10 | Hudson mas77              |         2 |     5 |  
|     13 | Yuana Putra               |         2 |     4 |  
|     14 | Nanda Aulia Irza Ramadhan |         2 |     4 |  
|     26 | taufiq rahman             |         2 |     5 |  
|     27 | Ashar Murdihastomo        |         2 |     5 |  
|     28 | Eril Obeit Choiri         |         2 |     5 |  
|     30 | Robertus Dwian Augusta    |         2 |     4 |  
|     44 | halim budiono             |         2 |     1 |  
+--------+---------------------------+-----------+-------+

当我尝试使用此查询获取类似记录时

SELECT rating.idUser, user.nmUser FROM rating
JOIN user 
ON rating.idUser = user.idUser
WHERE rating.idBengkel = 1 and rating.idUser 
IN (SELECT rating.idUser from rating WHERE rating.idBengkel = 2) 
ORDER by idUser

结果 ：

+-----------+------------------------+
| idUser    |         nmUser         |  
+-----------+------------------------+
|        10 | Hudson mas77           |  
|        27 | Ashar Murdihastomo     | 
|        28 | Eril Obeit Choiri      | 
|        30 | Robertus Dwian Augusta |  
+-----------+------------------------+

结果工作正常，但我想将列“nilai”显示为 ItemX 和 ItemY。这些是用户相似的数据。在这种情况下，我有 4 个类似的用户对 idBengkel=1 和 idBengkel=2 进行评分，结果如上所示。我想要如下表所示。

+--------+------------------------+-------+-------+
| idUser |         nmUser         | ItemX | ItemY | 
+--------+------------------------+-------+-------+
|     10 | Hudson mas77           |     5 |     5 |  
|     27 | Ashar Murdihastomo     |     5 |     5 |  
|     28 | Eril Obeit Choiri      |     2 |     5 |  
|     30 | Robertus Dwian Augusta |     4 |     4 |  
+--------+------------------------+-------+-------+

我需要解决方案，我在https://stackoverflow.com/a/7976379/12396302中尝试使用此解决方案，但结果不止一行。请帮助我，我无法实现该查询的解决方案。问候！

标签： mysqljoinpivot-tablecrosstab

Answer 1

我认为您需要以下查询-

SELECT rating.idUser,
       user.nmUser,
       MAX(CASE WHEN rating.idBengkel = 1 THEN rating.nilai END) ItemX,
       MAX(CASE WHEN rating.idBengkel = 2 THEN rating.nilai END) ItemY,
  FROM `rating`
  JOIN user on rating.idUser = user.idUser
 WHERE rating.idBengkel IN (1, 2)
 GROUP BY rating.idUser,
          user.nmUser