mysql - 从 2Tables-Mysql 中删除通用 id 时发现问题

Question

SET @sql =   CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'i2049127_wp1' AND table_name = 
'wp3_wpsp_ticket' AND column_name NOT IN ('wt.guest_name', 'wt.guest_email')), 
' from i2049127_wp1.wp3_wpsp_ticket wt,i2049127_wp1.wp3_wpsp_catagories wc   where wt.cat_id=wc.id limit 1');

PREPARE data FROM @sql;

EXECUTE data;

尝试连接 2 个表，但出现如下错误

Answer 1

如果您不需要它，最简单的方法就是从所选列中删除 id:

SET @sql =   CONCAT('SELECT ',
                    (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'i2049127_wp1' AND table_name = 
'wp3_wpsp_ticket' AND column_name NOT IN ('wt.guest_name', 'wt.guest_email', 'id')), 
                  ' from i2049127_wp1.wp3_wpsp_ticket wt  join i2049127_wp1.wp3_wpsp_catagories wc on wt.cat_id = wc.id limit 1');

如果您确实需要它，您需要决定哪一个:

SET @sql =   CONCAT('SELECT ',
                    (SELECT GROUP_CONCAT((CASE WHEN COLUM_NAME = 'id' THEN 'wt.id' ELSE COLUMN_NAME END)) FROM 
information_schema.columns WHERE table_schema = 'i2049127_wp1' AND table_name = 
'wp3_wpsp_ticket' AND column_name NOT IN ('wt.guest_name', 'wt.guest_email')), 
                  ' from i2049127_wp1.wp3_wpsp_ticket wt join i2049127_wp1.wp3_wpsp_catagories wc on wt.cat_id = wc.id limit 1');

请注意，我还修复了过时的 JOIN 语法。