溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下面一起來了解下如何實現mysql行轉列，相信大家看完肯定會受益匪淺，文字在精不在多，希望如何實現mysql行轉列這篇短內容是你想要的。
原始數據如下：
mysql> select id,sdkname,sid,date,total_count from u1ge_query_log;
+------+----------------+------+------------+-------------+
| id | sdkname | sid | date | total_count |
+------+----------------+------+------------+-------------+
| 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)
看完如何實現mysql行轉列這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
