mysql 行转列
最近遇到一需求原始数据如下：
mysql> select> +------+----------------+------+------------+-------------+
|> +------+----------------+------+------------+-------------+
|521 | ICC_ICCGAME(3) | 11 | 2017-05-01 | 0 |
|522 | ICC_ICCGAME(3) | 11 | 2017-05-02 | 0 |
|523 | ICC_ICCGAME(3) | 11 | 2017-05-03 | 1 |
|531 | ICC_HUAWEI | 11 | 2017-05-01 | 0 |
|532 | ICC_HUAWEI | 11 | 2017-05-02 | 0 |
|533 | ICC_HUAWEI | 11 | 2017-05-03 | 0 |
|541 | ICC_ICCGAME(0) | 11 | 2017-05-01 | 0 |
|542 | ICC_ICCGAME(0) | 11 | 2017-05-02 | 0 |
|543 | ICC_ICCGAME(0) | 11 | 2017-05-03 | 0 |
|551 | ICC_UC | 11 | 2017-05-01 | 0 |
|552 | ICC_UC | 11 | 2017-05-02 | 0 |
|553 | ICC_UC | 11 | 2017-05-03 | 0 |
|561 | ICC_QIHOO | 11 | 2017-05-01 | 0 |
|562 | ICC_QIHOO | 11 | 2017-05-02 | 0 |
|563 | ICC_QIHOO | 11 | 2017-05-03 | 0 |
|571 | ICC_VIVO | 11 | 2017-05-01 | 0 |
|572 | ICC_VIVO | 11 | 2017-05-02 | 0 |
|573 | ICC_VIVO | 11 | 2017-05-03 | 0 |
|581 | ICC_GIONEE | 11 | 2017-05-01 | 0 |
|582 | ICC_GIONEE | 11 | 2017-05-02 | 0 |
|583 | ICC_GIONEE | 11 | 2017-05-03 | 0 |
要求如下显示：
此处用到了行转列，由于列不固定，考虑使用动态sql
SET @d=(select GROUP_CONCAT('if(sdkname=\'',sdkname,'\' ,total_count,0) as `',sdkname,'`')
FROM (SELECT DISTINCT sdkname FROM pcik_log_dep.u1ge_query_log ) A);
SET @sql=CONCAT('select date,',@d,'from pcik_log_dep.u1ge_query_log group by date');
PREPARE sdtmt from @sql;
EXECUTE sdtmt;
deallocate prepare sdtmt;
------------+---------+-----------+-------------+------------+------------+----------------+----------------+------------+-----------+----------+-----------+--------+----------+------------+
| date | icctest | ICC_BAIDU | ICC_COOLPAD | ICC_GIONEE | ICC_HUAWEI | ICC_ICCGAME(0) | ICC_ICCGAME(3) | ICC_LENOVO | ICC_MEIZU | ICC_OPPO | ICC_QIHOO | ICC_UC | ICC_VIVO | ICC_XIAOMI |
+------------+---------+-----------+-------------+------------+------------+----------------+----------------+------------+-----------+----------+-----------+--------+----------+------------+
| 2017-05-01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-02 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-03 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-04 | 0 | 0 | 0 | 0 | 0 | 0 | 4380 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-05 | 0 | 0 | 0 | 0 | 0 | 0 | 5126 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-06 | 0 | 0 | 0 | 0 | 0 | 0 | 5571 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-07 | 0 | 0 | 0 | 0 | 0 | 0 | 5888 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-08 | 0 | 0 | 0 | 0 | 0 | 0 | 6135 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-09 | 0 | 0 | 0 | 0 | 0 | 0 | 6199 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2017-05-10 | 0 | 0 | 0 | 0 | 0 | 0 | 6199 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+---------+-----------+-------------+------------+------------+----------------+----------------+------------+-----------+----------+-----------+--------+----------+------------+
10 rows in set (0.00 sec)
页:
[1]