mysql - 我如何使用Mysql中的Select从表中获取查询

Question

我有表作为名称jual

 Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_jual    | int(11)     | NO   | PRI | NULL    |       |
| tanggal    | date        | YES  |     | NULL    |       |
| jumlah     | int(4)      | NO   |     | NULL    |       |
| id_obat    | int(11)     | YES  | MUL | NULL    |       |
| id_periksa | int(11)     | YES  | MUL | NULL    |       |
| pemakaian  | varchar(30) | YES  |     | NULL    |       |

当我执行select * from jual“结果在这里”

 id_jual | tanggal    | jumlah | id_obat | id_periksa | pemakaian |
+---------+------------+--------+---------+------------+-----------+
|       1 | 2014-10-29 |     20 |       1 |          1 | 3x1       |
|       2 | 2014-10-29 |     10 |       5 |          1 | 2x1       |
|       5 | 2014-10-29 |     20 |       8 |          2 | 2x1       |
|       6 | 2014-10-29 |     20 |       1 |          3 | 3x1       |
|      10 | 2014-10-29 |     13 |       5 |          4 | 2x1       |
|      11 | 2014-10-29 |     10 |       8 |          4 | 2x1       |
|      12 | 2014-10-29 |     30 |       1 |          5 | 2x1       |
|      15 | 2014-10-29 |     30 |       3 |          6 | 21        |
|      16 | 2014-10-29 |     20 |       8 |          6 | 3x1       |
|      18 | 2014-10-29 |     30 |       3 |          7 | 1x2       |
|      22 | 2014-10-30 |     20 |       3 |          9 | 1x2       |
|      39 | 2014-10-30 |     10 |       3 |         18 | 1x1       |
|      43 | 2014-10-31 |     15 |       3 |         20 | 2x1       |
|      46 | 2014-10-31 |     10 |       5 |         21 | 2x1       |
|      47 | 2014-11-01 |     20 |       6 |         22 | 1x1       |
|      50 | 2014-11-01 |      7 |       6 |         23 | 1x1       |

我怎样才能得到这样的结果 View :

id_obat   |                         tanggal   
+---------+------------+----------+----------+----------+-----------+
            2014-10-29 |2014-10-30|2014-10-31|2014-11-01|2014-11-02
+---------+------------+----------+----------+----------+-----------+
|  1      | 20.20.30   |          |          |          |            |
|  3      | 30.30      | 20.10    | 15       |          |            |
|  5      | 10.13      |          | 10.      |          |            |
|  6      |            |          | 20.7     |          |            |
|  8      | 20.10.20   |          |          |          |            |
+---------+------------+----------+----------+----------+------------+

请帮我解决我的问题......!!!!!

Answer 1

您可以使用如下动态数据透视语句来做到这一点:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(tanggal = ''',
      tanggal,
      ''', val, NULL)) AS ''',
      tanggal,
      ''''
    )
  ) INTO @sql
FROM jual;

SET @sql = CONCAT('
       SELECT id_obat, ', @sql, ' 
       FROM (
         SELECT 
           tanggal, 
           id_obat, 
           GROUP_CONCAT(jumlah SEPARATOR ''.'') AS val 
         FROM jual
         GROUP BY tanggal, id_obat
         ORDER BY id_jual) AS DERIVED_TABLE
       GROUP BY id_obat;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

查看此sample SQL Fiddle

结果与您的示例输出不完全匹配，但我认为这是因为示例输出不正确，因为值 20.7 应该低于 2014-11-01，而不是像示例中那样低于 2014-10-31 .