php - 如何在mysql中的一个表中显示2个不同的数据(两个条件)

Question

我在 php mysql 中显示不同的数据时遇到了问题。我想在两种情况下显示不同的数据。例如:

我有 table :

tbl_hresponse

| id_reponse | id_atribut | skala | step |
| 1          | 1          | 1     | 1    |
| 1          | 2          | 4     | 1    |
| 1          | 3          | 2     | 1    |
| 1          | 1          | 5     | 2    |
| 1          | 2          | 6     | 2    |
| 1          | 3          | 2     | 2    |

所以，我想这样输出:

| id_reponse | id_atribut | skala step 1 | skala step 2 |
| 1          | 1          | 1            | 5      |
| 1          | 2          | 4            | 6      |
| 1          | 3          | 2            | 2      |

我有这样的代码:

$k = $db->pdo->prepare("select *, (CASE WHEN tbl_hresponder.step = '1' THEN tbl_hresponder.skala END) AS k,
                                                  (CASE WHEN tbl_hresponder.step = '2' THEN tbl_hresponder.skala END) AS q
                                                  from tbl_hresponder, tbl_atribut
                                                  where tbl_hresponder.id_atribut = tbl_atribut.id_atribut
                                                  AND tbl_hresponder.id_responder = '".$_GET['report']."'
                                                  AND tbl_hresponder.id_fservqual = '".$rfs['id_fservqual']."'
                                                  AND tbl_hresponder.step = 1");

Answer 1

您可以尝试使用CASE WHEN 和max 函数。

SELECT t1.id_reponse,
       t1.id_atribut,
       max(case when t1.step = 1 then t1.skala end) `skala step 1`, 
       max(case when t1.step = 2 then t1.skala end) `skala step 2`
FROM tbl_hresponse t1 
GROUP  BY  
       t1.id_reponse,
       t1.id_atribut

sqlfiddle:http://sqlfiddle.com/#!9/92a73e/4

结果

| id_reponse | id_atribut | skala step 1 | skala step 2 |
|------------|------------|--------------|--------------|
|          1 |          1 |            1 |            5 |
|          1 |          2 |            4 |            6 |
|          1 |          3 |            2 |            2 |