The simple way to select fields excluding others

Question

For example, there is 10 fields in a table. I want to select 9 fields except one(a9).

a1 a2 a3 a4 a5 a6 a7 a8 a9 a10


select a1,a2,a3,a4,a5,a6,a7,a8,a10
from t1

Is there simple way to get the result?

Notice: there is no rules for fields(like aN ). It's just convenient to make an example.

score 0 · Answer 1 · answered Jul 22 '20 at 08:20

0

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' 
AND COLUMN_NAME NOT IN ('a9')), ' FROM t1'); 
PREPARE stmt1 FROM @SQL; 
EXECUTE stmt1;

answered Jul 22 '20 at 08:20

RAHUL JAIN

11
2

2

would you mind explaining your code? – Jack Jul 22 '20 at 08:24
INFORMATION_SCHEMA.COLUMNS Contains all the column's names with respect to table. First, we select the columns needed in the query and then we create a dynamic query. – RAHUL JAIN Jul 22 '20 at 08:37
Please add some explanation to your answer such that others can learn from it – Nico Haase Jul 22 '20 at 10:39

The simple way to select fields excluding others

1 Answers1