我想在列中轉置此請求的結果
SELECT column_name
FROM information_schema.columns
WHERE table_name IN ('volumestest');
結果是這樣
| 時間 |
|---|
| T1 |
| T2 |
| T3 |
| T4 |
| T5 |
| T6 |
我要這個
|T0|T1|T2|T3|T4|T5|T6|
如果可能的話，我想用選擇中的選項來做到這一點，而不是用變數存盤。
謝謝你
uj5u.com熱心網友回復：
您可以使用動態陳述句和PREPARE STATEMENT來執行此操作， 如下所示：
SELECT CONCAT("SELECT "
, GROUP_CONCAT( column_name )
, " FROM MYTABLE"
, " WHERE 1=1") into @myQuery
FROM information_schema.columns
WHERE table_name = 'MYTABLE'
AND TABLE_SCHEMA = 'textil';
SELECT @myQuery;
PREPARE stmt FROM @MYQUERY;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
樣本
MariaDB [textil]> SELECT * from MYTABLE;
---- ------ ------ ------
| T0 | T1 | T2 | T3 |
---- ------ ------ ------
| 1 | 2 | 4 | 8 |
| 2 | 4 | 8 | 16 |
---- ------ ------ ------
2 rows in set (0.00 sec)
MariaDB [textil]> SELECT CONCAT("SELECT "
-> , GROUP_CONCAT( column_name )
-> , " FROM MYTABLE"
-> , " WHERE 1=1") into @myQuery
-> FROM information_schema.columns
-> WHERE table_name = 'MYTABLE'
-> AND TABLE_SCHEMA = 'textil';
Query OK, 1 row affected (0.01 sec)
MariaDB [textil]>
MariaDB [textil]> SELECT @myQuery;
-------------------------------------------
| @myQuery |
-------------------------------------------
| SELECT T0,T1,T2,T3 FROM MYTABLE WHERE 1=1 |
-------------------------------------------
1 row in set (0.00 sec)
MariaDB [textil]>
MariaDB [textil]> PREPARE stmt FROM @MYQUERY;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
MariaDB [textil]> EXECUTE stmt;
---- ------ ------ ------
| T0 | T1 | T2 | T3 |
---- ------ ------ ------
| 1 | 2 | 4 | 8 |
| 2 | 4 | 8 | 16 |
---- ------ ------ ------
2 rows in set (0.01 sec)
MariaDB [textil]>
MariaDB [textil]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
MariaDB [textil]>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/349052.html
上一篇：使用我自己的MySQL服務器將我的Flask應用程式部署到Heroku
下一篇：來自兩個不同表的除法結果